On 12/23/05, Karsten Hilbert <[EMAIL PROTECTED]> wrote: > On Thu, Dec 22, 2005 at 05:05:49PM -0700, Trent Shipley wrote: > > > On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote: > > > > I would assume quite a few people would use table > > > inheritance in a simple way were it available in a more > > > convenient fashion: to transport fields, primary and foreign > > > keys to child tables. > > > > I am not clear on why this sort of scenario benefits more from CREATE > > TABLE's > > "INHERITS" clause than the "LIKE" clause > Because the inherited fields are aggregated in the parent > table. > > Imagine a database: > > create table narrative_base ( > narrative text > ); > > create table memo ( > author text default CURRENT_USER > ) inherits (narrative_base); > > create table ads ( > fk_campaign integer references campaigns(pk) > ) inherits (narrative_base); > > ... more child tables > > ... even more child tables
We use something very similar to this to track user transactions (circulation of material, billings, etc.) in our (developing) ILS (Integrated Library System), OpenILS. But we take it even further with multiple levels of inheritance (simplified): CREATE TABLE payment ( pid serial, xact bigint, ptime timestamptz, pamount numeric(10,2) ); CREATE TABLE bnm_payment ( -- "brick-n-mortar" accepting_user int ) INHERITS (payment); CREATE TABLE bnm_desk_payment ( cash_drawer_id text ) INHERITS (bnm_payment); CREATE TABLE check_payment ( check_number text ) INHERITS (bnm_desk_payment); ... and so on ... > > Then we go on merrily inserting all sorts of stuff into the > narrative_base child tables for two years. > > Now the boss asks me: "Has anyone ever written anything with > 'PostgreSQL' in it in our company ?" > > So I go > > select tableoid, * from narrative_base where narrative ilike '%postgresql'; > > et voila. I don't have to remember all the tables > potentially containing narrative and join them. Precisely. We can report on daily payments at each of the "levels" all the way down to payment type, or just get a total for the cash drawers, or a grand total. Billing line items are structured similarly, so it's also very easy to grab a summary bill for a user and "explode" it for a detailed view using tableoid. > > Now, if this properly transporter primary and foreign keys > to child tables I could add > > pk serial primary key > > to narrative_base and be done with primary keys for all > children. > > Get the drift ? While I originally wanted this as well, by using a serial for the "pid" field in the root table you've essentially go that. While cross-table unique indexes aren't available now, I know that some smart people are thinking about them. Most of the time it comes up in relation to O*'s "global indexes" on partitioned tables, and in that sense is not of much use due to performance implications, but I think /our/ use makes a strong case for such a beast. That said, I believe I have a workaround that may suffice if you absolutely require constraint enforced globally unique PKEYs. This example uses the pid field from the root table (that is inherited everywhere) to track uniqueness. CREATE TABLE payment_entities ( id bigint primary key, toid oid -- tableoid ); CREATE FUNCTION global_unique_payment_entity RETURNS TRIGGER AS $$ BEGIN BEGIN insert into entities (id, toid) values (NEW.pid, TG_RELID); EXCEPTION WHEN UNIQUE_VIOLATION THEN RAISE EXCEPTION 'Ack! Key % already exists as a payment ID', NEW.pid; END; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER global_unique_entity_payment_trig BEFORE INSERT ON cash_payment FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity(); CREATE TRIGGER global_unique_entity_payment_trig BEFORE INSERT ON check_payment FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity(); CREATE TRIGGER global_unique_entity_payment_trig BEFORE INSERT ON credit_card_payment FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity(); etc... That doesn't cover UPDATEs of course, but that should be easy enough to do. It does, however, give you a simple "type" lookup table if you happen to have a pid in hand and want to know what it is. Thoughts? > > Karsten > -- > GPG key ID E4071346 @ wwwkeys.pgp.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org