computed values in plpgsql

Поиск
Список
Период
Сортировка
От Reid Thompson
Тема computed values in plpgsql
Дата
Msg-id 1254150306.2708.235.camel@raker.ateb.com
обсуждение исходный текст
Ответы Re: computed values in plpgsql  (Merlin Moncure <mmoncure@gmail.com>)
Re: computed values in plpgsql  (Martin Gainty <mgainty@hotmail.com>)
Re: computed values in plpgsql  (Reid Thompson <reid.thompson@ateb.com>)
Список pgsql-general
We have a set of tables that we're partitioning by year and month -
e.g. payments_parent, partitioned into payments_200901, payments200902, ...
and  inquiries_parent, partitioned into inquiries_200901, inquiries_200902, ...

Each table has a timestamp field import_ts that can be used to partition
the data by month.
The example trigger procs have an IF statement for *each* month that has
a partition - growing as time goes by, so you get some long trigger
procs if you have incoming data over a range

<code><pre>
            IF import_ts::date >= DATE '2009-01-01' and import_ts::date <= DATE '2009-01-31' THEN
                        INSERT INTO payments_200901 VALUES(NEW.*)
            ELSIF import_ts::date >= DATE '2009-02-01' and import_ts::date <= DATE '2009-02-28' THEN
                        INSERT INTO payments_200902 VALUES(NEW.*)
            ...
</pre></code>

Ditto for each other _parent/partition series.
It would be much simpler to compute the table name from the timestamp,
and re-use the proc for both payments and inquiries tables:

<code><pre>
------------------------------------------------------------
CREATE OR REPLACE FUNCTION partition_ins_trigger( )
RETURNS TRIGGER AS
$$
DECLARE
    insStmt  text;
    tableName   text;
    tableDate   text;
BEGIN
     tableDate := to_char(NEW.import_ts, '_yyyyMM');
     tableName := replace( TG_RELNAME, '_parent', tableDate );
-- Either
     INSERT INTO tableNAme VALUES(NEW.*)
-- OR
     EXECUTE 'INSERT INTO ' || tableName || ' VALUES( ' || NEW.* || ')';
     RETURN NULL;
END;

$$ language 'plpgsql' volatile;

CREATE TRIGGER payments_partition_ins_trigger BEFORE INSERT ON payments_parent
   FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger();

CREATE TRIGGER inquiries_partition_ins_trigger BEFORE INSERT ON inquiries_parent
   FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger();
--------------------------------------------------------------
</pre></code>

The problem is that I can't use a computed table name in a plpgsql
INSERT, and I haven't found a way to use the NEW.* values in an EXECUTE
statement. Is there a way to do this, to prevent the long series of IF's
in an INSERT trigger proc?


В списке pgsql-general по дате отправления:

Предыдущее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: bulk inserts
Следующее
От: Filip Rembiałkowski
Дата:
Сообщение: Re: sync structures