Hi Michael,
Haven't tried it yet .. but THANK YOU !
I will try it later today .... assuming it works it will say us a LOT of
maintenance!
Regards
Paul Newman
-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: 08 March 2006 23:48
To: Paul Newman
Cc: Louis Gonzales; Scott Marlowe; pgsql general
Subject: Re: [GENERAL] Triggers and Multiple Schemas.
On Wed, Mar 08, 2006 at 11:16:55PM -0000, Paul Newman wrote:
> So how can I get the schema name of the calling table trigger and use
it
> in the form of set Search_path at the beginning of the function ?
Here's an example:
CREATE FUNCTION trigfunc() RETURNS trigger AS $$
DECLARE
schemaname text;
oldpath text;
BEGIN
SELECT INTO schemaname n.nspname
FROM pg_namespace AS n
JOIN pg_class AS c ON c.relnamespace = n.oid
WHERE c.oid = TG_RELID;
oldpath := current_setting('search_path');
PERFORM set_config('search_path', schemaname, true);
RAISE INFO 'schema = % oldpath = %', schemaname, oldpath;
PERFORM set_config('search_path', oldpath, false);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE SCHEMA foo;
CREATE SCHEMA bar;
CREATE TABLE foo.tablename (id integer);
CREATE TABLE bar.tablename (id integer);
CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo.tablename
FOR EACH ROW EXECUTE PROCEDURE trigfunc();
CREATE TRIGGER bartrig BEFORE INSERT OR UPDATE ON bar.tablename
FOR EACH ROW EXECUTE PROCEDURE trigfunc();
Now let's insert some records:
test=> INSERT INTO foo.tablename VALUES (1);
INFO: schema = foo oldpath = public
INSERT 0 1
test=> INSERT INTO bar.tablename VALUES (2);
INFO: schema = bar oldpath = public
INSERT 0 1
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match