Re: Problem inserting composite type values

<br /> Stephan Szabo wrote: <blockquote cite="mid20061201155044.H8888@megazone.bigpanda.com" type="cite"><pre
wrap="">OnFri, 1 Dec 2006, Chris Dunworth wrote:
 
 </pre><blockquote type="cite"><pre wrap="">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;   </pre></blockquote><pre wrap="">

I think you'd need something likeINSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM startTablest;
to make it break up the type into its components.
 </pre></blockquote><br /> Yes! That was exactly it. I thought it might have been something simple.<br /><br /> Thanks,
Stephan!<br/><br /> -Chris<br /><br />