触发器函数中存在postgresql语法错误

6ie5vjzr 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(357)

我想在我的postgresql数据库中创建以下触发器函数：

CREATE FUNCTION attribute_edit_history() 
    RETURNS TRIGGER AS 
$BODY$   
  BEGIN
    Select 
            CASE 
            WHEN NOT EXISTS
            (SELECT * FROM public."TB02_MDD_KEY" where "ENCODED_ID" =ENCODE(CONVERT_TO(NEW."ATTRIBUTE_NAME", 'UTF-8'), 'base64'))
            THEN 
             CASE
             WHEN OLD."ATTRIBUTE_NAME" is distinct from NEW."ATTRIBUTE_NAME"
                THEN
                     INSERT INTO public."TB08_ATTRIBUTE_EDIT_HISTORY"(
                     "ENCODED_ID_OLD","ENCODED_ID_NEW" , "VERSION", "ATTRIBUTE_OLD", "ATTRIBUTE_NEW", "ATTRIBUTE_NEW_ID")
                     VALUES ( OLD."ENCODED_ID", NEW."ENCODED_ID", NEW."VERSION", OLD."ATTRIBUTE_NAME", NEW."ATTRIBUTE_NAME", ENCODE(CONVERT_TO(NEW."ATTRIBUTE_NAME", 'UTF-8'), 'base64')); 
                END;
             END;

    RETURN NEW;

  END;

$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER "attribute_edit_history" BEFORE UPDATE ON "TB02_MDD_KEY"
  FOR EACH ROW EXECUTE PROCEDURE attribute_edit_history();

我得到以下语法错误：

ERROR:  syntax error at or near "INTO"
LINE 13:       INSERT INTO public."TB08_ATTRIBUTE_EDIT_HISTORY"(
                      ^
SQL state: 42601
Character: 352

我不知道我哪里出错了。
提前谢谢！

sql postgresql database-trigger sql-update sql-insert

来源：https://stackoverflow.com/questions/61994379/postgresql-syntax-error-in-trigger-function

1条答案

按热度按时间

k10s72fa1#

很可能，你想要一个简单的 IF 语句而不是嵌套 CASE 表达：

CREATE FUNCTION attribute_edit_history() 
RETURNS TRIGGER AS 
$BODY$   
BEGIN
    IF 
        NOT EXISTS(
            SELECT 1 
             FROM public.TB02_MDD_KEY 
             WHERE ENCODED_ID = ENCODE(CONVERT_TO(NEW.ATTRIBUTE_NAME, 'UTF-8'), 'base64')
        ) 
        AND OLD.ATTRIBUTE_NAME IS DISTINCT FROM NEW.ATTRIBUTE_NAME
    THEN
        INSERT INTO public.TB08_ATTRIBUTE_EDIT_HISTORY(
            ENCODED_ID_OLD,
            ENCODED_ID_NEW , 
            VERSION, 
            ATTRIBUTE_OLD, 
            ATTRIBUTE_NEW, 
            ATTRIBUTE_NEW_ID
        ) VALUES ( 
            OLD.ENCODED_ID, 
            NEW.ENCODED_ID, 
            NEW.VERSION, 
            OLD.ATTRIBUTE_NAME, 
            NEW.ATTRIBUTE_NAME, 
            ENCODE(CONVERT_TO(NEW.ATTRIBUTE_NAME, 'UTF-8'), 'base64')
        ); 
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

注意，我删除了表和列标识符周围的双引号；您不需要这些（除非您有区分大小写的标识符，这里似乎不是这样）。

赞(0）回复(0）举报 2021-07-26

我来回答

触发器函数中存在postgresql语法错误

1条答案

相关问题

热门标签

最新问答