Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 4DEAC9F946C for ; Sat, 2 Dec 2006 00:20:50 -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 30858-08 for ; Sat, 2 Dec 2006 00:20:44 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.189]) by postgresql.org (Postfix) with ESMTP id D89839F9392 for ; Sat, 2 Dec 2006 00:20:43 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so3841171nfb for ; Fri, 01 Dec 2006 20:20:42 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=FhHD+wI/mGJv+qsQEDbwx0BmQDgvpxBYJ35x6y67U0A75/eWY+wwgKWwQpbJpiFZEciTBm+neJbjLis9rekBsruDqMYItLDiNku4IWFG6uKJbCBpvUBycXqIHHCIomUvQnEDjFG9OZJIaoUUDQpomPrNyb96Sk1csGoOMt11svo= Received: by 10.78.139.1 with SMTP id m1mr5541983hud.1165033242541; Fri, 01 Dec 2006 20:20:42 -0800 (PST) Received: by 10.78.136.6 with HTTP; Fri, 1 Dec 2006 20:20:42 -0800 (PST) Message-ID: Date: Fri, 1 Dec 2006 22:20:42 -0600 From: "Aaron Bono" To: "Chris Dunworth" Subject: Re: Problem inserting composite type values Cc: "Stephan Szabo" , pgsql-sql@postgresql.org In-Reply-To: <4570FAE7.8020903@earthcomber.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7932_25759080.1165033242498" References: <45707F56.70402@earthcomber.com> <20061201155044.H8888@megazone.bigpanda.com> <4570FAE7.8020903@earthcomber.com> X-Google-Sender-Auth: b0a30e99e69c35cd X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/20 X-Sequence-Number: 26976 ------=_Part_7932_25759080.1165033242498 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 12/1/06, Chris Dunworth wrote: > > 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. > > Good to know. I will keep that in mind if I come across that again in the future. -Aaron -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_7932_25759080.1165033242498 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 12/1/06, Chris Dunworth <cdunworth@earthcomber.com> wrote:
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.


Good to know.  I will keep that in mind if I come across that again in the future.

-Aaron

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_7932_25759080.1165033242498--