Christopher B. Browne's Home Page
cbbrowne@acm.org

8.20. add_missing_table_field(text, text, text, text)

Function Properties

PLPGSQLboolean
Add a column of a given type to a table if it is missing
    DECLARE
      p_namespace alias for $1;
      p_table     alias for $2;
      p_field     alias for $3;
      p_type      alias for $4;
      v_row       record;
      v_query     text;
    BEGIN
      select 1 into v_row from pg_namespace n, pg_class c, pg_attribute a
         where slon_quote_brute(n.nspname) = p_namespace and 
             c.relnamespace = n.oid and
             slon_quote_brute(c.relname) = p_table and
             a.attrelid = c.oid and
             slon_quote_brute(a.attname) = p_field;
      if not found then
        raise notice 'Upgrade table %.% - add field %', p_namespace, p_table, p_field;
        v_query := 'alter table ' || p_namespace || '.' || p_table || ' add column ';
        v_query := v_query || p_field || ' ' || p_type || ';';
        execute v_query;
        return 't';
      else
        return 'f';
      end if;
    END;

Google
Contact me at cbbrowne@acm.org