Skip site navigation (1) Skip section navigation (2)

Re: Setting search paths inside a function (plpgsql)

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Setting search paths inside a function (plpgsql)
Date: 2004-09-29 15:09:32
Message-ID: 20040929080723.H59588@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2004-09-29 15:32:24
Subject: Re: EXTRACT Clarification
Previous:From: Stephan SzaboDate: 2004-09-29 15:06:27
Subject: Re: About PostgreSQL's limit on arithmetic operations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group