Re: pl/pgsql problem with search_path

Thanks. If I had only read the manual before posting. I solved it by
using 'execute' instead of 'select into' since the search_path is not
static in my system.

Gene

On Saturday, September 6, 2003, at 04:21  PM, Bruce Momjian wrote:

>
> I think the problem is that the first time the function is called, it
> is
> compiled and cached for later use.  At that time the function is bound
> to the table oid, so even though you change the search path, the cached
> copy still calls the old table.
>
> If you exit psql and re-enter, or change the schema _before_ you call
> the function for the first time, it should be fine.
>
> This highlights another problem with our plpgsql function caching.
>
> -----------------------------------------------------------------------
> ----
>
> Eugene Chow wrote:
>> My plpgsql function seems to be ignoring search_path when looking for
>> the right table to select from. I'm running 7.3.4. Below is my test
>> code. Am I doing something wrong?
>>
>> TIA, Gene Chow
>>
>> test=> create or replace function getval() returns varchar as '
>> test'> declare val varchar;
>> test'> begin
>> test'>     select into val value from bar limit 1;
>> test'>     return val;
>> test'> end;' language 'plpgsql';
>> CREATE FUNCTION
>>
>> test=> create table public.bar ( value varchar );
>> CREATE TABLE
>>
>> test=> insert into public.bar values ('public value');
>> INSERT 4012748 1
>>
>> test=> create schema foo;
>> CREATE SCHEMA
>>
>> test=> create table foo.bar ( value varchar );
>> CREATE TABLE
>>
>> test=> insert into foo.bar values ('foo value');
>> INSERT 4012754 1
>>
>> test=> set search_path to foo, public;
>> SET
>>
>> test=> select *, getval() from bar;
>>     value   |  getval
>> -----------+-----------
>>   foo value | foo value
>> (1 row)
>>
>> test=> set search_path to public;
>> SET
>>
>> test=> select *, getval() from bar;
>>      value     |  getval
>> --------------+-----------
>>   public value | foo value
>> (1 row)
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
> 19073
>