Date: Fri, 1 Dec 2006 15:53:52 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Chris Dunworth <cdunworth@earthcomber.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem inserting composite type values
In-Reply-To: <45707F56.70402@earthcomber.com>
Message-ID: <20061201155044.H8888@megazone.bigpanda.com>
References: <45707F56.70402@earthcomber.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.