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