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
adrian.klaver@xxxxxxxxxxx
