Thread: Object syntax

Given the following table definitions, and data inserts:

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=# explain select client.addr.postcode from client;
NOTICE:  QUERY PLAN:
Seq Scan on client  (cost=0.00..1.01 rows=1 width=4)
EXPLAIN
dev=# select client.addr.postcode from client;
ERROR:  init_fcache: Cache lookup failed for procedure 18935

What's happening here?  Bug, or am I doing something wrong?

Cheers...

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

Tom,

> I'd recommend the traditional SQL solution: add a primary
> key to the
> address table and reference key values in the client
> table.

What you seem to be telling us is that, other than
inheritance, PGSQL doesn't really support OODB functionality
at this time.  Is that an accurate summary assessment?

-Josh berkus

"Josh Berkus" <josh@agliodbs.com> writes:
> What you seem to be telling us is that, other than
> inheritance, PGSQL doesn't really support OODB functionality
> at this time.  Is that an accurate summary assessment?

What's your definition of "OODB functionality"?  That's the kind
of term that can mean different things to different people...
        regards, tom lane