prvvrPZX

1SELECT column_name 
2FROM information_schema.columns 
3WHERE table_name='x' and column_name='y';
4	
5DO $$ 
6    BEGIN
7        BEGIN
8            ALTER TABLE <table_name> ADD COLUMN <column_name> <column_type>;
9        EXCEPTION
10            WHEN duplicate_column THEN RAISE NOTICE 'column <column_name> already exists in <table_name>.';
11        END;
12    END;
13$$
14	
15ALTER TABLE x ADD COLUMN y int;
16	
17create or replace function addcol(schemaname varchar, tablename varchar, colname varchar, coltype varchar)
18returns varchar 
19language 'plpgsql'
20as 
21$$
22declare 
23    col_name varchar ;
24begin 
25      execute 'select column_name from information_schema.columns  where  table_schema = ' ||
26      quote_literal(schemaname)||' and table_name='|| quote_literal(tablename) || '   and    column_name= '|| quote_literal(colname)    
27      into   col_name ;   
28
29      raise info  ' the val : % ', col_name;
30      if(col_name is null ) then 
31          col_name := colname;
32          execute 'alter table ' ||schemaname|| '.'|| tablename || ' add column '|| colname || '  ' || coltype; 
33      else
34           col_name := colname ||' Already exist';
35      end if;
36return col_name;
37end;
38$$
39	
40CREATE OR REPLACE FUNCTION add_column(schema_name TEXT, table_name TEXT, 
41column_name TEXT, data_type TEXT)
42RETURNS BOOLEAN
43AS
44$BODY$
45DECLARE
46  _tmp text;
47BEGIN
48
49  EXECUTE format('SELECT COLUMN_NAME FROM information_schema.columns WHERE 
50    table_schema=%L
51    AND table_name=%L
52    AND column_name=%L', schema_name, table_name, column_name)
53  INTO _tmp;
54
55  IF _tmp IS NOT NULL THEN
56    RAISE NOTICE 'Column % already exists in %.%', column_name, schema_name, table_name;
57    RETURN FALSE;
58  END IF;
59
60  EXECUTE format('ALTER TABLE %I.%I ADD COLUMN %I %s;', schema_name, table_name, column_name, data_type);
61
62  RAISE NOTICE 'Column % added to %.%', column_name, schema_name, table_name;
63
64  RETURN TRUE;
65END;
66$BODY$
67LANGUAGE 'plpgsql';
68	
69select add_column('public', 'foo', 'bar', 'varchar(30)');
70	
71CREATE OR REPLACE function f_add_col(
72   _tbl regclass, _col  text, _type regtype, OUT success bool)
73    LANGUAGE plpgsql AS
74$func$
75BEGIN
76
77IF EXISTS (
78   SELECT 1 FROM pg_attribute
79   WHERE  attrelid = _tbl
80   AND    attname = _col
81   AND    NOT attisdropped) THEN
82   success := FALSE;
83
84ELSE
85   EXECUTE '
86   ALTER TABLE ' || _tbl || ' ADD COLUMN ' || quote_ident(_col) || ' ' || _type;
87   success := TRUE;
88END IF;
89
90END
91$func$;
92	
93SELECT f_add_col('public.kat', 'pfad1', 'int');