Re: computed values in plpgsql

Reid-

shoehorn a variable into EXECUTE statement which will be casted as text and then do a substring to acquire extracted results

EXECUTE ''INSERT INTO payments_'' ||select * from substring(CAST(import_ts::date AS text) from 0
for 7) || VALUES(NEW.*) || '';

other solutions?
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Subject: [GENERAL] computed values in plpgsql
> From: reid.thompson@ateb.com
> To: pgsql-general@postgresql.org
> Date: Mon, 28 Sep 2009 11:05:06 -0400
>
> 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?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Bing™ brings you maps, menus, and reviews organized in one place. Try it now.