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....
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
