Better way to handle functions doing inserts into dynamically named tables?

From: "Steve Wormley" <steve(at)wormley(dot)com>
To: "Postgres General" <pgsql-general(at)postgresql(dot)org>
Subject: Better way to handle functions doing inserts into dynamically named tables?
Date: 2007-06-28 00:46:00
Message-ID: e15844ad0706271746p5bff635bq9f0daa61aaaf1e42@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

So, I wrote myself a trigger function that when called will
dynamically create new partition tables and rules so that the first
new record for each partition creates the partition. The only bit that
I wanted to see if there was a a better solution was the actual insert
into the new table at the end of the function. I tried just
reinserting into the main table(meta_data_part) but because of the way
functions are handled it doesn't hit the newly created rules. The
interesting bit, is labeled 'the interesting bit'.

(I've included the whole function in case it's useful to anyone.)

CREATE OR REPLACE FUNCTION meta_data_pitf() RETURNS trigger AS $pitf$
DECLARE
month_data varchar;
som timestamptz;
eom timestamptz;
rowdata text;

BEGIN
-- determine the month
month_data := to_char(NEW.data_time,'YYYY_MM');
som := date_trunc('month',NEW.data_time);
eom := date_trunc('month',NEW.data_time + '1 month'::interval);

BEGIN --exception block
-- create the table
EXECUTE $tc$CREATE TABLE p_md.md_$tc$||month_data||$tc$
( CHECK ( data_time >= '$tc$||som||$tc$' AND data_time < '$tc$||eom||$tc$' )
) INHERITS (meta_data_part) ;
$tc$;

-- create the insert rule
EXECUTE $rc$CREATE OR REPLACE RULE meta_data_pir_$rc$||month_data||$rc$ AS
ON INSERT TO meta_data_part WHERE
( data_time >= '$rc$||som||$rc$' AND data_time < '$rc$||eom||$rc$' )
DO INSTEAD INSERT INTO p_md.md_$rc$||month_data||$rc$
VALUES (NEW.*);
$rc$;
EXCEPTION WHEN duplicate_table THEN
-- dont care
END;

SELECT NEW INTO rowdata;

-- now the interesting bit
EXECUTE $ins$INSERT INTO p_md.md_$ins$||month_data||$ins$
SELECT ($ins$||quote_literal(rowdata)||$ins$::meta_data_part).* ; $ins$;

-- skip the next insert... maybe
RETURN NULL;

END;
$pitf$ LANGUAGE plpgsql;

Thanks,
-Steve

Browse pgsql-general by date

  From Date Subject
Next Message Michael Glaesemann 2007-06-28 01:03:55 Re: varchar(n) VS text
Previous Message Tom Lane 2007-06-28 00:42:32 Re: Error Message accessing configuration file