將行轉換為包含所有關係的嵌套 JSON 文件

在 PostgreSQL 10.10 中，我在 PL/pgSQL 中創建了一個觸發器函式，它NEW使用to_jsonb(NEW). NEW但是現在我需要以嵌套的方式在 JSON 對像中包含記錄的外鍵另一側的記錄。

例如：

前：

employee = {
   "id": 1,
   "name": "myname",
   "department": 2,
   "phone_no": "123456789"
}

後：

employee = {
   "id": 1,
   "name": "myname",
   "department": {
       "id": 2,
       "name": "IT"
   },
   "phone_no": "123456789"
}

在沒有關於記錄模式的先驗知識的情況下，最好和最通用的方法是NEW什麼？我需要使這個觸發器函式盡可能通用，因為我計劃在所有表上使用它。目前對我來說，關注外鍵的一級深度就足夠了。另外為了簡化，我可以假設所有外鍵都應該是一列。

據我了解，我需要遍歷NEW記錄中的所有列，使用information_schemaor找出該列是否是外鍵pg_catalog，找到外鍵詳細資訊，例如它指向哪個表上的哪一列，然後執行動態 SQL SELECT（因為我假設表和列名將是目標記錄的目標表上的字元串，而不是 SQL 標識符，將記錄轉換為 JSON，最後將其分配給頂級行 JSON 對象的適當鍵。

我還在嘗試為此編寫實際的工作程式碼，我歡迎任何幫助或指導。這個問題可能有更簡單的解決方案，我想知道。

您的猜測非常接近，您將需要動態 SQL。

但這應該比遍歷NEW記錄中的所有列等更快、更優雅：

CREATE OR REPLACE FUNCTION trg_jsonb_row_with_fk()
 RETURNS trigger AS
$func$
DECLARE
  _sql text;
  _jsonb_row jsonb;
BEGIN
  SELECT 'SELECT to_jsonb($1) || '
      || string_agg(
           format('(SELECT jsonb_build_object(%1$L, t.*)
                    FROM %2$s t WHERE %3$I = $1.%1$I)'
                , a.attname                 -- %1$L, %1$I
                , c.confrelid::regclass     -- %2$s
                , f.attname)                -- %3$I
           , ' || ')
  FROM   pg_constraint c
  JOIN   pg_attribute  a ON a.attrelid = c.conrelid
  JOIN   pg_attribute  f ON f.attrelid = c.confrelid
  WHERE  c.conrelid = TG_RELID     
  AND    c.contype  = 'f'               -- to select only FK constraints
  AND    a.attnum   = c.conkey[1]       -- assuming only single-col FKs!
  AND    f.attnum   = c.confkey[1]
  INTO   _sql;

  IF FOUND THEN                         -- FKs found
     EXECUTE _sql USING NEW INTO _jsonb_row;
  ELSE                                  -- no FKs found, plain conversion
     _jsonb_row := to_jsonb(NEW);
  END IF;

  RAISE NOTICE '%', _jsonb_row;         -- do something with it ...
  RETURN NEW;                           -- proceed with org. row
END
$func$  LANGUAGE plpgsql;

使用上述函式的範例觸發器：

CREATE TRIGGER upd_bef_jsonb_row_with_fk
 BEFORE UPDATE ON tbl
 FOR EACH ROW EXECUTE PROCEDURE trg_jsonb_row_with_fk();

這將為Postgres 目錄表中的所有 FK 建構子查詢並動態執行 SQL 命令。為簡單起見，我將查找表 ( t.*) 中相應行的所有使用者列包括在內。

挑剔： jsonb_build_object(%1$L, t.*)，不是jsonb_build_object(%1$L, t)。

似乎增加了噪音，但它避免了一個極端情況問題：這應該適用於任何輸入表，並且可能包含一個名為t. 然後t上面表達式中的名稱將解析為列而不是表別名（整行）。使用t.*消除了這種歧義，因為它只能解決整行。（需要括號來引用複合類型列，例如(t).*）。在此處和此處閱讀手冊。

由於它使用原始 FK 列的列名作為擴展對象的鍵名，因此純連接||可以滿足您的需要：它將現有的簡單值替換為 jsonb 對象。

進一步閱讀：

• 檢索所有 PK 和 FK
• 僅給定欄位名稱，如何訪問 NEW 或 OLD 欄位？

引用自：https://dba.stackexchange.com/questions/247241