python - Postgres 触发器找不到返回

Question

这是我的触发器:

CREATE OR REPLACE FUNCTION update_played ()
  RETURNS trigger
AS
$BODY$
  DECLARE
    v_count_played integer;
BEGIN

    SELECT count(*) INTO STRICT v_count_played FROM history WHERE song_id = NEW.song_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'history count for song % not found', NEW.song_id;
      WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'history count did not aggregate';

    EXECUTE 'UPDATE song SET count_played = $1 WHERE id = $2'
        USING v_count_played, NEW.song_id;

    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;


create trigger update_played_trigger
after insert on history
for each row
execute procedure update_played();

它给出了错误:

sqlalchemy.exc.InternalError: (psycopg2.InternalError) control reached end of trigger procedure without RETURN

Answer 1

您误解了 EXCEPTION 关键字。如果我改变你的缩进以匹配它的工作方式，它可能会帮助你理解。 EXCEPTION 与 BEGIN 和 END 一起作为 block 的一部分出现，就像其他语言中的 try {} catch {} .所以它是这样工作的:

BEGIN

    SELECT count(*) INTO STRICT v_count_played 
    FROM history WHERE song_id = NEW.song_id;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'history count for song % not found', NEW.song_id;
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'history count did not aggregate';

        EXECUTE 'UPDATE song SET count_played = $1 WHERE id = $2'
            USING v_count_played, NEW.song_id;

        RETURN NULL;
END;

此处发生的是您运行 SELECT。如果存在 NO_DATA_FOUND 异常，您可以RAISE。如果您RAISE 遇到TOO_MANY_ROWS 异常，那么在同一 block 中，在RAISE EXCEPTION 之后有无法访问的代码执行 EXECUTE 和 RETURN。

如果 SELECT 没有异常，则不会采取进一步的操作，该过程只是退出，不返回任何内容。

我认为您打算写的是:

BEGIN
    BEGIN
      SELECT count(*) INTO STRICT v_count_played FROM history WHERE song_id = NEW.song_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'history count for song % not found', NEW.song_id;
      WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'history count did not aggregate';
    END;

    EXECUTE 'UPDATE song SET count_played = $1 WHERE id = $2'
        USING v_count_played, NEW.song_id;

    RETURN NULL;
END;