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

Question

Tried to create a trigger function in 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;

I pasted the snippet into psql interpreter that connects to postgres, and can successfully created the function.

However, as I embed the snippet as part of db schema evolution (I am using the java Play framework), it throws an error:

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}

How can I resolve the issue to evolve the DB schema?

Apparently that framework doesn't understand dollar quoting. You will need to enclose the function body in single quotes as '...' language plpgsql; — user330315
– user330315, Commented Aug 8, 2022 at 18:11

GeauxEric · Accepted Answer · 2022-08-08 19:40:46Z

1

After some googling, I found a similar issue How do I create a function in PostgreSQL using evolutions in the Play framework?

Based on that answer, the snippet below works.

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;

answered Aug 8, 2022 at 19:40

GeauxEric

3,0907 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mishamba · Accepted Answer · 2023-12-19 07:19:01Z

0

I had pretty same problem with creating next procedure

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$;

And I fixed it with replacing END and $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;

answered Dec 19, 2023 at 7:19

Mishamba

291 silver badge8 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related