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.
Yes! That was exactly it. I thought it might have been something simple. Thanks, Stephan! -Chris
