Querying declared variable in Postgres gives "error: relation "XXX" does not exist"

Question

I'm creating a Trigger Using NODE JS in Postgres, and when running the following query :

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';
`;

I get the error:

relation "PAYLOAD" does not exist

What's wrong with the query ?

Answer 1

Double-quoting the PAYLOAD variable confuses PostgreSQL, making it think you have some column in mind.

You can access "employeeId" key in your PAYLOAD via a [] subscript and use ? operator to see if "employeeId" of table "employeeId" is in that list: demo

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 PAYLOAD['employeeId'] ? "employeeId";
            RETURN NULL;
          END IF;
          RETURN NULL;
      END;
  $$ 
  LANGUAGE 'plpgsql';
`;

jsonpath function jsonb_path_exists() will be less convenient in this case, but it's good to promote for its flexibility:

WHERE jsonb_path_exists( PAYLOAD
                       ,'$.employeeId[*] ? (@==$var)'
                       ,'{"var":"'||"employeeId"||'"}')