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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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.

Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
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_schema or 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
[hidden email]