plpgsql and schema

From: Roberto Pellagatti <roberto(dot)pellagatti(at)ropelsoftware(dot)fastwebnet(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql and schema
Date: 2005-06-22 16:50:40
Message-ID: 42B996E0.2050808@ropelsoftware.fastwebnet.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pablo Baena 2005-06-22 17:43:03 Re: Problem on function returning setof custom type
Previous Message Milorad Poluga 2005-06-22 16:36:14 How to compare the schemas ?