Skip site navigation (1) Skip section navigation (2)

Re: computed values in plpgsql

From: Martin Gainty <mgainty(at)hotmail(dot)com>
To: <reid(dot)thompson(at)ateb(dot)com>, "pgsql-general(at)postgresql(dot)org"<pgsql-general(at)postgresql(dot)org>
Subject: Re: computed values in plpgsql
Date: 2009-09-28 19:10:06
Message-ID: BLU142-W379AD787E7397D79CD06BDAED60@phx.gbl (view raw, whole thread or download thread mbox)
Lists: pgsql-general

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é
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
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(dot)thompson(at)ateb(dot)com
> To: pgsql-general(at)postgresql(dot)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( ) 
> $$
>     insStmt  text;
>     tableName   text;
>     tableDate   text;
>      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(at)postgresql(dot)org)
> To make changes to your subscription:
Bing™  brings you maps, menus, and reviews organized in one place.   Try it now.

In response to

pgsql-general by date

Next:From: Mike ChristensenDate: 2009-09-28 19:31:55
Subject: Re: Functions returning multiple rowsets
Previous:From: Oleg BartunovDate: 2009-09-28 18:49:54
Subject: Re: generic modelling of data models; enforcing constraints dynamically...

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group