Postgresql:参数类型在准备计划时不匹配PostgreSQL12

Question

希望能得到一些帮助，我正在工作的服务，它与一个Postgres-12数据库互动。我已经创建了一个触发器，每当对client表进行更新/插入/删除时就会触发该触发器，从而在受影响的行之外创建一个有效负载。

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $trigger$
DECLARE
  rec RECORD;
  dat RECORD;
  payload TEXT;
BEGIN

  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'UPDATE' THEN
     rec := NEW;
     dat := OLD;
  WHEN 'INSERT' THEN
     rec := NEW;
  WHEN 'DELETE' THEN
     rec := OLD;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;

  -- Build the payload
  payload := json_build_object('timestamp',CURRENT_TIMESTAMP,'action',LOWER(TG_OP),'db_schema',TG_TABLE_SCHEMA,'table',TG_TABLE_NAME,'record',row_to_json(rec), 'old',row_to_json(dat));

  -- Notify the channel
  PERFORM pg_notify('db_event', payload);

  RETURN rec;
END;
$trigger$ LANGUAGE plpgsql;

CREATE TRIGGER clients_notify AFTER INSERT OR UPDATE OR DELETE ON clients
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

我可以通过psql验证触发器大部分时间都正常工作，但是在对client表进行某些更新时，我会遇到以下错误。

DBProgrammingError: type of parameter 15 (clients) does not match that when preparing the plan (record)
CONTEXT:  PL/pgSQL function notify_trigger() line 22 at assignment

导致错误的SQL查询：

db.executeCommands(
    [
        """
        UPDATE clients
        SET pos = %(pos)s,
            cos = %(cos)s,
            rpl = %(rpl)s,
            obv = %(obv)s,
            oav = %(oav)s,
            wth = %(wth)s
        WHERE id = %(id)s
        """
    ],
    (
        {
            "id": client["id"],
            "pos": client["pos"],
            "cos": client["cos"],
            "rpl": client["rpl"],
            "obv": client["obv"],
            "oav": client["oav"],
            "wth": client["wth"],
        },
    ),
)

以及客户端表的架构：

CREATE TABLE clients (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    datetime timestamp DEFAULT now(),
    first VARCHAR(16) NOT NULL,
    last VARCHAR(16) NOT NULL,
    alias VARCHAR(32) NOT NULL,
    pos integer DEFAULT 0,
    cos numeric(1000,4) DEFAULT 0.0,
    rpl numeric(1000,4) DEFAULT 0.0,
    obv integer DEFAULT 0,
    oav integer DEFAULT 0,
    wth numeric(1000,4) DEFAULT 0.0
);

奇怪的是，我在服务中的两个不同位置执行此更新，一个接近api，另一个位于单独的线程中；前者导致上面的错误，而后者则成功地执行。

我已经签出了刚才问的这个答案，但我不确定它如何适用于这里的情况，因为将有效负载转换为text (PERFORM pg_notify('db_event', payload::text);)没有帮助。我对PL/pgSQL的了解非常薄弱，所以如果有人对Postgres 听/通知有很深的理解，我会非常感谢您的帮助。

如何使参数(客户端)与计划(记录)相匹配？

溶液

正如Laurenz在编辑之前指出的那样，client类型与我用来表示它的record类型不匹配。虽然我无法像上面指出的那样让coalesce工作，但我决定为我需要表示的每一种数据类型创建一个单独的触发器处理程序。虽然有点冗长，但能把工作做完。更好的解决方案欢迎。

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $trigger$
DECLARE
-  rec RECORD;
-  dat RECORD;
+  rec clients;
+  dat clients;
  payload TEXT;
BEGIN

  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'UPDATE' THEN
     rec := NEW;
     dat := OLD;
  WHEN 'INSERT' THEN
     rec := NEW;
  WHEN 'DELETE' THEN
     rec := OLD;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;

  -- Build the payload
  payload := json_build_object('timestamp',CURRENT_TIMESTAMP,'action',LOWER(TG_OP),'db_schema',TG_TABLE_SCHEMA,'table',TG_TABLE_NAME,'record',row_to_json(rec), 'old',row_to_json(dat));

  -- Notify the channel
  PERFORM pg_notify('db_event', payload);

  RETURN rec;
END;
$trigger$ LANGUAGE plpgsql;

CREATE TRIGGER clients_notify AFTER INSERT OR UPDATE OR DELETE ON clients
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

Answer 1

您正在返回一个record类型的值，但它应该是client。

简单、安全和通用的RETURN语句将是

RETURN coalesce(NEW, OLD);