Skip site navigation (1) Skip section navigation (2)

Re: computed values in plpgsql

From: Reid Thompson <reid(dot)thompson(at)ateb(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: computed values in plpgsql
Date: 2009-09-28 20:29:51
Message-ID: 1254169791.1228.6.camel@raker.ateb.com (view raw or flat)
Thread:
Lists: pgsql-general
On Mon, 2009-09-28 at 11:05 -0400, Reid Thompson wrote:
> We have a set of tables that we're partitioning by year and month - 

> 

We can't seem to quite get it right...
This is our quick stub test.

--------------
-- Tables:
--------------

CREATE TABLE payments (
 id serial,
 payment_name varchar(32),
 payment_type varchar(10),
 when_done timestamp,
 amount numeric(12,3));


CREATE TABLE payments_200901 
   (CHECK (when_done::date >= DATE '2009-01-01' and when_done::date <=
'2009-01-31' ) ) 
   inherits (payments);

CREATE TABLE payments_200902
   (CHECK (when_done::date >= DATE '2009-02-01' and when_done::date <=
'2009-02-28' ) ) 
   inherits (payments);

CREATE TABLE payments_200903
   (CHECK (when_done::date >= DATE '2009-03-01' and when_done::date <=
'2009-03-31' ) ) 
   inherits (payments);

--------------
-- Trigger proc:
---------------

CREATE OR REPLACE FUNCTION partition_ins_trigger( ) 
RETURNS TRIGGER AS
$$
DECLARE
    insStmt  text;
    tableName   text;
    tableDate   text;
BEGIN
     tableDate := to_char(NEW.when_done, '_yyyyMM');
     tableName := TG_RELNAME || tableDate;
     execute 'insert into ' || tableName || ' select (' || new::text ||
')::' || TG_RELNAME || ').*';
      RETURN NULL;

END;
$$ language 'plpgsql' volatile;


--------------
-- Trigger
--------------

CREATE TRIGGER payments_partition_ins_trigger BEFORE INSERT ON payments
   FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger();

--------------
-- Insert
--------------

# insert into payments(payment_name, payment_type, when_done, amount) 
   values('FRED','WIDGET', TIMESTAMP '2009-01-15 14:20:00', 14.50 );

--------------
-- Error
--------------

LINE 1: ... ((7,FRED,WIDGET,"2009-01-15 14:20:00",14.500))::payments).*
                                                                    ^
QUERY:  insert into payments_200901 select ((7,FRED,WIDGET,"2009-01-15
14:20:00",14.500))::payments).*
CONTEXT:  PL/pgSQL function "partition_ins_trigger" line 8 at EXECUTE
statement


----------------
-- If I remove the .* from the function, I get
----------------

# insert into payments(payment_name, payment_type, when_done, amount)
values('FRED','WIDGET','2009-01-15 14:20:00', 14.50 );

ERROR:  column "fred" does not exist
LINE 1: insert into payments select (3,FRED,WIDGET,"2009-01-15 14:20...
                                       ^
QUERY:  insert into payments select (3,FRED,WIDGET,"2009-01-15
14:20:00",14.500)::payments
CONTEXT:  PL/pgSQL function "partition_ins_trigger" line 8 at EXECUTE
statement


-----------------------------

So the ::text is converting NEW, but what it converts into doesn't fly
in the EXECUTE's INSERT....


In response to

Responses

pgsql-general by date

Next:From: Scott MarloweDate: 2009-09-28 20:48:39
Subject: Re: PostgreSQL reads each 8k block - no larger blocks are used - even on sequential scans
Previous:From: InterRobDate: 2009-09-28 20:08:08
Subject: Re: generic modelling of data models; enforcing constraints dynamically...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group