From: | Reece Hart <reece(at)harts(dot)net> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | partitioned table insert triggers |
Date: | 2008-06-21 17:29:58 |
Message-ID: | 1214069398.6824.16.camel@snafu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm trying to create a date-partitioned version of the postgres_log
table that is described here:
http://www.postgresql.org/docs/current/static/runtime-config-logging.html
In lieu of hardcoding the partition table names in the trigger, as shown
in the table partitioning docs, I wanted to dynamically compute the
table name. I tried:
CREATE OR REPLACE FUNCTION postgres_log_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
c text;
ptable text := 'postgres_log';
ctable text := ptable || '_' ||
to_char(NEW.session_start_time, 'YYYY_MM');
BEGIN
c := 'INSERT INTO ' || ctable || ' VALUES (NEW.*)';
EXECUTE c;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
I get:
ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "INSERT INTO
measurement_postgres_log_2008_05 VALUES (NEW.*)"
PL/pgSQL function "postgres_log_insert_trigger" line 7 at
EXECUTE statement
I didn't really think that would work exactly for the reason given, but
I didn't know what else to try.
Now that my only idea is gone, the question for y'all is: how?
(Okay, I actually do have one more idea: construct the insert values
list with quote_literal. I'm hoping there's a better way.)
Thanks,
Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Thoen | 2008-06-21 17:30:43 | Abnormal termination of PostgreSQL |
Previous Message | Tom Lane | 2008-06-21 16:56:13 | Re: System in Recovery Mode But No Activity |