Thread: plpgsql and schema

plpgsql and schema

From
Roberto Pellagatti
Date:
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



Re: plpgsql and schema

From
Michael Fuhr
Date:
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/

Re: plpgsql and schema

From
Michael Fuhr
Date:
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/