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

Re: pl/pgsql problem with search_path

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Eugene Chow <gene(at)paragonam(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: pl/pgsql problem with search_path
Date: 2003-09-06 23:21:11
Message-ID: 200309062321.h86NLBA20427@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-bugs by date

Next:From: Eugene ChowDate: 2003-09-06 23:29:45
Subject: Re: pl/pgsql problem with search_path
Previous:From: Dennis BjorklundDate: 2003-09-06 10:39:02
Subject: LOAD broken?

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