Re: when to use "execute" in plpgsql?

On Fri, Feb 27, 2009 at 12:00 PM, Enrico Sirola <enrico(dot)sirola(at)gmail(dot)com> wrote:
> 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

I'm not completely sure what the problem is because this is light on
detail, but here's a what I bet the problem is. static (that is, not
EXECUTE-ed) queries in pl/pgsql functions convert table references in
the function body to fixed 'pointers' to actual tables that are always
schema qualified. Once the function is run the first time and the
plan generated, changing the schema will have no bearing on which
tables are used. Thus, the function will not 'float' with the current
schema search path setting.

dynamic sql functions however will always re-look up the tables based
on the search path because the plan is not saved off for the query.

If this is your problem, you have a couple of options:

*) always use dynamic sql in functions that are meant to apply to
multiple schemas in the same session
*) duplicate your function for each schema and make your tables fully
schema qualified
*) DISCARD your plans before running your function

merlin