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.
