Re: [SQL] add column if doesn't exist

[Brandon Metcalf]

c == [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] add column if doesn't exist

[Brandon Metcalf]

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 ...)

?

-- 
Brandon

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] add column if doesn't exist

[Chris Browne]

[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

[SQL] add column if doesn't exist

[Brandon Metcalf]

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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] add column if doesn't exist

[Peter Eisentraut]

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/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster