[EMAIL PROTECTED] ("Brandon Metcalf") writes: > p == [EMAIL PROTECTED] 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 @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED](n.nspname) = p_namespace and c.relnamespace = n.oid and @[EMAIL PROTECTED](c.relname) = p_table and a.attrelid = c.oid and @[EMAIL PROTECTED](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 @[EMAIL PROTECTED] (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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly