On 10/22/15 11:37 AM, Karsten Hilbert wrote:
On Mon, Oct 19, 2015 at 04:25:39AM -0500, Jim Nasby wrote:
BTW, if there's any interest, I have code that sets up a non-inheritance
table specifically for doing foreign keys to the inheritance parent. It
doesn't support updates right now, but should be fully safe from a FK
standpoint.
Can you tell me more ?
I create a '_fk' table that looks like this:
CREATE TABLE _lead.lead_fk(
lead_id int NOT NULL PRIMARY KEY
, organic_lead_id int CONSTRAINT organic_lead_id_sanity CHECK( organic_lead_id IS NULL OR organic_lead_id = lead_id )
, some_lead_provider_lead_id int CONSTRAINT some_lead_provider_lead_id_sanity CHECK( some_lead_provider_lead_id IS NULL OR some_lead_provider_lead_id = lead_id )
-- TODO , CHECK( only one blah_lead_id field is NOT NULL using count_nulls() from PGXN )
);
Then each table that inherits from lead.lead (as well as lead.lead
itself) has:
, FOREIGN KEY (lead_id) REFERENCES _lead.lead_fk DEFERRABLE INITIALLY DEFERRED
That FK is in place to ensure that when a lead record is inserted, a
corresponding row is inserted into _lead.lead_fk as well. That insert is
handled by this trigger function:
CREATE OR REPLACE FUNCTION _lead.tg_lead_fk(
) RETURNS trigger LANGUAGE plpgsql
-- !!!!!!!!!
SECURITY DEFINER SET search_path = pg_catalog
-- !!!!!!!!!
AS $body$
BEGIN
EXECUTE format(
$$INSERT INTO _lead.lead_fk( lead_id, %I ) VALUES( $1, $1 )$$
, TG_TABLE_NAME || '_id'
)
USING NEW.lead_id
;
RETURN NEW;
END
$body$;
Finally, a table that needs to have a FK to a lead has
, lead_id int NOT NULL REFERENCES _lead.lead_fk
I also have the following in a pgTap test function to verify that the FK
exists on all children of the lead.lead table.
FOR r IN
SELECT * FROM cat_tools.pg_class_v WHERE reloid = 'lead.lead'::regclass
UNION ALL
SELECT c.*
FROM pg_inherits i
JOIN cat_tools.pg_class_v c ON reloid = inhrelid
WHERE inhparent = 'lead.lead'::regclass
LOOP
RETURN NEXT col_is_pk(
r.relschema
, r.relname
, array[ 'lead_id' ]
, 'lead_id is PK'
);
RETURN NEXT fk_ok(
r.relschema
, r.relname
, 'lead_id'
, '_lead'
, 'lead_fk'
, 'lead_id'
);
END LOOP;
At some point I'll turn this into metacode so that setting all of this
up is just a function call. I just haven't gotten to it yet. (Though, if
someone wanted to pay me to do that... ;P )
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
