Обсуждение: Convert a row to a nested JSON document containing all relations inPostgreSQL

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

> 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

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