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

Re: Setting search paths inside a function (plpgsql)

From: "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Setting search paths inside a function (plpgsql)
Date: 2004-09-29 17:59:56
Message-ID: 71E37EF6B7DCC1499CEA0316A256832801D4B8A5@loki.wc.globexplorer.net (view raw or flat)
Thread:
Lists: pgsql-general
Doh !  Thanks for the insight.

Greg
-----Original Message-----
From:	Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent:	Wed 9/29/2004 8:40 AM
To:	Gregory S. Williamson
Cc:	pgsql-general(at)postgresql(dot)org
Subject:	Re: [GENERAL] Setting search paths inside a function (plpgsql) 
"Gregory S. Williamson" <gsw(at)globexplorer(dot)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




pgsql-general by date

Next:From: Greg StarkDate: 2004-09-29 19:18:50
Subject: Re: Null comparisons (was Re: checksum)
Previous:From: Geisler, JimDate: 2004-09-29 17:37:00
Subject: failure to restart postgresql service after improper shutdown

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