I am trying to cast unnamed row types to a known type
using SQL. There seems to be some syntactic sugar
that is missing or I haven't found it.
A stored procedure can be cast to an unnamed row type
with select * from proc() as (A text, B text, C text).
What I have is a text represenation of a ROW stored
in a column. I want to extract it and store it in a
table or select out the individual columns.
Tables.
f (fid integer, fname text)
qs (qid integer, q text, dom text)
Insert text description of qs into f.fname works with these variations:
insert into f (fname) select textin(record_out(ROW(1,'q1',NULL),0::oid));
insert into f (fname) select textin(record_out(ROW(qs)),0::oid)) from qs;
insert into f (fname) select textin(record_out(ROW(qs)),4638118::oid)) from qs;
Now I want to go the other way and take f.fname and expand it into a qs typed row.
I want to be able to select out the columns and I want to be able to insert it
back into the qs table.
Below are several variations I've tried. The last one is most curious.
=# select (fname).* from found;
ERROR: type text is not composite
=# select (ROW(fname)).* from found;
ERROR: record type has not been registered
=# select (record_in(fname)).* from found;
ERROR: function record_in(text) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.
=# select (record_in(fname),4638118::oid).* from found;
ERROR: syntax error at or near "." at character 39
LINE 1: select (record_in(fname),4638118::oid).* from found;
=# select record_in(textout(fname),4638118::oid) from found;
ERROR: invalid input syntax for integer: "(1,q1,)"
Any help would be appreciated.
The underlying problem is the need for a semi-sane, preferably SQL
version of jagged rows. I would certainly use the original jagged
rows if I could.
Elein
elein@varlena.com
