add column if doesn't exist
add column if doesn't exist
От:
"Brandon Metcalf" <bmetcalf@nortel.com>
Дата:
Is there a way to check for the existence of a column in a table other than, say, doing a SELECT on that column name and checking the output? I'm basically looking to do an ALTER TABLE foo ADD COLUMN bar if bar doesn't exist. Thanks. -- Brandon
Re: add column if doesn't exist
От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
Brandon Metcalf wrote: > Is there a way to check for the existence of a column in a table > other than, say, doing a SELECT on that column name and checking the > output? SELECT * FROM information_schema.columns; Customize to taste. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Re: add column if doesn't exist
От:
"Brandon Metcalf" <bmetcalf@nortel.com>
Дата:
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 tablep> > other than, say, doing a SELECT on that column name and checking thep> > 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 ...) ? -- Brandon
Re: add column if doesn't exist
От:
Chris Browne <cbbrowne@acm.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
Re: add column if doesn't exist
От:
"Brandon Metcalf" <bmetcalf@nortel.com>
Дата:
c == cbbrowne@acm.org writes: c> I set up a stored procedure to do this for Slony-I... Replace c> @NAMESPACE@ with your favorite namespace, and slon_quote_brute can c> likely be treated as an identity function unless you use silly c> namespace names :-). Thanks. -- Brandon
Re: add column if doesn't exist
От:
"Brandon Metcalf" <bmetcalf@nortel.com>
Дата:
c == cbbrowne@acm.org writes: c> I set up a stored procedure to do this for Slony-I... Replacec> @NAMESPACE@ with your favorite namespace, and slon_quote_brute canc> likely be treated as an identity function unless you use sillyc> namespace names :-). Thanks. -- Brandon
