pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedadd column if doesn't exist
5+ messages / 3 participants
[nested] [flat]
* add column if doesn't exist
@ 2005-09-26 20:38 Brandon Metcalf <bmetcalf@nortel.com>
0 siblings, 1 reply; 5+ messages in thread
From: Brandon Metcalf @ 2005-09-26 20:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: add column if doesn't exist
@ 2005-09-27 00:16 Peter Eisentraut <peter_e@gmx.net>
parent: Brandon Metcalf <bmetcalf@nortel.com>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Eisentraut @ 2005-09-27 00:16 UTC (permalink / raw)
To: Brandon Metcalf <bmetcalf@nortel.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: add column if doesn't exist
@ 2005-09-27 12:45 Brandon Metcalf <bmetcalf@nortel.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 5+ messages in thread
From: Brandon Metcalf @ 2005-09-27 12:45 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
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 ...)
?
--
Brandon
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: add column if doesn't exist
@ 2005-09-27 13:34 Chris Browne <cbbrowne@acm.org>
parent: Brandon Metcalf <bmetcalf@nortel.com>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Browne @ 2005-09-27 13:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: add column if doesn't exist
@ 2005-09-28 14:23 Brandon Metcalf <bmetcalf@nortel.com>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 5+ messages in thread
From: Brandon Metcalf @ 2005-09-28 14:23 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-09-28 14:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-26 20:38 add column if doesn't exist Brandon Metcalf <bmetcalf@nortel.com>
2005-09-27 00:16 ` Peter Eisentraut <peter_e@gmx.net>
2005-09-27 12:45 ` Brandon Metcalf <bmetcalf@nortel.com>
2005-09-27 13:34 ` Chris Browne <cbbrowne@acm.org>
2005-09-28 14:23 ` Brandon Metcalf <bmetcalf@nortel.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox