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