Re: BUG #3628: Wrong schema picked

Heikki Linnakangas wrote:

> Pedro Gimeno wrote:
> > When a function has a SQL statement to execute that has an
> > unqualified table, that SQL statement doesn't always pick the table
> > from a schema in the search_path.
>=20
> The first time the function is run, all the statements in it are
> planned and the schema used for the insert is resolved. Subsequent=20=20
> calls will reuse the same plan. The obvious fix is to always=20=20
> explicitly specify the schema in the INSERT. Or if if you want it to=20=
=20
> depend on the search_path, you can execute it dynamically using=20=20
> EXECUTE=20=20
> (http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQ=
L-STATEMENTS-EXECUTING-DYN).

That's the workaround I'm using, yet I find this behaviour quite=20=20
unexpected. Using EXECUTE prevents the benefit of using prepared
statements until search_path changes or a schema is deleted or renamed,
not to mention the impact on readability.

What I expect is that when search_path changes, either explicitly or=20=20
implicitly (e.g. by creating a temporary table), or a schema is deleted=20=
=20
or renamed, the affected prepared queries are parsed again so that the=20=
=20
right schema is picked.

This would allow using a common schema for functions instead of=20=20
defining them once in every schema in which to apply them, in cases=20=20
(like mine) where the same operations have to be applied to different=20=20
schemas.

-- Pedro Gimeno