Thread: partitioned table insert triggers

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

Reece Hart <reece@harts.net> wrote:

>             c := 'INSERT INTO ' || ctable || ' VALUES (NEW.*)';

> 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.)

You need to do so in 8.3:
    EXECUTE 'INSERT INTO ' || ctable ||
            ' VALUES( (' || quote_literal(new) || '::' || ptable || ').*)';

In 8.4, the next release, you will be able to use a better way:
    EXECUTE 'INSERT INTO ' || ctable || ' VALUES( ($1).* )' USING new;

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

You need to do so in 8.3:
    EXECUTE 'INSERT INTO ' || ctable ||
            ' VALUES( (' || quote_literal(new) || '::' || ptable || ').*)';

In 8.4, the next release, you will be able to use a better way:
    EXECUTE 'INSERT INTO ' || ctable || ' VALUES( ($1).* )' USING new;

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0