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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
