Re: How hard would a "path" operator be to implement in PostgreSQL - Mailing list pgsql-general

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