postgresql postgres create trigger function error：Unterminated dollar quote Expected terminating $$ in db schema evolution

y1aodyip 于 12个月前发布在 PostgreSQL

关注(0)|答案(2)|浏览(236)

尝试在postgres 11中创建触发器函数。

CREATE FUNCTION task_state_change_stamp() RETURNS TRIGGER AS $task_state_change_stamp$
    BEGIN
        IF NEW.task_id = OLD.task_id AND NEW.state_id != OLD.state_id THEN
        INSERT INTO workflow.review_task_audit (task_id, old_state_id, new_state_id, last_updated_ts) VALUES (NEW.task_id, OLD.state_id, NEW.state_id, now());
        END IF;
        RETURN NULL;
    END;
$task_state_change_stamp$ LANGUAGE plpgsql;

字符串
我把代码片段粘贴到连接到postgres的psql解释器中，可以成功地创建函数。
然而，当我将代码片段作为db schema evolution的一部分嵌入时（我使用的是java Play框架），它抛出了一个错误：

23:38:18 {"@timestamp":"2022-08-08T06:38:18.959Z","@version":"1","message":"Unterminated dollar quote started at position 61 in SQL CREATE FUNCTION task_state_change_stamp() RETURNS TRIGGER AS $task_state_change_stamp$\nBEGIN\nIF NEW.task_id = OLD.task_id AND NEW.state_id != OLD.state_id THEN\nINSERT INTO workflow.review_task_audit (task_id, old_state_id, new_state_id, last_updated_ts) VALUES (NEW.task_id, OLD.state_id, NEW.state_id, now()). Expected terminating $$ [ERROR:0, SQLSTATE:42601]","logger_name":"play.api.db.evolutions.DefaultEvolutionsApi","thread_name":"pool-1-thread-1","level":"ERROR","level_value":40000}

型
我如何解决这个问题来发展DB模式？

postgresql

来源：https://stackoverflow.com/questions/73282072/postgres-create-trigger-function-error-unterminated-dollar-quote-expected-termi

2条答案

按热度按时间

ccrfmcuu1#

经过一番谷歌搜索，我发现一个类似的问题How do I create a function in PostgreSQL using evolutions in the Play framework?
基于这个答案，下面的代码片段起作用了。

CREATE FUNCTION task_state_change_stamp() RETURNS TRIGGER AS $$
    BEGIN
        IF NEW.task_id = OLD.task_id AND NEW.state_id != OLD.state_id THEN
        INSERT INTO workflow.review_task_audit (task_id, old_state_id, new_state_id, last_updated_ts) VALUES (NEW.task_id, OLD.state_id, NEW.state_id, now());;
        END IF;;
        RETURN NULL;;
    END;;
$$ LANGUAGE plpgsql;

字符串

赞(0）回复(0）举报 12个月前

kiz8lqtg2#

我在创建下一个过程时遇到了同样的问题

CREATE OR REPLACE PROCEDURE my_cool_procedure(
    OUT data refcursor)
LANGUAGE 'plpgsql'
AS $BODY$

BEGIN
    POSTABLE_VISITS := 'cssuser_p_get_postable_reviews';
    OPEN POSTABLE_VISITS FOR
        SELECT 8;
END;
$BODY$;

字符串
我通过替换END和$BODY$修复了它

CREATE OR REPLACE PROCEDURE my_cool_procedure(
    OUT data refcursor)
LANGUAGE 'plpgsql'
AS $BODY$

BEGIN
    POSTABLE_VISITS := 'cssuser_p_get_postable_reviews';
    OPEN POSTABLE_VISITS FOR
        SELECT 8;
$BODY$;
END;

型

赞(0）回复(0）举报 12个月前

我来回答

postgresql postgres create trigger function error：Unterminated dollar quote Expected terminating $$ in db schema evolution

2条答案

相关问题

热门标签

最新问答