Convert a row to a nested JSON document containing all relations in PostgreSQL

[Ali Alizadeh]

Hello.


In PostgreSQL 10.10, I have created a trigger function that converts the `NEW` 
row to a JSON object using `to_jsonb(NEW)`. But now I need to include the 
records on the other side of the foreign keys in `NEW` record in the JSON 
object in a nested fashion.


What is the best and most generic way to accomplish this without prior 
knowledge about the schema of the `NEW` record? I need to keep this trigger 
function as generic as possible, because I plan to use it on all tables. One 
level of depth in following foreign keys is currently enough for me.


Thank you.

Re: Convert a row to a nested JSON document containing all relations in PostgreSQL

[Ali Alizadeh]

> 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_schema or pg_catalog, find the 
foreign key details like to which column on which table, then perform a dynamic 
SQL SELECT (because I presume table and column names would be strings, not SQL 
identifiers) over the target table for the target record, convert the record to 
JSON and finally assign it to the appropriate key of the JSON object of 
top-level row. I'm yet trying to write the actual working code for this, for 
which I welcome any help or directions. And there 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