From: Chris Browne <cbbrowne@acm.org>
Subject: Re: add column if doesn't exist
Date: Tue, 27 Sep 2005 09:34:07 -0400
Organization: cbbrowne Computing Inc
Lines: 65
Message-ID: <60d5muk52o.fsf@dba2.int.libertyrms.com>
References: <Pine.LNX.4.58L.0509261534430.1453@cash.rhiamet.com>
	<200509270216.05141.peter_e@gmx.net>
	<Pine.LNX.4.58L.0509270732170.1453@cash.rhiamet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux)
Cancel-Lock: sha1:FLaF5gGVknFVvu2LZr8xko1dzV0=
To: pgsql-sql@postgresql.org

bmetcalf@nortel.com ("Brandon Metcalf") writes:

> p == peter_e@gmx.net writes:
>
>  p> Brandon Metcalf wrote:
>  p> > Is there a way to check for the existence of a column in a table
>  p> > other than, say, doing a SELECT on that column name and checking the
>  p> > output?
>
>  p> SELECT * FROM information_schema.columns;
>
>  p> Customize to taste.
>
>
> Yes, that's what I'm looking for.  Thanks.
>
> Now, is there a way to mix PostgreSQL commands and SQL and do
> something like
>
>   ALTER TABLE foo ADD COLUMN bar WHERE EXISTS(SELECT * FROM
>     information_schema.columns WHERE ...)
>
> ?

I set up a stored procedure to do this for Slony-I...  Replace
@NAMESPACE@ with your favorite namespace, and slon_quote_brute can
likely be treated as an identity function unless you use silly
namespace names :-).

create or replace function @NAMESPACE@.add_missing_table_field (text, text, text, text) 
returns bool as '
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 @NAMESPACE@.slon_quote_brute(n.nspname) = p_namespace and 
         c.relnamespace = n.oid and
         @NAMESPACE@.slon_quote_brute(c.relname) = p_table and
         a.attrelid = c.oid and
         @NAMESPACE@.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;' language plpgsql;

comment on function @NAMESPACE@.add_missing_table_field (text, text, text, text) 
is 'Add a column of a given type to a table if it is missing';

-- 
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/sgml.html
"The  surest  sign  that  intelligent  life exists  elsewhere  in  the
universe is that it has never tried to contact us."
-- Calvin and Hobbes