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

From: Ali Alizadeh <a(dot)alizadeh(at)rayanbourse(dot)ir>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Convert a row to a nested JSON document containing all relations in PostgreSQL
Date: 2019-09-07 04:35:52
Message-ID: 7f6e7d7739c04c2cb33681ec8f705541@rayanbourse.ir
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ray Cote 2019-09-07 12:32:39 Re: Environment Variable for --data-checksum during initdb
Previous Message Jason Ralph 2019-09-07 00:15:54 Re: PG11.2 - wal_level =minimal max_wal_senders = 0