Object syntax

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

is there a downside to use and int as part of
of a compound key? TIA, -ar

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

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?

"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

Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:

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?

Well, other than the fact that OIDs are not "dereferenceable" ---
they're serial numbers, not pointers --- that's doable in theory.
It'd break backward compatibility with POSTQUEL but I doubt anyone
would complain about that. Feel free to start hacking ...

regards, tom lane