Re: 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.