Find all tables referenced by a foreign key (including schema and referencing column)

example.sql

/*
  Function to merge two people into a single person

  The primary person (referenced by primary_person_id) will be retained, the secondary person
  will have all their records re-referenced to the primary person, and then the secondary person
  will be deleted

  Note that this function may be destructive! For most tables, the records will simply be merged,
  but in cases where merging would violate a UNIQUE or EXCLUSION constraint, the secondary person's
  respective records will be dropped. For example, people cannot have overlapping pledges (on the
  pledges.pledge table). If the secondary person has a pledge that overlaps with a pledge that is
  on record for the primary person, the secondary person's pledge will just be deleted.
*/
CREATE FUNCTION utils.merge_person (primary_person_id BIGINT, secondary_person_id BIGINT)
RETURNS people.person AS $$
DECLARE
  _referenced_table utils.referenced_table_t;
  _col name;
  _exec TEXT;
  _primary_person people.person;
BEGIN
  -- defer all deferrable constraints
  SET CONSTRAINTS ALL DEFERRED;
  -- This loop updates / deletes all referenced tables, setting the person_id (or equivalent)
  -- From secondary_person_id => primary_person_id
  FOR _referenced_table IN (SELECT * FROM utils.get_referenced_tables('people', 'person', 'id')) LOOP
    -- the column_names are stored as an array, so we need to loop through these too
    FOREACH _col IN ARRAY _referenced_table.column_name LOOP
      RAISE NOTICE 'Merging %.%(%)', _referenced_table.schema_name, _referenced_table.table_name, _col;

      -- FORMAT allows us to safely build a dynamic SQL string
      _exec = FORMAT(
        $sql$ UPDATE %s.%s SET %s = $1 WHERE %s = $2 $sql$,
        _referenced_table.schema_name,
        _referenced_table.table_name,
        _col,
        _col
      );

      RAISE NOTICE 'SQL:  %', _exec;

      -- wrap the execution in a block so that we can handle uniqueness violations
      BEGIN
        EXECUTE _exec USING primary_person_id, secondary_person_id;
        RAISE NOTICE 'Merged %.%(%) OK!', _referenced_table.schema_name, _referenced_table.table_name, _col;
      EXCEPTION
        -- Error codes are Postgres built-ins, see https://www.postgresql.org/docs/9.6/errcodes-appendix.html
        WHEN unique_violation OR exclusion_violation THEN
          RAISE NOTICE 'Cannot merge record with % = % on table %.%, falling back to deletion!', _col, secondary_person_id, _referenced_table.schema_name, _referenced_table.table_name;
          _exec = FORMAT(
            $sql$ DELETE FROM %s.%s WHERE %s = $1 $sql$,
            _referenced_table.schema_name,
            _referenced_table.table_name,
            _col
          );
          RAISE NOTICE 'SQL:  %', _exec;
          EXECUTE _exec USING secondary_person_id;
          RAISE WARNING 'Deleted record with % = % on table %.%', _col, secondary_person_id, _referenced_table.schema_name, _referenced_table.table_name;
      END;

    END LOOP;
  END LOOP;

  -- Once we've updated all the tables, we can safely delete the secondary person
  RAISE WARNING 'Deleted person with id = %', secondary_person_id;

  -- Get our primary person so that we can return them
  SELECT * FROM people.person WHERE id = primary_person_id INTO _primary_person;

  RETURN _primary_person;

END
$$ LANGUAGE plpgsql VOLATILE;

find_referenced_tables.sql

CREATE SCHEMA utils;

-- Return type for the utils.get_referenced_tables function
CREATE TYPE utils.referenced_table_t AS (
  constraint_name name,
  schema_name name,
  table_name name,
  column_name name[],
  foreign_schema_name name,
  foreign_table_name name
);
/*
 A function to get all downstream tables that are referenced to a table via a foreign key relationship
 The function looks at all constraints that contain a reference to the provided schema-qualified table column
 It then generates a list of the schema/table/column tuples that are the target of these references

 Idea based on https://stackoverflow.com/a/21125640/7114675

 Postgres built-in reference:
 - pg_namespace  => schemas
 - pg_class      => tables
 - pg_attribute  => table columns
 - pg_constraint => constraints

*/
CREATE FUNCTION utils.get_referenced_tables (schema_name name, table_name name, column_name name)
RETURNS SETOF utils.referenced_table_t AS $$
  -- Wrap the internal query in a select so that we can order it more easily
  SELECT * FROM (
    -- Get human-readable names for table properties by mapping the OID's stored on the pg_constraint
    -- table to the underlying value on their relevant table.
    SELECT
      -- constraint name - we get this directly from the constraints table
      pg_constraint.conname AS constraint_name,
      -- schema_name
      (
        SELECT pg_namespace.nspname FROM pg_namespace
        WHERE pg_namespace.oid = pg_constraint.connamespace
      ) as schema_name,
      -- table_name
      (
        SELECT pg_class.relname FROM pg_class
        WHERE pg_class.oid = pg_constraint.conrelid
      ) as table_name,
      -- column_name
      (
        SELECT array_agg(attname) FROM pg_attribute
        WHERE attrelid = pg_constraint.conrelid
          AND ARRAY[attnum] <@ pg_constraint.conkey
      ) AS column_name,
      -- foreign_schema_name
      (
        SELECT pg_namespace.nspname FROM pg_namespace
        WHERE pg_namespace.oid = (
          SELECT pg_class.relnamespace FROM pg_class
          WHERE pg_class.oid = pg_constraint.confrelid
        )
      ) AS foreign_schema_name,
      -- foreign_table_name
      (
        SELECT pg_class.relname FROM pg_class
        WHERE pg_class.oid = pg_constraint.confrelid
      ) AS foreign_table_name
    FROM pg_constraint
    -- confrelid = constraint foreign relation id = target schema + table
    WHERE confrelid IN (
        SELECT oid FROM pg_class
        -- relname = target table name
        WHERE relname = get_referenced_tables.table_name
        -- relnamespace = target schema
          AND relnamespace = (
            SELECT oid FROM pg_namespace
            WHERE nspname = get_referenced_tables.schema_name
          )
    )
    -- confkey = constraint foreign key = the column on the foreign table linked to the target column
    AND confkey @> (
      SELECT array_agg(attnum) FROM pg_attribute
      WHERE attname = get_referenced_tables.column_name
      AND attrelid = pg_constraint.confrelid
    )
  ) a
  ORDER BY
    schema_name,
    table_name,
    column_name,
    foreign_table_name,
    foreign_schema_name
 ;
$$ LANGUAGE SQL STABLE;

Note the use of SET CONSTRAINTS ALL DEFERRED; in the example function, which ensures that foreign key relationships are checked at the end of the merge. You may need to update your constraints to be DEFERRABLE INITIALLY DEFERRED:

ALTER TABLE settings.contact_preference
  DROP CONSTRAINT contact_preference_person_id_fkey,
  DROP CONSTRAINT person_id_current_address_id_fkey,
  ADD CONSTRAINT contact_preference_person_id_fkey
    FOREIGN KEY (person_id)
    REFERENCES people.person(id)
    ON UPDATE CASCADE ON DELETE CASCADE
    DEFERRABLE INITIALLY IMMEDIATE,
  ADD CONSTRAINT person_id_current_address_id_fkey
    FOREIGN KEY (person_id, current_address_id)
    REFERENCES people.address(person_id, id)
    DEFERRABLE INITIALLY IMMEDIATE
;