RE: Re: [SQL] Object syntax

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(at)sss(dot)pgh(dot)pa(dot)us]
>> Sent: 05 October 2000 15:33
>> To: Michael Ansley
>> Cc: pgsql-sql(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
>> Subject: [GENERAL] Re: [SQL] Object syntax
>>
>>
>> Michael Ansley <Michael(dot)Ansley(at)intec-telecom-systems(dot)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
>>