Setting search paths inside a function (plpgsql) - Mailing list pgsql-general

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;