On Tue, 09 Nov 2004 19:20:20 GMT, TJ Talluto <tj@getlostspam mers.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* oidParentTables Record
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.authorit y_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.authorit y_list (entity,entity_ type)
VALUES (NEW.id,TG_ARGV[0] || '.' || TG_RELNAME);
RETURN NEW;
END;
$func$ LANGUAGE 'plpgsql';
CREATE FUNCTION func.remove_ent ity_entry () RETURNS TRIGGER AS $func$
BEGIN
DELETE FROM entity.authorit y_list
WHERE entity = OLD.id AND
entity_type = TG_ARGV[0] || '.' || TG_RELNAME;
RETURN OLD;
END;
$func$ LANGUAGE 'plpgsql';
CREATE TABLE someschema.some table ( id BIGSERIAL, name TEXT );
CREATE TRIGGER sometable_add_e ntity_trig
AFTER INSERT ON someschema.some table
FOR EACH ROW
EXECUTE PROCEDURE func.add_entity _entry(somesche ma);
CREATE TRIGGER sometable_remov e_entity_trig
BEFORE DELETE ON someschema.some table
FOR EACH ROW
EXECUTE PROCEDURE func.remove_ent ity_entry(somes chema);
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
mr*******@gmail .com
GPLS -- PINES Development
Database Developer
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster