Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #124

Re: Review question

Show all headers | View raw

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 

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Review question Mladen Gogala <no@email.here.invalid> - 2011-05-16 17:19 +0000
  Re: Review question "Laurenz Albe" <invite@spam.to.invalid> - 2011-05-17 10:34 +0200
    Re: Review question Mladen Gogala <gogala.mladen@gmail.com> - 2011-05-17 12:19 +0000
    Re: Review question Mladen Gogala <no@email.here.invalid> - 2011-05-18 18:19 +0000

csiph-web