Обсуждение: computed values in plpgsql
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
<code><pre>
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:
<code><pre>
------------------------------------------------------------
CREATE OR REPLACE FUNCTION partition_ins_trigger( )
RETURNS TRIGGER AS
$$
DECLARE
insStmt text;
tableName text;
tableDate text;
BEGIN
tableDate := to_char(NEW.import_ts, '_yyyyMM');
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?
On Mon, Sep 28, 2009 at 11:05 AM, Reid Thompson <reid.thompson@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
>
> <code><pre>
> 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:
>
> <code><pre>
> ------------------------------------------------------------
> CREATE OR REPLACE FUNCTION partition_ins_trigger( )
> RETURNS TRIGGER AS
> $$
> DECLARE
> insStmt text;
> tableName text;
> tableDate text;
> BEGIN
> tableDate := to_char(NEW.import_ts, '_yyyyMM');
> 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
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.
On Mon, Sep 28, 2009 at 1:29 PM, Reid Thompson <reid.thompson@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
Reid-
shoehorn a variable into EXECUTE statement which will be casted as text and then do a substring to acquire extracted results
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.
> Subject: [GENERAL] computed values in plpgsql
> From: reid.thompson@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
>
> <code><pre>
> 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:
>
> <code><pre>
> ------------------------------------------------------------
> CREATE OR REPLACE FUNCTION partition_ins_trigger( )
> RETURNS TRIGGER AS
> $$
> DECLARE
> insStmt text;
> tableName text;
> tableDate text;
> BEGIN
> tableDate := to_char(NEW.import_ts, '_yyyyMM');
> 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.
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 0other solutions?
for 7) || VALUES(NEW.*) || '';
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.thompson@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
>
> <code><pre>
> 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:
>
> <code><pre>
> ------------------------------------------------------------
> CREATE OR REPLACE FUNCTION partition_ins_trigger( )
> RETURNS TRIGGER AS
> $$
> DECLARE
> insStmt text;
> tableName text;
> tableDate text;
> BEGIN
> tableDate := to_char(NEW.import_ts, '_yyyyMM');
> 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.
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....
On Mon, Sep 28, 2009 at 4:29 PM, Reid Thompson <reid.thompson@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, '_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
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
2009/9/29 Reid Thompson <reid.thompson@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
>
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/
2009/9/29 Sam Mason <sam@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
>
On Tue, Sep 29, 2009 at 10:49 AM, Reid Thompson <reid.thompson@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', 'ab"cd');
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
On Tue, Sep 29, 2009 at 06:30:42PM +0200, Pavel Stehule wrote:
> 2009/9/29 Sam Mason <sam@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/