Re: [GENERAL] Triggers and Multiple Schemas.

[Michael Fuhr]

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