Unable to execute create postgres function in Play framework evolutions

Question

I want to create the following trigger for Postgres DB via play framework evolution script, If I run this in an sql ID-e tool it works ok but when I try to run it via the evolution/migration script in the play app it returns an error:

 CREATE OR REPLACE FUNCTION mark_processed()
  RETURNS trigger AS
  $BODY$
  BEGIN
    IF NEW.status <> OLD.status and NEW.status = 'FT' THEN
      update "match" set processed = true;
    END IF;
    RETURN NEW;
  END;
  $BODY$
  LANGUAGE plpgsql;

  CREATE TRIGGER on_fultime_trigger
  AFTER UPDATE
    ON "match"
  FOR EACH ROW
  EXECUTE PROCEDURE mark_processed();

The error:

e got the following error: ERROR: unterminated dollar-quoted string at or near "$BODY$ BEGIN IF NEW.status <> OLD.status and NEW.status = 'FT' THEN update "match" set processed = true" Position: 64 [ERROR:0, SQLSTATE:42601], while trying to run this SQL script:

The stack trace:

[error] 2018-02-18 21:38:07,365 o.j.StatementLogger - java.sql.Statement.execute: CREATE OR REPLACE FUNCTION mark_processed()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.status <> OLD.status and NEW.status = 'FT' THEN
update "match" set processed = true;
throws exception: org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string at or near "$BODY$
BEGIN
IF NEW.status <> OLD.status and NEW.status = 'FT' THEN
update "match" set processed = true"
  Position: 64
org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string at or near "$BODY$
BEGIN
IF NEW.status <> OLD.status and NEW.status = 'FT' THEN
update "match" set processed = true"
  Position: 64
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:615)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:451)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:443)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
[error] 2018-02-18 21:38:07,365 o.j.StatementLogger - java.sql.Statement.execute: CREATE OR REPLA

Answer 1

This is a bug of play evolutions. You have to double all the ";"...
So your code would be:

  CREATE OR REPLACE FUNCTION mark_processed()
  RETURNS trigger AS
  $BODY$
  BEGIN
  IF NEW.status <> OLD.status and NEW.status = 'FT' THEN
  update "match" set processed = true;;
  END IF;;
  RETURN NEW;;
  END;;
  $BODY$
  LANGUAGE plpgsql;;

  CREATE TRIGGER on_fultime_trigger
  AFTER UPDATE
    ON "match"
  FOR EACH ROW
  EXECUTE PROCEDURE mark_processed();;