Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search archives
  Advanced Search

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: Thu, 28 Apr 2005 21:59:25 -0700
  • Message-id: <20050429045925.GL5278@varlena.com> <text/plain>

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




Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group