pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem inserting composite type values
6+ messages / 3 participants
[nested] [flat]
* Problem inserting composite type values
@ 2006-12-01 19:15 Chris Dunworth <cdunworth@earthcomber.com>
2006-12-01 23:53 ` Re: Problem inserting composite type values Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Dunworth @ 2006-12-01 19:15 UTC (permalink / raw)
To: pgsql-sql
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;
ERROR: column "intval" is of type integer but expression is of type
endtable
HINT: You will need to rewrite or cast the expression
It's taking the entire row coming out of the function (e.g. "(3,30,38)")
and tries to fit it all into just the first column, intVal. I'm
obviously doing something wrong. Is there some way to format the INSERT
so that I can get full rows inserted, with the individual columns
separated out properly (e.g. intVal=3, newVal1=30, newVal2=38)?
I'm still pretty new to all this, so it could be something simple.
Thanks for reading.
Kind Regards,
Chris Dunworth
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem inserting composite type values
2006-12-01 19:15 Problem inserting composite type values Chris Dunworth <cdunworth@earthcomber.com>
@ 2006-12-01 23:53 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-12-02 00:56 ` Re: Problem inserting composite type values Aaron Bono <postgresql@aranya.com>
2006-12-02 03:49 ` Re: Problem inserting composite type values Chris Dunworth <cdunworth@earthcomber.com>
0 siblings, 2 replies; 6+ messages in thread
From: Stephan Szabo @ 2006-12-01 23:53 UTC (permalink / raw)
To: Chris Dunworth <cdunworth@earthcomber.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem inserting composite type values
2006-12-01 19:15 Problem inserting composite type values Chris Dunworth <cdunworth@earthcomber.com>
2006-12-01 23:53 ` Re: Problem inserting composite type values Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2006-12-02 00:56 ` Aaron Bono <postgresql@aranya.com>
2006-12-02 04:02 ` Re: Problem inserting composite type values Chris Dunworth <cdunworth@earthcomber.com>
1 sibling, 1 reply; 6+ messages in thread
From: Aaron Bono @ 2006-12-02 00:56 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Chris Dunworth <cdunworth@earthcomber.com>; pgsql-sql
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
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem inserting composite type values
2006-12-01 19:15 Problem inserting composite type values Chris Dunworth <cdunworth@earthcomber.com>
2006-12-01 23:53 ` Re: Problem inserting composite type values Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-12-02 00:56 ` Re: Problem inserting composite type values Aaron Bono <postgresql@aranya.com>
@ 2006-12-02 04:02 ` Chris Dunworth <cdunworth@earthcomber.com>
2006-12-02 04:20 ` Re: Problem inserting composite type values Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Dunworth @ 2006-12-02 04:02 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
Aaron Bono wrote:
> On 12/1/06, *Stephan Szabo* <sszabo@megazone.bigpanda.com
> <mailto: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.
Cheers,
Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem inserting composite type values
2006-12-01 19:15 Problem inserting composite type values Chris Dunworth <cdunworth@earthcomber.com>
2006-12-01 23:53 ` Re: Problem inserting composite type values Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-12-02 00:56 ` Re: Problem inserting composite type values Aaron Bono <postgresql@aranya.com>
2006-12-02 04:02 ` Re: Problem inserting composite type values Chris Dunworth <cdunworth@earthcomber.com>
@ 2006-12-02 04:20 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 6+ messages in thread
From: Aaron Bono @ 2006-12-02 04:20 UTC (permalink / raw)
To: Chris Dunworth <cdunworth@earthcomber.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem inserting composite type values
2006-12-01 19:15 Problem inserting composite type values Chris Dunworth <cdunworth@earthcomber.com>
2006-12-01 23:53 ` Re: Problem inserting composite type values Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2006-12-02 03:49 ` Chris Dunworth <cdunworth@earthcomber.com>
1 sibling, 0 replies; 6+ messages in thread
From: Chris Dunworth @ 2006-12-02 03:49 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-12-02 04:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-01 19:15 Problem inserting composite type values Chris Dunworth <cdunworth@earthcomber.com>
2006-12-01 23:53 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-12-02 00:56 ` Aaron Bono <postgresql@aranya.com>
2006-12-02 04:02 ` Chris Dunworth <cdunworth@earthcomber.com>
2006-12-02 04:20 ` Aaron Bono <postgresql@aranya.com>
2006-12-02 03:49 ` Chris Dunworth <cdunworth@earthcomber.com>
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