sql - Postgres 将超过 100 个参数传递给 CONCAT 函数

Question

我有以下查询:

SELECT
"external_id" AS EVENT_ID,
"uuid"        AS EVENT_VERSION_ID,
"timestamp"   AS EVENT_VERSION_TIMESTAMP,
CONCAT('{',
'"timestamp": ', COALESCE(timestamp, NULL), ',',
'"uuid": ',   COALESCE("uuid", '"' || "uuid" || '"', 'null'), ',',
'"fraud": ',   COALESCE("fraud",'true','false', NULL), ',',
'"score": ', COALESCE("score", NULL), ',',
'"scoreTracking": ', COALESCE("scoreTracking", NULL), ',',
'"domain": ',   COALESCE("domain", '"' || "domain" || '"', NULL), ',',
'"event_occurred_at": ', COALESCE("event_occurred_at", NULL), ',',
'"event_received_at": ', COALESCE("event_received_at", NULL), ',',
'"event_type": ',   COALESCE("event_type", '"' || "event_type" || '"',     NULL), ',',
'"event_is_update": ',   COALESCE("event_is_update",'true', 'false', NULL), ',',
'"transaction_id": ',   COALESCE("transaction_id", '"' || "transaction_id" || '"', NULL), ',',
'"transaction_created_at": ', COALESCE("transaction_created_at", NULL), ',',
'"transaction_updated_at": ', COALESCE("transaction_updated_at", NULL), ',',
'"transaction_type": ',   COALESCE("transaction_type", '"' || "transaction_type" || '"', NULL), ',',
.
.
.
.
.
186 fields to concat......) AS EVENT_FIELDS;

但是，postgres 不允许我传递超过 100 个参数。我在网上查了一下，发现阅读了关于“variadic” 参数的内容。但是，我不确定如何在这种情况下使用它。

非常感谢。

Answer 1

row_to_json()是获得像 {"timestamp:2017-01-01","uuid: 1255784",...}

这样的输出的最佳方式

SELECT "external_id" AS EVENT_ID
      ,"uuid" AS EVENT_VERSION_ID
      ,"timestamp" AS EVENT_VERSION_TIMESTAMP
      ,row_to_json(( --{"timestamp:2017-01-01","uuid: 1255784",...}
            SELECT t
            FROM (
                SELECT timestamp
                      ,uuid
                      ,fraud
                      ,score
                      ,event_occurred_at
                ) t
            )) AS EVENT_DATA
FROM your_table