Re: BUG #12553: Altering search_path between function calls

rs@plusw.de writes:
> Bug reference:      12553
> Logged by:          Rolf Schaufelberger
> Email address:      rs@plusw.de
> PostgreSQL version: 9.2.4
> Operating system:   Ubuntu 4.4.3-4ubuntu5.1

[ this function acts funny if the search_path is changed between calls: ]

> create or replace function public.xxx( coid integer)
> returns integer as $$ DECLARE newid integer;
> BEGIN
> insert into company_objects(company_id, obj_class) values (coid, 'TEST')
> returning id into newid;
> return newid;
> END;
> $$ language plpgsql;

This example works as you're expecting in 9.3 and up, as per this 9.3
release note item:

    Force cached plans to be replanned if the search_path changes (Tom Lane)

    Previously, cached plans already generated in the current session
    were not redone if the query was re-executed with a new
    search_path setting, resulting in surprising behavior.

So in previous versions, the function latches onto whichever
company_objects table it sees during its first execution.  Although that's
arguably a bug, we felt it was not safe to change the behavior so
fundamentally in minor releases.  9.2.x will not get changed in this way.

            regards, tom lane