Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by hub.org (8.10.1/8.10.1) with ESMTP id e95EXs751242; Thu, 5 Oct 2000 10:33:54 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.0/8.11.0) with ESMTP id e95EWaB28752; Thu, 5 Oct 2000 10:32:36 -0400 (EDT) To: Michael Ansley cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: Re: [SQL] Object syntax In-reply-to: <7F124BC48D56D411812500D0B7472514061460@fileserver002.intecsystems.co.uk> References: <7F124BC48D56D411812500D0B7472514061460@fileserver002.intecsystems.co.uk> Comments: In-reply-to Michael Ansley message dated "Thu, 05 Oct 2000 14:19:40 +0100" Date: Thu, 05 Oct 2000 10:32:35 -0400 Message-ID: <28749.970756355@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200010/118 Michael Ansley 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