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@xxxxxxxxx> 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
