From: Chris Dunworth <cdunworth@earthcomber.com>
To: pgsql-sql@postgresql.org
Subject: Problem inserting composite type values
Date: Fri, 01 Dec 2006 13:15:34 -0600
Message-ID: <45707F56.70402@earthcomber.com> (raw)
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
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: cdunworth@earthcomber.com
Subject: Re: Problem inserting composite type values
In-Reply-To: <45707F56.70402@earthcomber.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