Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!feeder.news-service.com!kanaga.switch.ch!switch.ch!newscore.univie.ac.at!aconews-feed.univie.ac.at!aconews.univie.ac.at!not-for-mail
From: "Laurenz Albe" <invite@spam.to.invalid>
Newsgroups: comp.databases.postgresql
References: <pan.2011.05.16.17.19.40@email.here.invalid>
Subject: Re: Review question
Date: Tue, 17 May 2011 10:34:29 +0200
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6090
Organization: dienste.wien.at ISP
Message-ID: <1305621292.488979@proxy.dienste.wien.at>
X-Cache: nntpcache 2.3.3 (see http://www.nntpcache.org/)
Lines: 77
NNTP-Posting-Host: 141.203.254.23
X-Trace: 1305621296 aconews.univie.ac.at 11354 141.203.254.23
X-Complaints-To: abuse@univie.ac.at
Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:124

Mladen Gogala wrote:
> I have a partitioned table and the insert statements find the right
> partition through the following trigger function:
>
[static SQL]
>
> The only problem with that function is that I have to change it from time
> to time. Being lazy bum that I am, I came up with the following trigger
> function on my test DB:
>
> CREATE OR REPLACE FUNCTION moreover.moreover_insert_trgfn()
>  RETURNS trigger AS
> $BODY$
> DECLARE
> V_MONTH SMALLINT;
> V_YEAR  VARCHAR(4);
> V_INS   VARCHAR(512):='insert into moreover.moreover_documents_y';
> V_VALS  VARCHAR(128):=' VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,
>                               $13,$14,$15,$16,$17,$18)';
> BEGIN
>    SELECT EXTRACT(MONTH FROM NEW.created_at)  INTO V_MONTH;
>    SELECT EXTRACT(YEAR  FROM NEW.created_at)  INTO V_YEAR;
>    IF (V_MONTH>=10) THEN
>        V_INS:=V_INS||V_YEAR||'m'||V_MONTH||V_VALS;
>    ELSE
>        V_INS:=V_INS||V_YEAR||'m0'||V_MONTH||V_VALS;
>    END IF;
>    execute V_INS USING NEW.document_id,
>                        NEW.dre_reference,
>                        NEW.headline,
>                        NEW.author,
>                        NEW.url,
>                        NEW.rank,
>                        NEW.content,
>                        NEW.stories_like_this,
>                        NEW.internet_web_site_id,
>                        NEW.harvest_time,
>                        NEW.valid_time,
>                        NEW.keyword,
>                        NEW.article_id,
>                        NEW.media_type,
>                        NEW.source_type,
>                        NEW.created_at,
>                        NEW.autonomy_fed_at,
>                        NEW.language;
>    RETURN NULL;
> END;
> $BODY$
>  LANGUAGE plpgsql VOLATILE
>  COST 100;
>
> The function tests perfectly but it is a bit more complex. It should
> route approximately 1 million records every day to the right partition.
> Any thoughts or words of caution?

There will be a performance impact because the dynamic statement must be
prepared whenever it is used, but that should not be too bad with a
simple INSERT statement.

Note that you remove one hard-coded dependency on the partitions, but
introduce another one on the column names.

You might get away shorter and more flexibly with something like that:

...
V_VALS  text:=' VALUES($1.*)';
...
execute V_INS USING NEW;
...

I tried it on PostgreSQL 8.4, I don't know if it works as nicely
on older versions.

Yours,
Laurenz Albe