add_missing_table_field(text, text, text, text)

6.20. add_missing_table_field(text, text, text, text)

Function Properties

Language: PLPGSQL

Return Type: boolean

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;