The following bug has been logged online: Bug reference: 3599 Logged by: Alexis Beuraud Email address: [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match