From: Aaron Bono <postgresql@aranya.com>
To: Chris Dunworth <cdunworth@earthcomber.com>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem inserting composite type values
Date: Fri, 1 Dec 2006 22:20:42 -0600
Message-ID: <bf05e51c0612012020o62481cep20c82cf88a441740@mail.gmail.com> (raw)
In-Reply-To: <4570FAE7.8020903@earthcomber.com>
References: <45707F56.70402@earthcomber.com>
<20061201155044.H8888@megazone.bigpanda.com>
<bf05e51c0612011656q44cf1f04rd6b1bf1bf627b48a@mail.gmail.com>
<4570FAE7.8020903@earthcomber.com>
On 12/1/06, Chris Dunworth <cdunworth@earthcomber.com> wrote:
>
> Aaron Bono wrote:
>
> On 12/1/06, Stephan Szabo <sszabo@megazone.bigpanda.com> 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.comhttp://codeelixir.com
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, cdunworth@earthcomber.com, sszabo@megazone.bigpanda.com
Subject: Re: Problem inserting composite type values
In-Reply-To: <bf05e51c0612012020o62481cep20c82cf88a441740@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox