Thread: plpgsql and schema

The problem:

I've two tables, both called "test" on two different schemas, let's say
schema1 and schema2.

I need to write a function like (just a simple example)

CREATE OR REPLACE FUNCTION foo()
  RETURNS int4 AS
$BODY$
declare
   num int4;
begin
    select into num count(*) from test;
   return num;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

I need something like:
SET search_path = schema1,public;
select foo();
-- get the number of rows of schema1.test


SET search_path = schema2,public;
select foo();
-- get the number of rows of schema2.test

The problem is that i get always the number of  rows from the table in
the schema that was current when I created the function.

How can I obtain what I need ?? I cannot write 2 different functions (my
db is really more complex, with lots of functions and triggers that need
this kind of access).

Thanks to everybody
Roberto

On Wed, Jun 22, 2005 at 06:50:40PM +0200, Roberto Pellagatti wrote:
>
> The problem is that i get always the number of  rows from the table in
> the schema that was current when I created the function.

PL/pgSQL caches its query plans, so subsequent calls to the function
use the plan created on the first call.  You can avoid this plan
caching by using EXECUTE.

http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Note what the documentation says about not being able to get the
result of an EXECUTE query directly.  A couple of workarounds are
mentioned.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Thu, Jun 23, 2005 at 09:33:27AM -0600, Michael Fuhr wrote:
>
> http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Note what the documentation says about not being able to get the
> result of an EXECUTE query directly.  A couple of workarounds are
> mentioned.

BTW, 8.1 will have EXECUTE INTO, which stores the first row of the
result into a row variable or variable list.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/