Re: ID column naming convention

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
NULLOR organic_lead_id = lead_id ) 
>     , some_lead_provider_lead_id        int                    CONSTRAINT some_lead_provider_lead_id_sanity CHECK(
some_lead_provider_lead_idIS 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