On Mon, Sep 28, 2009 at 4:29 PM, Reid Thompson <reid.thompson@xxxxxxxx> wrote:
> 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...
you are missing some quotes in there. also, don't use 'values', use
select. see my example above:
execute 'insert into foo_something select (''' || new::text || '''::foo).*';
the actual query should look like:
insert into payments(payment_name, payment_type, when_done, amount)
select ('(7,FRED,WIDGET,"2009-01-15 14:20:00",14.500)'::payments).*;
merlin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
