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

Groups > comp.databases.postgresql > #123

Review question

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — 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