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.
