On 12/1/06, Chris Dunworth <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:
On 12/1/06, Stephan Szabo <[EMAIL PROTECTED]> 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
Hi Aaron --
I had actually tried your approach earlier, or something very similar:
INSERT INTO endTable SELECT et.* FROM (SELECT convertStartToEnd(st.*) FROM
startTable st) AS et;
Which is basically the same as you suggest, plus an alias ("et") for the
subquery that postgresql was asking for. But it gave the same type mismatch
result as I posted about.
Turns out Stephan's suggestion did the trick.
Good to know. I will keep that in mind if I come across that again in the
future.
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
