[postgis-users] execute 'alter table' won't work

[Mark Cave-Ayland]

On Wed, 2007-04-18 at 13:35 +0200, Peter Bange wrote:
> Anybody an idea why this won't work, I am checking if a table has all
> the columns I need:
> 
> 
> for rec in select shortname from atb_pollutants loop
> -- loop over pollutants
> BEGIN
> strSQL = 'alter table atb_compositions add column ' || rec.shortname
> || ' real';
> execute strSQL;
> raise notice '... added';
> EXCEPTION
> when duplicate_column then
> -- take next
> raise notice '... already existing';
> END;
> end loop;
> 
> 
> I get the notice ' ... added'  for all non-existing columns in the
> table (so I assume no error has occured) but when I look in the table
> not a single field is added. And indeed, when I run the same code
> again I get exactly the same ' ...added' messages. (The notice
> '...already existing works fine for all the columns already in the
> table.)


Hi Peter,

Have you tried outputting the statement actually being executed, e.g.:

...
...
strSQL = 'alter table atb_compositions add column ' || rec.shortname ||
' real';
raise notice 'sql: %', strSQL;
execute strSQL;
...
...

Also you haven't given the complete code for the stored procedure - for
example, you may have specified incorrect variable types in the DECLARE
section or incorrect parameter definitions in the function declaration
itself.


Kind regards,

Mark.