Обсуждение: Plperl Question
Hi,
I'm trying to write a plperl function to copy the new row e.g. NEW in
plpgsql into another table. I was looking for a similar effect to the
INSERT INTO blah VALUES (NEW.*)
Syntax that can be used in plpgsql. So fat the best I have come up with
is:
$collist = "";
$vallist = "";
while (($col, $val) = each(%{$_TD->{new}}))
{
$collist .= ($col.",");
#Need to fix issues here with quoting in the value list.
$vallist .= ("'".$val."',");
}
chop($collist);
chop($vallist);
However, this leads to issues with numerical columns being quoted, and
worse still NULL numerical column being entered as '' which results in
"Invalid syntax for integer" errors.
The only solution I can see at present is to look up the type of each
column name in the pg_catalog tables, and based upon this, quote as
necessary.
Does anyone have any better suggestions of how to approach this problem?
Many thanks
Chris
--------------------------------
Chris Coleman
Programmer
Information Systems
Room PKL1 Phone 369
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
This e-mail is confidential and may be read only by the intended recipient.
If you are not the intended recipient, please do not forward, copy or take
any action based on it and, in addition, please delete this email and
inform the sender.
We cannot be sure that this e-mail or its attachments are free from
viruses. In keeping with good computing practice, please ensure that
you take adequate steps to check for any viruses. Before replying
or sending any email to us, please consider that the internet is inherently
insecure and is an inappropriate medium for certain kinds of information.
We reserve the right to access and read all e-mails and attachments
entering or leaving our systems.
Registered office: Eurocom House, Ashbourne Road, Derby DE22 4NB Company number: 01574696.
Answers in place:
> I'm trying to write a plperl function to copy the new row e.g. NEW in
> plpgsql into another table. I was looking for a similar effect to the
> INSERT INTO blah VALUES (NEW.*)
> Syntax that can be used in plpgsql. So fat the best I have come up with
> is:
> $collist = "";
> $vallist = "";
> while (($col, $val) = each(%{$_TD->{new}}))
> {
> $collist .= ($col.",");
>
> #Need to fix issues here with quoting in the value list.
> $vallist .= ("'".$val."',");
> }
> chop($collist);
> chop($vallist);
> However, this leads to issues with numerical columns being quoted, and
> worse still NULL numerical column being entered as '' which results in
> "Invalid syntax for integer" errors.
NULL values will have $val undefined, so you can just avoide adding them to
$collist and $vallist in the first place
next if ( ! defined $val); # don't add NULL values
as the first line of your while loop body will easily acheive this.
Numbers are trickier- you could go with the heuristic that if $val
looks like a number, it is a number and don't quote it. However then
you run into problems with number data in char columns. And then you
start thinking about your pg_catalog solution again.
> The only solution I can see at present is to look up the type of each
> column name in the pg_catalog tables, and based upon this, quote as
> necessary.
Good luck,
Stuart.
Great - that works fine.
If I continue to quote all values that I place into the query string
could I be running into problems later down the line? As far as I can
work out then PG will try to convert the quoted values to whatever the
correct datatype for the column is anyway.
This seems much cheaper than trying to determine the data types by
querying the pg_catalog tables, and much cleaner assuming it is free
from any pitfalls...
Thanks again,
Chris
-----Original Message-----
From: Stuart Cooper [mailto:stuart.cooper@gmail.com]
Sent: 14 March 2007 22:25
To: Chris Coleman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Plperl Question
Answers in place:
> I'm trying to write a plperl function to copy the new row e.g. NEW in
> plpgsql into another table. I was looking for a similar effect to the
> INSERT INTO blah VALUES (NEW.*)
> Syntax that can be used in plpgsql. So fat the best I have come up
with
> is:
> $collist = "";
> $vallist = "";
> while (($col, $val) = each(%{$_TD->{new}}))
> {
> $collist .= ($col.",");
>
> #Need to fix issues here with quoting in the value list.
> $vallist .= ("'".$val."',");
> }
> chop($collist);
> chop($vallist);
> However, this leads to issues with numerical columns being quoted, and
> worse still NULL numerical column being entered as '' which results in
> "Invalid syntax for integer" errors.
NULL values will have $val undefined, so you can just avoide adding them
to
$collist and $vallist in the first place
next if ( ! defined $val); # don't add NULL values
as the first line of your while loop body will easily acheive this.
Numbers are trickier- you could go with the heuristic that if $val
looks like a number, it is a number and don't quote it. However then
you run into problems with number data in char columns. And then you
start thinking about your pg_catalog solution again.
> The only solution I can see at present is to look up the type of each
> column name in the pg_catalog tables, and based upon this, quote as
> necessary.
Good luck,
Stuart.
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
This e-mail is confidential and may be read only by the intended recipient.
If you are not the intended recipient, please do not forward, copy or take
any action based on it and, in addition, please delete this email and
inform the sender.
We cannot be sure that this e-mail or its attachments are free from
viruses. In keeping with good computing practice, please ensure that
you take adequate steps to check for any viruses. Before replying
or sending any email to us, please consider that the internet is inherently
insecure and is an inappropriate medium for certain kinds of information.
We reserve the right to access and read all e-mails and attachments
entering or leaving our systems.
Registered office: Eurocom House, Ashbourne Road, Derby DE22 4NB Company number: 01574696.