Re: Setting search paths inside a function (plpgsql)

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.