Re: [GENERAL] computed values in plpgsql
On Mon, Sep 28, 2009 at 4:29 PM, Reid Thompson reid.thomp...@ateb.com 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, '_MM');
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
On Tue, 2009-09-29 at 07:54 -0400, Merlin Moncure wrote:
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
Merlin,
thank you. That appears to work except for one case. If one of the
string literals in the insert happens to have an escaped quote (e.g.
'Joe''s Crabshack') the insert falls over due to quoting.
insert into inquiries(who, when_done, question ) values('FRED',TIMESTAMP
'2009-01-16 09:14:00', 'Where''s my money');
LINE 1: ...901 select ('(1,FRED,2009-01-16 09:14:00,Where's my money...
^
QUERY: insert into inquiries_200901 select ('(1,FRED,2009-01-16
09:14:00,Where's my money)'::inquiries).*
Does anyone know...
if 8.4 would have the same issue?
is there a non-trivial solution to this that could be implemented in the
plpgsql function
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
2009/9/29 Reid Thompson reid.thomp...@ateb.com:
On Tue, 2009-09-29 at 07:54 -0400, Merlin Moncure wrote:
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
Merlin,
thank you. That appears to work except for one case. If one of the
string literals in the insert happens to have an escaped quote (e.g.
'Joe''s Crabshack') the insert falls over due to quoting.
insert into inquiries(who, when_done, question ) values('FRED',TIMESTAMP
'2009-01-16 09:14:00', 'Where''s my money');
LINE 1: ...901 select ('(1,FRED,2009-01-16 09:14:00,Where's my money...
^
QUERY: insert into inquiries_200901 select ('(1,FRED,2009-01-16
09:14:00,Where's my money)'::inquiries).*
you cannot use double quotes. It's not php.
regards
Pavel Stehule
Does anyone know...
if 8.4 would have the same issue?
is there a non-trivial solution to this that could be implemented in the
plpgsql function
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
On Tue, Sep 29, 2009 at 05:42:37PM +0200, Pavel Stehule wrote:
you cannot use double quotes. It's not php.
Normally yes, but *inside* literals you do indeed want double quotes.
I think the OP wants to be using quote_literal here. I.e. instead of:
execute 'insert into foo_something select (''' || new::text || '''::foo).*';
it wants to be closer to:
execute 'insert into foo_something select (foo ' || quote_literal(new) ||
').*;';
but it's a bit fiddly and I may have got that wrong somewhere else.
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
2009/9/29 Sam Mason s...@samason.me.uk:
On Tue, Sep 29, 2009 at 05:42:37PM +0200, Pavel Stehule wrote:
you cannot use double quotes. It's not php.
Normally yes, but *inside* literals you do indeed want double quotes.
I think the OP wants to be using quote_literal here. I.e. instead of:
execute 'insert into foo_something select (''' || new::text || '''::foo).*';
it wants to be closer to:
execute 'insert into foo_something select (foo ' || quote_literal(new) ||
').*;';
but it's a bit fiddly and I may have got that wrong somewhere else.
I afraid so this technique is very buggy. You need unpacked serialised
record. And the result have to be valid sql literal.
postgres=# create type t as (name varchar, addr varchar);
CREATE TYPE
postgres=# select row('Pavel Stehule','Benesov')::t;
row
---
(Pavel Stehule,Benesov)
(1 row)
postgres=# select (row('Pavel Stehule','Benesov')::t).*;
name | addr
---+-
Pavel Stehule | Benesov
(1 row)
but you need 'Pavel Stehule','Benesov'
you cannot apply quote literal on two or more columns. I thing, so
this isn't possible now.
Pavel
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
On Tue, Sep 29, 2009 at 10:49 AM, Reid Thompson reid.thomp...@ateb.com wrote:
On Tue, 2009-09-29 at 07:54 -0400, Merlin Moncure wrote:
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
Merlin,
thank you. That appears to work except for one case. If one of the
string literals in the insert happens to have an escaped quote (e.g.
'Joe''s Crabshack') the insert falls over due to quoting.
insert into inquiries(who, when_done, question ) values('FRED',TIMESTAMP
'2009-01-16 09:14:00', 'Where''s my money');
LINE 1: ...901 select ('(1,FRED,2009-01-16 09:14:00,Where's my money...
^
QUERY: insert into inquiries_200901 select ('(1,FRED,2009-01-16
09:14:00,Where's my money)'::inquiries).*
Does anyone know...
if 8.4 would have the same issue?
is there a non-trivial solution to this that could be implemented in the
plpgsql function
dollar quoting can get you pretty far (bytea values can still be a problem):
create table foo(id int, a text, b text);
insert into foo values (1,'ab''cd', 'abcd');
create or replace function test_insert() returns void as
$$
declare
r text;
begin
select foo::text from foo limit 1 into r;
execute 'insert into foo select ($q$' || r || '$q$::foo).*';
end;
$$ language plpgsql;
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
On Tue, Sep 29, 2009 at 06:30:42PM +0200, Pavel Stehule wrote:
2009/9/29 Sam Mason s...@samason.me.uk:
I may have got that wrong somewhere else.
I afraid so this technique is very buggy. You need unpacked serialised
record.
Hum, I'm not sure what an unpacked serialised record is or why I'd
need one.
And the result have to be valid sql literal.
I'm asking PG to generate one for me, and if it doesn't know what a
valid literal is I don't know who does. Here's a more complete example:
CREATE TABLE t (name varchar, addr varchar);
CREATE TABLE s (name varchar, addr varchar);
CREATE OR REPLACE FUNCTION trig () RETURNS trigger AS $$
BEGIN
EXECUTE 'INSERT INTO s (SELECT (t '||quote_literal(new)||').*);';
RETURN NULL;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER trig BEFORE INSERT ON t
FOR EACH ROW EXECUTE PROCEDURE trig();
INSERT INTO t VALUES ('Pavel Stehule','Benesov');
SELECT * FROM s;
This does the right thing for me in both 8.3 and 8.4, it would also seem
as though it's easy to apply this to the problem the OP was having.
you cannot apply quote literal on two or more columns. I thing, so
this isn't possible now.
Maybe I mis-interpret the problem?
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
On Mon, Sep 28, 2009 at 11:05 AM, Reid Thompson reid.thomp...@ateb.com wrote:
We have a set of tables that we're partitioning by year and month -
e.g. payments_parent, partitioned into payments_200901, payments200902, ...
and inquiries_parent, partitioned into inquiries_200901, inquiries_200902,
...
Each table has a timestamp field import_ts that can be used to partition
the data by month.
The example trigger procs have an IF statement for *each* month that has
a partition - growing as time goes by, so you get some long trigger
procs if you have incoming data over a range
codepre
IF import_ts::date = DATE '2009-01-01' and import_ts::date =
DATE '2009-01-31' THEN
INSERT INTO payments_200901 VALUES(NEW.*)
ELSIF import_ts::date = DATE '2009-02-01' and import_ts::date =
DATE '2009-02-28' THEN
INSERT INTO payments_200902 VALUES(NEW.*)
...
/pre/code
Ditto for each other _parent/partition series.
It would be much simpler to compute the table name from the timestamp,
and re-use the proc for both payments and inquiries tables:
codepre
CREATE OR REPLACE FUNCTION partition_ins_trigger( )
RETURNS TRIGGER AS
$$
DECLARE
insStmt text;
tableName text;
tableDate text;
BEGIN
tableDate := to_char(NEW.import_ts, '_MM');
tableName := replace( TG_RELNAME, '_parent', tableDate );
-- Either
INSERT INTO tableNAme VALUES(NEW.*)
-- OR
EXECUTE 'INSERT INTO ' || tableName || ' VALUES( ' || NEW.* || ')';
RETURN NULL;
END;
$$ language 'plpgsql' volatile;
CREATE TRIGGER payments_partition_ins_trigger BEFORE INSERT ON payments_parent
FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger();
CREATE TRIGGER inquiries_partition_ins_trigger BEFORE INSERT ON
inquiries_parent
FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger();
--
/pre/code
The problem is that I can't use a computed table name in a plpgsql
INSERT, and I haven't found a way to use the NEW.* values in an EXECUTE
the best way to do this is very version dependent. the basic trick is
to use text cast to pass a composite type into the query sting.
one way:
execute 'insert into foo_something select (' || new::text || '::foo).*';
you can try:
execute 'insert into foo_something select ($1::foo).*' using new::text;
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
On Mon, 2009-09-28 at 12:42 -0400, Merlin Moncure wrote:
the best way to do this is very version dependent. the basic trick is
to use text cast to pass a composite type into the query sting.
one way:
execute 'insert into foo_something select (' || new::text || '::foo).*';
you can try:
execute 'insert into foo_something select ($1::foo).*' using new::text;
merlin
thanks, we're using version 8.3.7.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
On Mon, Sep 28, 2009 at 1:29 PM, Reid Thompson reid.thomp...@ateb.com wrote:
On Mon, 2009-09-28 at 12:42 -0400, Merlin Moncure wrote:
the best way to do this is very version dependent. the basic trick is
to use text cast to pass a composite type into the query sting.
one way:
execute 'insert into foo_something select (' || new::text || '::foo).*';
you can try:
execute 'insert into foo_something select ($1::foo).*' using new::text;
merlin
thanks, we're using version 8.3.7.
'execute using' is 8.4 feature. so you have to use the string
concatenation approach. let me fix the errors:
execute 'insert into foo_something select (''' || new::text || '''::foo).*';
:-)
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] computed values in plpgsql
Reid- shoehorn a variable into EXECUTE statement which will be casted as text and then do a substring to acquire extracted results EXECUTE ''INSERT INTO payments_'' ||select * from substring(CAST(import_ts::date AS text) from 0 for 7) || VALUES(NEW.*) || ''; other solutions? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Subject: [GENERAL] computed values in plpgsql From: reid.thomp...@ateb.com To: pgsql-general@postgresql.org Date: Mon, 28 Sep 2009 11:05:06 -0400 We have a set of tables that we're partitioning by year and month - e.g. payments_parent, partitioned into payments_200901, payments200902, ... and inquiries_parent, partitioned into inquiries_200901, inquiries_200902, ... Each table has a timestamp field import_ts that can be used to partition the data by month. The example trigger procs have an IF statement for *each* month that has a partition - growing as time goes by, so you get some long trigger procs if you have incoming data over a range codepre IF import_ts::date = DATE '2009-01-01' and import_ts::date = DATE '2009-01-31' THEN INSERT INTO payments_200901 VALUES(NEW.*) ELSIF import_ts::date = DATE '2009-02-01' and import_ts::date = DATE '2009-02-28' THEN INSERT INTO payments_200902 VALUES(NEW.*) ... /pre/code Ditto for each other _parent/partition series. It would be much simpler to compute the table name from the timestamp, and re-use the proc for both payments and inquiries tables: codepre CREATE OR REPLACE FUNCTION partition_ins_trigger( ) RETURNS TRIGGER AS $$ DECLARE insStmt text; tableName text; tableDate text; BEGIN tableDate := to_char(NEW.import_ts, '_MM'); tableName := replace( TG_RELNAME, '_parent', tableDate ); -- Either INSERT INTO tableNAme VALUES(NEW.*) -- OR EXECUTE 'INSERT INTO ' || tableName || ' VALUES( ' || NEW.* || ')'; RETURN NULL; END; $$ language 'plpgsql' volatile; CREATE TRIGGER payments_partition_ins_trigger BEFORE INSERT ON payments_parent FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger(); CREATE TRIGGER inquiries_partition_ins_trigger BEFORE INSERT ON inquiries_parent FOR EACH ROW EXECUTE PROCEDURE partition_ins_trigger(); -- /pre/code The problem is that I can't use a computed table name in a plpgsql INSERT, and I haven't found a way to use the NEW.* values in an EXECUTE statement. Is there a way to do this, to prevent the long series of IF's in an INSERT trigger proc? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Bing™ brings you maps, menus, and reviews organized in one place. Try it now. http://www.bing.com/search?q=restaurantsform=MLOGENpubl=WLHMTAGcrea=TEXT_MLOGEN_Core_tagline_local_1x1
Re: [GENERAL] computed values in plpgsql
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, '_MM');
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
