postgres create trigger function error: Unterminated dollar quote Expected terminating $$ in db sche-CodePudding

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?

CodePudding user response：

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;