Wednesday, September 26, 2012

Add the column only if not exist on PostgreSQL

here the function to add the column to the table only if column does not exist.

create or replace function addcol(schemaname varchar, tablename varchar, colname varchar, coltype varchar)
returns varchar 
language 'plpgsql'
as $$
    col_name varchar ;
      execute 'select column_name from information_schema.columns  where  table_schema = ' ||
      quote_literal(schemaname)||' and table_name='|| quote_literal(tablename) || '   and    column_name= '|| quote_literal(colname)    
      into   col_name ;   

      raise info  ' the val : % ', col_name;
      if(col_name is null ) then 
          col_name := colname;
          execute 'alter table ' ||schemaname|| '.'|| tablename || ' add column '|| colname || '  ' || coltype; 
           col_name := colname ||' Already exist';
      end if;
return col_name;

function would require argument schema, table, column and data type

if the column exist it returns, 'column Already exist '  else 
return the added column.

 select addcol('masters','m_approve_status', 'test1', 'integer');

No comments: