Re: need simple strategy for universal extension table

On Tue, 09 Nov 2004 19:20:20 GMT, TJ Talluto <tj@getlostspammers.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@gmail.com
GPLS -- PINES Development
Database Developer