I have a partitioned table and the insert statements find the right
partition through the following trigger function:

CREATE OR REPLACE FUNCTION moreover.moreover_insert_trgfn()
RETURNS trigger AS
$BODY$
BEGIN
IF ( NEW.created_at >= TIMESTAMP '2011-04-01 00:00:00' AND
NEW.created_at < TIMESTAMP '2011-05-01 00:00:00' ) THEN
INSERT INTO moreover.moreover_documents_y2011m04 VALUES (NEW.*);
ELSIF ( NEW.created_at >= TIMESTAMP '2011-05-01 00:00:00' AND
NEW.created_at < TIMESTAMP '2011-06-01 00:00:00' ) THEN
INSERT INTO moreover.moreover_documents_y2011m05 VALUES (NEW.*);
ELSIF ( NEW.created_at >= TIMESTAMP '2011-06-01 00:00:00' AND
NEW.created_at < TIMESTAMP '2011-07-01 00:00:00' ) THEN
INSERT INTO moreover.moreover_documents_y2011m06 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range.
Fix the moreover_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

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?

--
http://mgogala.byethost5.com

Mladen Gogala wrote:
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

On Tue, 17 May 2011 10:34:29 +0200, Laurenz Albe wrote:


Thanks, Laurenz. I will try that.



--
http://mgogala.byethost5.com

On Tue, 17 May 2011 10:34:29 +0200, Laurenz Albe wrote:

Works as a charm on 9.0.4.



--
http://mgogala.freehostia.com