> 1) What side are you talking about, the parent of the record or the
> children?
the children. only one level of depth is fine.
> 2) What procedural language are you using?
PL/pgSQL
As I understand, I need to loop over all columns in the "NEW" record, find out if the column is a foreign key using
information_schemaor pg_catalog, find the foreign key details like to which column on which table, then perform a
dynamicSQL SELECT (because I presume table and column names would be strings, not SQL identifiers) over the target
tablefor the target record, convert the record to JSON and finally assign it to the appropriate key of the JSON object
oftop-level row. I'm yet trying to write the actual working code for this, for which I welcome any help or directions.
Andthere might be simpler solutions to this problem, which I would like to know about.
Also see my question on SO:
https://stackoverflow.com/questions/57830543/convert-a-row-to-a-nested-json-document-containing-all-relations-in-postgresql