From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: [SQL] Object syntax
Date: Thu, 05 Oct 2000 10:32:35 -0400
Message-ID: <28749.970756355@sss.pgh.pa.us> (raw)
In-Reply-To: <7F124BC48D56D411812500D0B7472514061460@fileserver002.intecsystems.co.uk>
References: <7F124BC48D56D411812500D0B7472514061460@fileserver002.intecsystems.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, Michael.Ansley@intec-telecom-systems.com, pgsql-general@postgresql.org
Subject: Re: [SQL] Object syntax
In-Reply-To: <28749.970756355@sss.pgh.pa.us>
References: <7F124BC48D56D411812500D0B7472514061460@fileserver002.intecsystems.co.uk> <28749.970756355@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox