Re: need simple strategy for universal extension table

On Tue, 09 Nov 2004 19:20:20 GMT, TJ Talluto <tj(at)getlostspammers(dot)com> wrote:
> This new table would act as a universal extension table. Instead of having
> FKs back to any particular table, it would contain regular keys that point
> back to whatever table::record is its source.
>
> I was thinking of using two keys only:
>
> xmOwnerInfo
> COL1 PK* oidParentTable
> COL2 PK* oidParentTablesRecord
> COL3-15 [attribs]
>

I am doing something similar to this, though at this point I'm not
using it for storing creator/updator fields. I need to be able to
group any arbitrary set of rows from multiple tables together and
apply sets of attributes to them. I have a central 'entity' table
that holds the table name and the value of the 'id' column from that
table. Then I attach two triggers to each table whos rows I want to
track. I am using tablename and a BIGSERIAL 'id' column because OIDs
on tables

1) are only a 32 bit INT and I may very well wrap around on them,
whereas a BIGSERIAL is a 64 bit INT and

2) the fact that OIDs may go away in a future version of PG.

Here's exactly what I'm doing (NOTE: this is for 8.0beta, so you will
have to adjust the quoting on the trigger functions for 7.x):

CREATE SCHEMA func;
CREATE SCHEMA entity;

CREATE TABLE entity.authority_list (
id BIGSERIAL PRIMARY KEY,
entity BIGINT,
entity_type text
) WITHOUT OIDS;

CREATE FUNCTION func.add_entity_entry () RETURNS TRIGGER AS $func$
BEGIN
INSERT INTO entity.authority_list (entity,entity_type)
VALUES (NEW.id,TG_ARGV[0] || '.' || TG_RELNAME);
RETURN NEW;
END;
$func$ LANGUAGE 'plpgsql';

CREATE FUNCTION func.remove_entity_entry () RETURNS TRIGGER AS $func$
BEGIN
DELETE FROM entity.authority_list
WHERE entity = OLD.id AND
entity_type = TG_ARGV[0] || '.' || TG_RELNAME;
RETURN OLD;
END;
$func$ LANGUAGE 'plpgsql';

CREATE TABLE someschema.sometable ( id BIGSERIAL, name TEXT );

CREATE TRIGGER sometable_add_entity_trig
AFTER INSERT ON someschema.sometable
FOR EACH ROW
EXECUTE PROCEDURE func.add_entity_entry(someschema);

CREATE TRIGGER sometable_remove_entity_trig
BEFORE DELETE ON someschema.sometable
FOR EACH ROW
EXECUTE PROCEDURE func.remove_entity_entry(someschema);

I am supplying the schema name to the trigger because the relation
name passed in as TG_RELNAME is the schema unqualified table name and
I have the same table name in several schemas.

Any comments on any of this would be very welcome.

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer