Convert a row to a nested JSON document containing all relations
Your guess is pretty close, you'll need dynamic SQL.

But this should be considerably faster and more elegant than looping through all columns in the NEW record etc.:

CREATE OR REPLACE FUNCTION trg_jsonb_row_with_fk()
  RETURNS trigger AS
$func$
DECLARE
   _sql text;
   _jsonb_row jsonb;
BEGIN
   SELECT 'SELECT to_jsonb($1) || '
       || string_agg(
            format('(SELECT jsonb_build_object(%1$L, t.*)
                     FROM %2$s t WHERE %3$I = $1.%1$I)'
                 , a.attname                 -- %1$L, %1$I
                 , c.confrelid::regclass     -- %2$s
                 , f.attname)                -- %3$I
            , ' || ')
   FROM   pg_constraint c
   JOIN   pg_attribute  a ON a.attrelid = c.conrelid
   JOIN   pg_attribute  f ON f.attrelid = c.confrelid
   WHERE  c.conrelid = TG_RELID     
   AND    c.contype  = 'f'               -- to select only FK constraints
   AND    a.attnum   = c.conkey[1]       -- assuming only single-col FKs!
   AND    f.attnum   = c.confkey[1]
   INTO   _sql;

   IF FOUND THEN                         -- FKs found
      EXECUTE _sql USING NEW INTO _jsonb_row;
   ELSE                                  -- no FKs found, plain conversion
      _jsonb_row := to_jsonb(NEW);
   END IF;

   RAISE NOTICE '%', _jsonb_row;         -- do something with it ...
   RETURN NEW;                           -- proceed with org. row
END
$func$  LANGUAGE plpgsql;


Example trigger using above function:

CREATE TRIGGER upd_bef_jsonb_row_with_fk
  BEFORE UPDATE ON tbl
  FOR EACH ROW EXECUTE PROCEDURE trg_jsonb_row_with_fk();


This builds subqueries for all FKs from the Postgres catalog tables and executes the SQL command dynamically. For simplicity, I include all user columns from corresponding rows in lookup tables (t.*).

Nitpick: jsonb_build_object(%1$L, t.*), not jsonb_build_object(%1$L, t).
Seems like adding noise, but it avoids a corner case problem: This is supposed to work for any input table, and one might contain a column named t. Then the name t in above expression would resolve to the column instead of the table alias (the whole row). Using t.* removes this ambiguity as it can only resolve to the whole row. (Parentheses would be required to refer to a composite type column, like (t).*). Read the manual here and here.

Since it uses the column name of original FK columns as key name for extended objects, plain concatenation with || does what you need: it replaces the existing simple value with the jsonb object.

Further reading:


Retrieving all PK and FK
How to access NEW or OLD field given only the field&#39;s name?