UwyG7bFE

1create or replace function create_constraint_if_not_exists (
2    t_name text, c_name text, constraint_sql text
3) 
4returns void AS
5$$
6begin
7    -- Look for our constraint
8    if not exists (select constraint_name 
9                   from information_schema.constraint_column_usage 
10                   where table_name = t_name  and constraint_name = c_name) then
11        execute constraint_sql;
12    end if;
13end;
14$$ language 'plpgsql' 
15     
16SELECT create_constraint_if_not_exists(
17        'foo',
18        'bar',
19        'ALTER TABLE foo ADD CONSTRAINT bar CHECK (foobies < 100);') 
20     
21ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
22ALTER TABLE foo ADD CONSTRAINT bar ...; 
23     
24ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
25ALTER TABLE foo ADD CONSTRAINT bar ...; 
26     
27DO $$
28BEGIN
29
30  BEGIN
31    ALTER TABLE foo ADD CONSTRAINT bar ... ;
32  EXCEPTION
33    WHEN duplicate_object THEN RAISE NOTICE 'Table constraint foo.bar already exists';
34  END;
35
36END $$; 
37     
38SELECT 1 FROM pg_constraint WHERE conname = 'constraint_name'" 
39     
40DO $$
41BEGIN
42    IF NOT EXISTS ( SELECT  constraint_schema
43                ,       constraint_name 
44                FROM    information_schema.check_constraints 
45                WHERE   constraint_schema = 'myschema'
46                  AND   constraint_name = 'myconstraintname'
47              )
48    THEN
49        ALTER TABLE myschema.mytable ADD CONSTRAINT myconstraintname CHECK (column <= 100);
50    END IF;
51END$$; 
52     
53DO 
54$$ BEGIN
55IF NOT EXISTS (select constraint_name 
56               from information_schema.table_constraints 
57               where table_schema='schame_name' and upper(table_name) = 
58upper('table_name')  and upper(constraint_name) = upper('constraint_name'))
59
60THEN
61
62   ALTER TABLE TABLE_NAME ADD CONSTRAINT CONTRAINT_NAME..... ;
63
64ELSE raise NOTICE 'Constraint CONTRAINT_NAME already exists in Table TABLE_NAME';   
65
66END IF;
67END
68$$;