Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 218D19F9FB2 for ; Fri, 1 Dec 2006 23:59:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 28601-02 for ; Fri, 1 Dec 2006 23:59:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from elasmtp-scoter.atl.sa.earthlink.net (elasmtp-scoter.atl.sa.earthlink.net [209.86.89.67]) by postgresql.org (Postfix) with ESMTP id D6FD19FA100 for ; Fri, 1 Dec 2006 23:59:14 -0400 (AST) Received: from [69.3.100.14] (helo=[192.168.26.5]) by elasmtp-scoter.atl.sa.earthlink.net with asmtp (Exim 4.34) id 1GqM1A-0004aw-L5; Fri, 01 Dec 2006 22:58:25 -0500 Message-ID: <4570FAE7.8020903@earthcomber.com> Date: Fri, 01 Dec 2006 22:02:47 -0600 From: Chris Dunworth User-Agent: Thunderbird 1.5.0.8 (X11/20061115) MIME-Version: 1.0 To: Aaron Bono CC: Stephan Szabo , pgsql-sql@postgresql.org Subject: Re: Problem inserting composite type values References: <45707F56.70402@earthcomber.com> <20061201155044.H8888@megazone.bigpanda.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------060108000504020205000104" X-ELNK-Trace: f95dbdb9e0397947e4766830010d2531239a348a220c2609e5d065e83a15cbaaf01bdd23113c1fbca8438e0f32a48e08350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 69.3.100.14 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/19 X-Sequence-Number: 26975 This is a multi-part message in MIME format. --------------060108000504020205000104 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Aaron Bono wrote: > On 12/1/06, *Stephan Szabo* > wrote: > > On Fri, 1 Dec 2006, Chris Dunworth wrote: > > > Hi all -- > > > > (huge apologies if this is a duplicate post -- I sent from an > > unsubscribed email account before...) > > > > I have a problem trying to INSERT INTO a table by selecting from a > > function that returns a composite type. (I'm running version > 8.1.4, FYI) > > > > Basically, I have two tables. I want to retrieve rows from one > table and > > store them into the other. The schema of the two tables is not > the same, > > so I use a conversion function (written in plperl) that takes a > row from > > the start table and returns a row from the end table. However, I > can't > > get the insert working. > > > > Here's a simplified example of my real system (must have plperl > > installed to try it): > > > > --------------------------------------- > > -- Read rows from here... > > CREATE TABLE startTable ( intVal integer, textVal text ); > > > > -- ...and store as rows in here > > CREATE TABLE endTable ( intVal integer, newVal1 integer, newVal2 > integer); > > > > -- Some test data for the startTable > > INSERT INTO startTable VALUES ( 1, '10:11'); > > INSERT INTO startTable VALUES ( 2, '20:25'); > > INSERT INTO startTable VALUES ( 3, '30:38'); > > > > -- Note: Takes composite type as argument, and returns composite > type. > > -- This just converts a row of startTable into a row of > endTable, splitting > > -- the colon-delimited integers from textVal into separate integers. > > CREATE OR REPLACE FUNCTION convertStartToEnd(startTable) RETURNS > > endTable AS $$ > > my ($startTable) = @_; > > my @newVals = split(/:/, $startTable->{"textval"}); > > my $result = { "intval"=>$startTable->{"intval"}, > > "newval1"=>@newVals[0], "newval2"=>@newVals[1] }; > > return $result; > > $$ LANGUAGE plperl; > > --------------------------------------- > > > > Now, if I run the following SELECT, I get the results below it: > > > > SELECT convertStartToEnd(st.*) FROM startTable st; > > > > convertstarttoend > > ------------------- > > (1,10,11) > > (2,20,25) > > (3,30,38) > > (3 rows) > > > > This seems OK. But when I try to INSERT the results of this > select into > > the endTable, I get this error: > > > > INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM > startTable st; > > > I think you'd need something like > INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM > startTable > st; > to make it break up the type into its components. > > > INSERT INTO endTable SELECT * FROM (SELECT convertStartToEnd(st.*) > FROM startTable) > > that should work too > Hi Aaron -- I had actually tried your approach earlier, or something very similar: INSERT INTO endTable SELECT et.* FROM (SELECT convertStartToEnd(st.*) FROM startTable st) AS et; Which is basically the same as you suggest, plus an alias ("et") for the subquery that postgresql was asking for. But it gave the same type mismatch result as I posted about. Turns out Stephan's suggestion did the trick. Cheers, Chris --------------060108000504020205000104 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Aaron Bono wrote:
On 12/1/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
On Fri, 1 Dec 2006, Chris Dunworth wrote:

> Hi all --
>
> (huge apologies if this is a duplicate post -- I sent from an
> unsubscribed email account before...)
>
> I have a problem trying to INSERT INTO a table by selecting from a
> function that returns a composite type. (I'm running version 8.1.4, FYI)
>
> Basically, I have two tables. I want to retrieve rows from one table and
> store them into the other. The schema of the two tables is not the same,
> so I use a conversion function (written in plperl) that takes a row from
> the start table and returns a row from the end table. However, I can't
> get the insert working.
>
> Here's a simplified example of my real system (must have plperl
> installed to try it):
>
> ---------------------------------------
> -- Read rows from here...
> CREATE TABLE startTable ( intVal integer, textVal text );
>
> -- ...and store as rows in here
> CREATE TABLE endTable ( intVal integer, newVal1 integer, newVal2 integer);
>
> -- Some test data for the startTable
> INSERT INTO startTable VALUES ( 1, '10:11');
> INSERT INTO startTable VALUES ( 2, '20:25');
> INSERT INTO startTable VALUES ( 3, '30:38');
>
> -- Note: Takes composite type as argument, and returns composite type.
> -- This just converts a row of startTable into a row of endTable, splitting
> -- the colon-delimited integers from textVal into separate integers.
> CREATE OR REPLACE FUNCTION convertStartToEnd(startTable) RETURNS
> endTable AS $$
>    my ($startTable) = @_;
>    my @newVals = split(/:/, $startTable->{"textval"});
>    my $result = { "intval"=>$startTable->{"intval"},
> "newval1"=>@newVals[0], "newval2"=>@newVals[1] };
>    return $result;
> $$ LANGUAGE plperl;
> ---------------------------------------
>
> Now, if I run the following SELECT, I get the results below it:
>
> SELECT convertStartToEnd(st.*) FROM startTable st;
>
> convertstarttoend
> -------------------
> (1,10,11)
> (2,20,25)
> (3,30,38)
> (3 rows)
>
> This seems OK. But when I try to INSERT the results of this select into
> the endTable, I get this error:
>
> INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM startTable st;


I think you'd need something like
INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM startTable
st;
to make it break up the type into its components.

INSERT INTO endTable SELECT * FROM (SELECT convertStartToEnd(st.*) FROM startTable)

that should work too

Hi Aaron --

I had actually tried your approach earlier, or something very similar:

INSERT INTO endTable SELECT et.* FROM (SELECT convertStartToEnd(st.*) FROM startTable st) AS et;

Which is basically the same as you suggest, plus an alias ("et") for the subquery that postgresql was asking for. But it gave the same type mismatch result as I posted about.

Turns out Stephan's suggestion did the trick.

Cheers,
Chris

--------------060108000504020205000104--