Hello,
I'm having some troubles with the correct use of the execute plpgsql
statement. Where I work, we have a postgresql db hosting a set of
schemas all with the same tables and, from time to time, we upgrade
the schemas to a new version coding a stored procedure like the
following (pseudocode):
-------> example use case <--------
-- upgrade function, gets a schema name as input and upgrades it
create function upgrade_to_new_version(schema_name name)
returns void as $$
begin
-- become the schema owner
execute 'set role to ' || schema_name;
/* perform DDL and data transformations work here */
/* body here */
end;
language plpgsql volatile strict;
-- schemas_to_upgrade contains a column sname with the names of
-- the schemas needing an upgrade
select upgrade_to_new_version(sname) from schemas_to_upgrade;
--------->example end<-------------
the strange thing is that from time to time the function doesn't work.
Or, even worst, It works for a database but doesn't on another. The
issue usually goes away if we substitute the statement into /* body
here */ prepending those with an execute and submitting those via
execute; apparently the behaviour is reproducibile given a database
instance (i.e. it is not random), but it is impossible (at least for
us) to tell in advance if it will happen on another database (with the
same schemas and postgresql version number which, by the way, is the
official 8.3.5 on centos5/x86_64). The "safe" way to do things (it
never breaks) is to pass every statement via executes but we would
like to dig on this.
What are we missing?
Thanks in advance for your help,
enrico
