Re: How hard would a "path" operator be to implement in PostgreSQL

Hi all;

So I found an interesting and relatively manageable way of doing this.

Suppose we have an inventory table:

CREATE TABLE inventory_item (
id serial primary key,
cogs_account_id int references account(id),
inv_account_id int references account(id),
income_account_id int references account(id),
sku text not null,
description text,
last_cost numeric, -- null if never purchased
sell_price numeric not null,
active bool not null default true
);

Now we want to be able to add pointers to this table in other tables
without adding a lot of decentralized code. So what we do is:

CREATE TABLE joins_inventory_item (
inventory_item_id int
);

Then we create a table method function like:

CREATE FUNCTION inventory_item(joins_inventory_item) RETURNS inventory_item
LANGUAGE SQL AS $$
SELECT * FROM inventory_item where id = $1.inventory_item_id;
$$;

Then any table which inherits joins_inventory_item gets a path back.
So for example:

CREATE TABLE inventory_barcode (
barcode text primary key;
FOREIGN KEY inventory_item_id REFERENCES inventory_item(id)
);

Then we can:

select (bc.inventory_item).sku FROM inventory_barcode bc WHERE barcode
= '12345';

Best Wishes,
Chris Travers