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

On 9/6/19 9:35 PM, Ali Alizadeh wrote:
> 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.

Questions:

1) What side are you talking about, the parent of the record or the 
children?

2) What procedural language are you using?

> 
> 
> 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.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com