From: | "Stuart Cooper" <stuart(dot)cooper(at)gmail(dot)com> |
---|---|
To: | "Chris Coleman" <ChristopherC(at)eurocom(dot)co(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Plperl Question |
Date: | 2007-03-14 22:25:27 |
Message-ID: | 7fc8628a0703141525m3d9fd31fqc222db5802597454@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jaime Silvela | 2007-03-14 22:32:57 | Re: DST failing on 8.1.3 |
Previous Message | Klint Gore | 2007-03-14 22:24:14 | Re: Debugging Server Code |