From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ali Alizadeh <a(dot)alizadeh(at)rayanbourse(dot)ir>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Convert a row to a nested JSON document containing all relations in PostgreSQL |
Date: | 2019-09-08 21:11:56 |
Message-ID: | 02caac58-9db3-724a-566a-1486ded5f253@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | stan | 2019-09-08 23:11:36 | help formualting a query |
Previous Message | Peter Geoghegan | 2019-09-08 20:28:04 | Re: Whan is it safe to mark a function PARALLEL SAFE? |