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 $$
declare 
    col_name varchar ;
begin 
      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; 
      else
           col_name := colname ||' Already exist';
      end if;
return col_name;
end;
$$


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: