Re: Dynamic pgplsql triggers - Mailing list pgsql-general

I have scanned the archives and found the following message from 2004
dealing with v7.4, however it doesn't solve all my problems:

http://archives.postgresql.org/pgsql-sql/2004-07/msg00208.php

Here is a brief example.  I have a table db with a merge trigger given
below, shamelessly stolen from Example 36-1 in the docs.  When an
insert occurs, the number_seen is updated if the id/content are the
same, otherwise the new record is inserted.

-- Note that id is not unique
CREATE TABLE db (
    id INTEGER,
    content BYTEA,
    number_seen INTEGER
);

CREATE OR REPLACE FUNCTION merge_db() RETURNS TRIGGER AS $$
  BEGIN
    UPDATE db SET number_seen = number_seen + NEW.number_seen
      WHERE id = NEW.id AND content = NEW.content;
    IF FOUND THEN
      --Update row
      RETURN NULL;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER db_merge_db BEFORE INSERT ON db FOR EACH ROW EXECUTE
PROCEDURE merge_db();

This works like a charm.  My 'db' table is getting rather large,
however, and I started to partition it based on the non-unique column
'id'

CREATE TABLE db_1 (
  CONSTRAINT partition_id_1 CHECK (id =1);
) INHERITS db;

CREATE TABLE db_2 .... db_100

However now I am at a loss as to how to create a single trigger
function that I can use to trigger all of the partition tables.
Basically, how do I set the db name to be dynamic?  I tried the naive
approach of just replacing 'db' with TG_RELNAME, i.e.:

    UPDATE TG_RELNAME SET number_seen = number_seen + NEW.number_seen
      WHERE id = NEW.id AND content = NEW.content;

but of course this gives me an error about $1, which is what I
figured, given that the table name can't be a parameter (right?):
'ERROR:  syntax error at or near "$1" at character 9'

So then I tried to make the statement dynamic, i.e.:

    EXECUTE 'UPDATE ' || TG_RELNAME || '
      SET number_seen = number_seen + NEW.number_seen
      WHERE id = NEW.id AND content = NEW.content;

but this gives the error: 'ERROR:  NEW used in query that is not in a
rule'.  This seems a little confusing as NEW should be visible to
trigger functions, but I assume that it has something to do with the
EXECUTE and how the planner couldn't pre-plan the SQL.

So I plod on, and try and make all the NEW args dynamic, i.e.:

    EXECUTE 'UPDATE ' || TG_RELNAME || '
      SET number_seen = number_seen + ' || NEW.number_seen || '
      WHERE id = ' || NEW.id || ' AND content = ' || NEW.content;

However now I get the error: 'ERROR:  operator does not exist:  bytea
|| ip4'.  I think I understand what is going on ... that bytea doesn't
have a text representation, right?

So I'm not quite sure where to go from here.  How do I make the UPDATE
statement in the trigger function operate against the table on which
the trigger was fired, while at the same time passing in the values in
NEW?

Any help would be greatly appreciated.

Thanks!



CREATE OR REPLACE FUNCTION merge_db() RETURNS TRIGGER AS $$
  BEGIN
    UPDATE db SET number_seen = number_seen + NEW.number_seen
      WHERE id = NEW.id AND content = NEW.content;
    IF FOUND THEN
      --Update row
      RETURN NULL;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE PLPGSQL;


On 7/31/06, Worky Workerson <worky.workerson@gmail.com> wrote:
> I'm trying to trigger a whole bunch of partitions at once (initial DB
> setup) using the same plpgsql trigger.  The trigger is basically the
> merge trigger in the docs (i.e. UPDATE IF NOT FOUND RETURN NEW ...).
>
> I need to use the TG_RELNAME variable within the "UPDATE" in the
> trigger so that I can use the same function to trigger all of the
> partitions (correct?), the problem is that I can't quite figure out
> how.  I figure that I will have to use EXECUTE on a string that I
> build up, right?  The problem that I'm having with this approach is
> that some of the columns of NEW don't have a text conversion, and I'm
> getting an error whenever the trigger fires.  Is there a way around
> this and/or a better way to trigger a bunch of partitions with the
> same function?
>
> Thanks!
> -Worky
>