Thread: BUG #3599: Wrong search_path inside a function

The following bug has been logged online:

Bug reference:      3599
Logged by:          Alexis Beuraud
Email address:      alexis@siatel.com
PostgreSQL version: 8.2.4
Operating system:   Windows 2000 Professional
Description:        Wrong search_path inside a function
Details:

The function 'set search_path to' is not properly working when executed
through EXECUTE() in a function called more than once.
Please E-mail if I am doing something wrong or if there is a workaround. I
could not find anything on the Internet.

Postgres version 8.2.4 (pgAdmin 1.6.3 - 6112)
Here is a way to reproduce the problem (treat this as SQL code)


--creating the test schemas-----------

CREATE SCHEMA bugschema7
  AUTHORIZATION postgres;

CREATE SCHEMA bugschema8
  AUTHORIZATION postgres;

--creating test data

CREATE TABLE bugschema7.TableT
(
  i integer
)
WITHOUT OIDS;
ALTER TABLE bugschema7.TableT OWNER TO postgres;

CREATE TABLE bugschema8.TableT
(
  i integer
)
WITHOUT OIDS;
ALTER TABLE bugschema7.TableT OWNER TO postgres;

INSERT INTO bugschema7.TableT(
            i)
    VALUES (1);

INSERT INTO bugschema8.TableT(
            i)
    VALUES (2);

---Creating the buggy function-----------

CREATE OR REPLACE FUNCTION getifromthisschema(character varying)
  RETURNS SETOF bigint AS
$BODY$DECLARE
p_schemaName        ALIAS FOR $1;
result  integer;
begin
EXECUTE (' set search_path to ' || p_schemaName ); ---- setting the search
path here!
FOR result in
  select i
  from TableT
loop
return next result;
 END LOOP;
return;
end$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


--Viewing the bug
set search_path to bugschema7; ---- setting the search path here!
select t1.i,t2.i,t3,t4
from TableT as t1, bugschema8.TableT as
t2,public.getifromthisschema('bugschema7') as
t3,public.getifromthisschema('bugschema8') as t4;


---Should return :
---1,2,1,2
---but returns instead
---1,2,2,2

"Alexis Beuraud" <alexis@siatel.com> writes:
> EXECUTE (' set search_path to ' || p_schemaName ); ---- setting the search
> path here!
> FOR result in
>   select i
>   from TableT
> loop
> return next result;
>  END LOOP;

The reason that doesn't do what you expect is that the plan for the
SELECT is cached the first time through.  You'll need to use FOR IN
EXECUTE to make this work.  Rather than explicitly setting search_path
like that, which is likely to have unpleasant consequences all over the
place (hint: the effects persist after your function exits), you might
want to just insert the schema name into the EXECUTE string:

FOR result IN EXECUTE
    'select i from ' || quote_ident(p_schemaName) || '.TableT'
LOOP ...

            regards, tom lane