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

On 9/8/19 5:22 AM, Ali Alizadeh wrote:
> 
>> 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 
 

Probably easier to look whether there are FK's on a table:

select * from information_schema.table_constraints where table_schema = 
'some_schema' and table_name ='some_name' and constraint_type = 'FOREIGN 
KEY';

Then use that to look up the columns(to account for multi-column FK's):

select table_name, column_name from 
information_schema.constraint_column_usage where constraint_name = 
'fk_name';

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

Take a look at the format() example here:

https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

https://www.postgresql.org/docs/11/functions-string.html#FUNCTIONS-STRING-FORMAT

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
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com