casting unnamed row types

From: elein(at)varlena(dot)com (elein)
To: psql-general(at)postgresql(dot)org
Cc: elein <elein(at)varlena(dot)com>
Subject: casting unnamed row types
Date: 2005-04-29 04:59:25
Message-ID: 20050429045925.GL5278@varlena.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)varlena(dot)com

Browse pgsql-general by date

  From Date Subject
Next Message Wenzhe Zhou (wzhou) 2005-04-29 05:01:26 Re:
Previous Message John DeSoi 2005-04-29 04:23:53 Re: Postgresql and VBA vs Python