Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 900CC9FA354 for ; Fri, 1 Dec 2006 20:56:28 -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 07444-02 for ; Fri, 1 Dec 2006 20:56:19 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 49EAF9FA1A7 for ; Fri, 1 Dec 2006 20:56:23 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so2324118ugf for ; Fri, 01 Dec 2006 16:56:21 -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=crIXs+W9DIzhyzwnjbH4hbHZHd/UJ4bqkUj00Do4tn3NlN+lzV+WidzJiSGpTpaoRhcl08LjpfMpeSfx4cAKJWQjAV4UXmxBIWCWXpjkXj8WF/QXdpEeHOU8xrolg3m7wO124oMqW1B7Ac1HBIEB9JPKkzjh811xrujRM3HTKhI= Received: by 10.78.172.20 with SMTP id u20mr5532712hue.1165020981468; Fri, 01 Dec 2006 16:56:21 -0800 (PST) Received: by 10.78.136.6 with HTTP; Fri, 1 Dec 2006 16:56:21 -0800 (PST) Message-ID: Date: Fri, 1 Dec 2006 18:56:21 -0600 From: "Aaron Bono" To: "Stephan Szabo" Subject: Re: Problem inserting composite type values Cc: "Chris Dunworth" , pgsql-sql@postgresql.org In-Reply-To: <20061201155044.H8888@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6796_29730816.1165020981108" References: <45707F56.70402@earthcomber.com> <20061201155044.H8888@megazone.bigpanda.com> X-Google-Sender-Auth: 183f127972b5ae25 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/17 X-Sequence-Number: 26973 ------=_Part_6796_29730816.1165020981108 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_6796_29730816.1165020981108 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_6796_29730816.1165020981108--