RE: Re: [SQL] Object syntax - Mailing list pgsql-general

Given the fact that you have the type (in this case address), and you have the OID, wouldn't it be a short step to dereferencing the OID to a tuple, and putting it in the result?  Or am I being a little optimistic?

>>   -----Original Message-----
>>   From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>>   Sent: 05 October 2000 15:33
>>   To: Michael Ansley
>>   Cc: pgsql-sql@postgresql.org; pgsql-general@postgresql.org
>>   Subject: [GENERAL] Re: [SQL] Object syntax
>>  
>>  
>>   Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
>>   > dev=# create address (addr varchar(50), postcode varchar(9));
>>   > dev=# create client (name varchar(30), addr address);
>>   > dev=# insert into client values ('Michael');
>>   > dev=# insert into address values ('11 Windsor Close',
>>   'RH16 4QR');
>>   > INSERT 18935 1
>>   > dev=# update client set addr = 18935::address;
>>   > dev=# select client.addr.postcode from client;
>>   > ERROR:  init_fcache: Cache lookup failed for procedure 18935
>>  
>>   What you're messing with here is some extremely old and
>>   crufty Berkeley
>>   code.  It does more or less work as-designed in current development
>>   sources, although it seems to be broken in 7.0.2. 
>>   However, the above
>>   is not as-designed.  The underlying idea of table-valued
>>   attributes in
>>   POSTQUEL was that the referencing row would store the OID
>>   of a procedure
>>   that would yield the table value on execution.  So, when you try to
>>   force it as above, you get a complaint that there's no
>>   procedure of that
>>   OID.
>>  
>>   In current sources I can get it to work:
>>  
>>   create function rowxx() returns address as
>>   'select \'11 Windsor Close\'::varchar,\'RH16 4QR\'::varchar '
>>   language 'sql';
>>   CREATE
>>   select oid from pg_proc where proname = 'rowxx';
>>     oid
>>   --------
>>    395222
>>   (1 row)
>>  
>>   insert into client values ('Michael', 395222::address);
>>   INSERT 395223 1
>>   select client.addr.postcode from client;
>>    postcode
>>   ----------
>>    RH16 4QR
>>   (1 row)
>>  
>>   There used to be some support in the parser for
>>   automatically creating
>>   an intermediate function like this if you wrote, say,
>>   insert into client values ('Michael', '{11 Windsor
>>   Close,RH16 4QR}');
>>   (not quite that syntax I suppose, but you get the idea). 
>>   But the parser
>>   support has been dead code for a long time.  It could
>>   probably be fixed
>>   if anyone really wanted to.
>>  
>>   However, this code is an academic toy exercise and quite
>>   unusable for
>>   production purposes.  You certainly don't want to create a
>>   function for
>>   every row of your database, even neglecting the fact that
>>   POSTQUEL never
>>   had any mechanism for getting rid of no-longer-referenced
>>   row generation
>>   functions.  And the implementation suffers from severe performance
>>   problems and memory leaks, even on days when it's working.
>>    Add in the
>>   fact that no one's really bothering to maintain non-SQL POSTQUEL
>>   functionality, and the bottom line is pretty clear.
>>  
>>   I'd recommend the traditional SQL solution: add a primary
>>   key to the
>>   address table and reference key values in the client table.
>>  
>>                      regards, tom lane
>>