Thread: Setting search paths inside a function (plpgsql)
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
triedto 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
obviousbut I am not seeing it. This is 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_results_t AS (parcel_gid INTEGER,
parcel_fips VARCHAR(10),parcel_zip VARCHAR(10),parcel_ownname TEXT);
BEGIN;
CREATE OR REPLACE FUNCTION fips_name_srch(VARCHAR,VARCHAR)
RETURNS setof fips_name_results_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_results_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_fti 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;
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.
"Gregory S. Williamson" <gsw@globexplorer.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_fti 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
Doh ! Thanks for the insight.
Greg
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wed 9/29/2004 8:40 AM
To: Gregory S. Williamson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Setting search paths inside a function (plpgsql)
"Gregory S. Williamson" <gsw@globexplorer.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_fti 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