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

Search Discussions

  • Tom Lane at Sep 4, 2007 at 4:28 pm

    "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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedSep 4, '07 at 10:21a
activeSep 4, '07 at 4:28p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Alexis Beuraud: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase