perform a lastmod stamp in a parent table by supplying all args or follow the fk

tsz_touch_parent_row.sql

-- PROCEDURE tsz_touch_parent_row()
-- generic function to perform a lastmod in any parent table with a lastupdatestamp column.
-- get to it directly by supplying all args or follow fk


CREATE OR REPLACE FUNCTION tsz_touch_parent_row() 
RETURNS TRIGGER AS $func$
DECLARE
-- _tsz needs to be preconfigured, it should be your standard 
-- naming convention for the tsz mod column in every table
  _tsz text := 'lastupdatestamp';
-- need to have the fk column supplied. There might be more than one. 
-- (might be neat to have the option to loop them all...)
  _fki text := TG_ARGV[0];
-- TBD BELOW
  _scm text;
  _tbl text;
  _col text;
  _sql text;
BEGIN
-- if TG_ARGN = 1, then we do the lookups ourselves based on fk (66x slower)
-- if TG_ARGN = 3, then we populate vars and assemble sql with 'public' schema
-- if TG_ARGN = 4, then we populate vars and assemble sql with given schema
  CASE
    WHEN TG_NARGS = 4 THEN
      _tbl := TG_ARGV[1];
      _col := TG_ARGV[2];
      _scm := TG_ARGV[3];
    WHEN TG_NARGS = 3 THEN
      _tbl := TG_ARGV[1];
      _col := TG_ARGV[2];
      _scm := 'public';
    WHEN TG_NARGS = 2 THEN
      RAISE EXCEPTION 'Missing arguments.  USAGE: (fk_col [ schema, table, comp_column ])';
    WHEN TG_NARGS = 1 THEN
      SELECT
        ccu.constraint_schema,
        ccu.table_name,
        ccu.column_name
        INTO STRICT
          _scm, _tbl, _col
        FROM
          information_schema.table_constraints AS tc
          JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
          JOIN information_schema.constraint_column_usage AS ccu
            ON ccu.constraint_name = tc.constraint_name
        WHERE constraint_type = 'FOREIGN KEY'
        AND tc.table_schema   = TG_TABLE_SCHEMA
        AND tc.table_name     = TG_TABLE_NAME
        and kcu.column_name   = _fki;
       
       IF EXISTS (
         SELECT 1
          FROM information_schema.columns
          WHERE table_schema = _scm
            AND table_name   = _tbl
            AND column_name  = _tsz
            AND data_type = 'timestamp with time zone'
          ) THEN
        ELSE
          RAISE EXCEPTION 'Entity not found for FK: %', _fki;     
        END IF;
    ELSE
      RAISE EXCEPTION 'No arguments passed.  USAGE: (fk_col [ schema, table, comp_column ])';
  END CASE;

-- assemble the sql from the vars
  _sql :=       
    'UPDATE ' 
    || quote_ident(_scm) || '.' || quote_ident(_tbl) 
    || ' SET ' ||  quote_ident(_tsz) || '= CURRENT_TIMESTAMP ' 
    || ' WHERE ' 
    || quote_ident(_scm) || '.'  ||  quote_ident(_tbl) || '.'  || quote_ident(_col) 
    || ' = $1.' || quote_ident(_fki);
  
-- update the parent using the fk available.
-- easier to just grab NEW or OLD than to join or subselect the TG_TABLE_NAME, etc.

  IF (TG_OP = 'DELETE') THEN
      EXECUTE _sql USING OLD;
  ELSIF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      EXECUTE _sql USING NEW;
  END IF;
  
  RETURN NULL;
END;
$func$ LANGUAGE plpgsql;