Hi;
I have been reading up on object-relational features of Oracle and DB2 and found that one of the big things they have that we don't is a path operator. The idea is that you can use the path operator to follow some subset of foreign keys called refs.
Suppose we have a table (Oracle/DB2 styles here):
CREATE TABLE country (
REF IS OID USER GENERATED,
id int serial not null unique,
name text primary key,
short_name text not null unique
);
CREATE TABLE address (
street_text text not null,
city text not null,
state_province text.
country REF(country)
);
In oracle this might allow you to do something like:
SELECT * from address where address.country.short_name = 'US';
In DB2 this might be done like:
SELECT * FROM address WHERE address->country->short_name = 'US';
I like DB2's approach better because there is no ambiguity between namespace resolution but I don't entirely like the way the refs work as separate from standard foreign keys.
What I am thinking about is a function which accepts a row and a destination table name, looks up foreign keys in and retrieves the row from the other table, returning it, if the table name exists. This could then be mapped to an operator which would avoid some problems. This could then be:
SELECT * FROM address where (address->'country').short_name = 'US'
Or if we also have a continents table:
SELECT * FROM address where (address->'country'->'continent').name = 'North America';
Obviously these examples assume a very small number of address records and are largely contrived from the IBM examples. However you could also do this on a small return set:
select (a->'country').short_name from address where ....;
Or even:
select (a->'country').* from address where......
The next question is whether there is a way to pass country in as an identifier so there is no need to use single quotes. This would make things a little more transparent if possible but I would be happy without this. Eventually it might be kinda useful (for those porting O-R stuff from Oracle or DB2) to have a path operator built in with a concept of a default, implicit join.
Any thoughts? If it can be done in plain SQL and inlined that would be ideal but in the prototyping state, that isn't so important and I expect that it is not.
Best Wishes,
Chris Traves
