On Mon, Sep 28, 2009 at 11:05 AM, Reid Thompson <reid.thompson@xxxxxxxx> wrote:
> 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
the best way to do this is very version dependent. the basic trick is
to use text cast to pass a composite type into the query sting.
one way:
execute 'insert into foo_something select (' || new::text || '::foo).*';
you can try:
execute 'insert into foo_something select ($1::foo).*' using new::text;
merlin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
