Setting search paths inside a function (plpgsql)

[Gregory S. Williamson]

Dear peoples,

I've got a problem which seemed to be neatly solved by the use of schemas, and in fact it mostly works, but I have tried to go one step too far, perhaps.

Rather than have the application do

SET search_path TO f12057;
SELECT * FROM parcel-owners WHERE ... ;
SET search_path TO public;

I thought I'd have a single function in the public schema which they call:

select * from fips_name_srch( '12057','white' );

and in the function I do:
env_str := ''SET search_path TO f'' || p_fips || '',public'';
EXECUTE env_str;
and then my search and a LOOP to return values with a final SET command to put us back to the public schema ...

In fact it works, once, and returns the expected values, but subsequent calls get exactly the same data ...

gex_vector=# select * from fips_name_srch( '12057','white' );
NOTICE: doing name search for fips 12057
NOTICE: did exec of <SET search_path TO f12057,public>
parcel_gid | parcel_fips | parcel_zip | parcel_ownname
------------+-------------+------------+--------------------------------
11449960 | 12057 | 33548 | DELANOE WHITE
11437500 | 12057 | 33548 | WHITE DORREN
11444394 | 12057 | 33548 | WHITE FERD T AND LACY A JR
....

select * from fips_name_srch( '12031','white' );
NOTICE: doing name search for fips 12031
NOTICE: did exec of <SET search_path TO f12031,public>
parcel_gid | parcel_fips | parcel_zip | parcel_ownname
------------+-------------+------------+--------------------------------
11449960 | 12057 | 33548 | DELANOE WHITE
11437500 | 12057 | 33548 | WHITE DORREN
11444394 | 12057 | 33548 | WHITE FERD T AND LACY A JR
....

If I exit and run the second one it works:
gex_vector=# select * from fips_name_srch( '12031','white' );
NOTICE: doing name search for fips 12031
NOTICE: did exec of <SET search_path TO f12031,public>
parcel_gid | parcel_fips | parcel_zip | parcel_ownname
------------+-------------+------------+--------------------------------
8830922 | 12031 | 32202 | CARLA WHITE MISSION
8830925 | 12031 | 32202 | CARLA WHITE MISSION
8855011 | 12031 | 32202 | CARLA WHITE MISSION
8824016 | 12031 | 32202 | CARLA WHITE MISSION INC
....
I have tried variations with VOLATILE explicitly defined and some unsuccessful gyrations. I am sure the answer is obvious but I am not seeing it. Thisis postgres 7.4, the function is below.

Any suggestions or advice would be welcome ... (RTFM acceptable but a page reference would be helpful)

thanks,

Greg Williamson
DBA
GlobeXplorer LLC

CREATE TYPE fips_name_resul ts_t AS (parcel_gid INTEGER,
parcel_fips VARCHAR(10),par cel_zip VARCHAR(10),par cel_ownname TEXT);

BEGIN;
CREATE OR REPLACE FUNCTION fips_name_srch( VARCHAR,VARCHAR )
RETURNS setof fips_name_resul ts_t AS '
DECLARE p_fips ALIAS FOR $1;
p_srchstr ALIAS FOR $2;
parcel_gid INTEGER;
parcel_zip VARCHAR(10);
parcel_ownname TEXT;
env_str TEXT;
retrec fips_name_resul ts_t%rowtype;
BEGIN
RAISE NOTICE ''doing name search for fips %'',p_fips;
env_str := ''SET search_path TO f'' || p_fips || '',public'';
EXECUTE env_str;
RAISE NOTICE ''did exec of <%>'',env_str ;
FOR retrec IN
SELECT o.gid,o.s_fips_ cou,o.s_zip,o.s _ownername
FROM parcel_owners o, parcel_owner_ft i f
WHERE f.string = p_srchstr AND f.id = o.orig_id ORDER BY 2,3,4
LOOP
RETURN NEXT retrec;
--SET search_path TO public;
END LOOP;
RETURN;
SET search_path TO public;
END;
' LANGUAGE 'plpgsql' VOLATILE;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

On Wed, 29 Sep 2004, Gregory S. Williamson wrote:

I've got a problem which seemed to be neatly solved by the use of
schemas, and in fact it mostly works, but I have tried to go one step
too far, perhaps.

Rather than have the application do

SET search_path TO f12057;
SELECT * FROM parcel-owners WHERE ... ;
SET search_path TO public;

I thought I'd have a single function in the public schema which they call:

select * from fips_name_srch( '12057','white' );

and in the function I do:
env_str := ''SET search_path TO f'' || p_fips || '',public'';
EXECUTE env_str;
and then my search and a LOOP to return values with a final SET command to put us back to the public schema ...

I think you probably need to be using EXECUTE on the query you want to
have be affected by the above. Otherwise it's likely to be planned once
and saved with the first values used for the session.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Tom Lane]

"Gregory S. Williamson" <gs*@globexplor er.com> writes:

RAISE NOTICE ''doing name search for fips %'',p_fips;
env_str := ''SET search_path TO f'' || p_fips || '',public'';
EXECUTE env_str;
RAISE NOTICE ''did exec of <%>'',env_str ;
FOR retrec IN
SELECT o.gid,o.s_fips_ cou,o.s_zip,o.s _ownername
FROM parcel_owners o, parcel_owner_ft i f
WHERE f.string = p_srchstr AND f.id = o.orig_id ORDER BY 2,3,4
LOOP

You'd have to use FOR-IN-EXECUTE to make this work the way you are
expecting. As is, the plan for the SELECT is generated and cached
the first time through, and in the process the table references are
bound to specific tables in specific schemas.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match