Message-ID: <4570F7AD.8080007@earthcomber.com>
Date: Fri, 01 Dec 2006 21:49:01 -0600
From: Chris Dunworth <cdunworth@earthcomber.com>
User-Agent: Thunderbird 1.5.0.8 (X11/20061115)
MIME-Version: 1.0
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Problem inserting composite type values
References: <45707F56.70402@earthcomber.com>
	<20061201155044.H8888@megazone.bigpanda.com>
In-Reply-To: <20061201155044.H8888@megazone.bigpanda.com>
Content-Type: multipart/alternative;
	boundary="------------090204030306070704020202"

This is a multi-part message in MIME format.
--------------090204030306070704020202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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


--------------090204030306070704020202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
Stephan Szabo wrote:
<blockquote cite="mid20061201155044.H8888@megazone.bigpanda.com"
 type="cite">
  <pre wrap="">On Fri, 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-&gt;{"textval"});
   my $result = { "intval"=&gt;$startTable-&gt;{"intval"},
"newval1"=&gt;@newVals[0], "newval2"=&gt;@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 like
 INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM startTable
 st;
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>
</body>
</html>

--------------090204030306070704020202--