PostgreSQL触发器函数查询变量报错：relation "PAYLOAD"不存在

使用Node.js创建PostgreSQL触发器时，执行以下代码出现错误：

const SQL_QUERY = '...............';  // **SOME SQL THAT RETURNS A VALID DATA !!!**

const EmployeesTrigger = `
  CREATE OR REPLACE FUNCTION UPDATE_EXMPLOYYEES() RETURNS TRIGGER AS 
  $$
      DECLARE
          PAYLOAD JSONB;
      BEGIN
          SELECT json_agg(groupperSubquery)
          INTO PAYLOAD
          FROM (
            ${SQL_QUERY}
          ) groupperSubquery;         
          IF PAYLOAD IS NOT NULL THEN
            UPDATE "employees" SET "employeeLeft" = TRUE WHERE "employeeId" IN (SELECT "employeeId" FROM "PAYLOAD");
            RETURN NULL;
          END IF;
          RETURN NULL;
      END;
  $$ 
  LANGUAGE 'plpgsql';
`;

报错信息：

relation "PAYLOAD" does not exist

核心问题 #

你错误地将PL/pgSQL变量PAYLOAD当作数据库表来查询，PostgreSQL会把带双引号的"PAYLOAD"识别为数据库中的关系（表/视图），但这个关系并不存在，因此触发报错。

PAYLOAD是你声明的JSONB变量，必须使用PostgreSQL的JSON处理函数解析其中的数据，而非直接用FROM "PAYLOAD"进行查询。

修复后的代码 #

将查询JSON变量的逻辑替换为jsonb_to_recordset函数，把JSON数组转换为可查询的记录集：

const SQL_QUERY = '...............';  // **SOME SQL THAT RETURNS A VALID DATA !!!**

const EmployeesTrigger = `
  CREATE OR REPLACE FUNCTION UPDATE_EMPLOYEES() RETURNS TRIGGER AS 
  $$
      DECLARE
          PAYLOAD JSONB;
      BEGIN
          SELECT json_agg(groupperSubquery)
          INTO PAYLOAD
          FROM (
            ${SQL_QUERY}
          ) groupperSubquery;         
          IF PAYLOAD IS NOT NULL THEN
            UPDATE "employees" SET "employeeLeft" = TRUE 
            WHERE "employeeId" IN (
              SELECT rec."employeeId" 
              FROM jsonb_to_recordset(PAYLOAD) AS rec("employeeId" INT)
            );
            RETURN NULL;
          END IF;
          RETURN NULL;
      END;
  $$ 
  LANGUAGE plpgsql;
`;

关键说明 #

1. jsonb_to_recordset用法：该函数将JSONB数组转换为关系型记录集，需显式指定字段名称和对应数据类型（示例中employeeId设为INT，请根据实际字段类型调整）。
2. 变量引用规范：变量PAYLOAD不需要加双引号，PostgreSQL中双引号用于区分大小写的表/列名，变量直接引用即可。
3. 拼写修正：原函数名UPDATE_EXMPLOYYEES存在拼写错误，已修正为UPDATE_EMPLOYEES，避免后续维护时产生混淆。

内容的提问来源于stack exchange，提问作者JAN