add_missing_table_field(p_type text, p_field text, p_table text, p_namespace text)

8.22. add_missing_table_field(p_type text, p_field text, p_table text, p_namespace text)

Function Properties

Language: PLPGSQL

Return Type: boolean

Add a column of a given type to a table if it is missing

DECLARE
  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;