BUG #3599: Wrong search_path inside a function - Mailing list pgsql-bugs

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