无法在Play框架演化中执行创建Postgres函数

Question

我希望能够通过play框架的演化脚本为Postgres数据库创建以下触发器。如果我在SQL ID-e工具中运行它，它可以正常工作，但是当我尝试通过play应用程序中的演化/迁移脚本运行它时，会返回错误：

 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();

错误信息：

我们遇到了以下错误：ERROR: 在运行此SQL脚本时，出现未终止的美元引号字符串错误，位置在 "$BODY$ BEGIN IF NEW.status <> OLD.status and NEW.status = 'FT' THEN update "match" set processed = true" 的附近。[ERROR:0, SQLSTATE:42601]。

堆栈跟踪：

[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

- simonC

Answer 1

这是Play Evolutions的一个错误。您需要将所有的“;”都加倍...
因此，您的代码应该是：

  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();;

- Simon

---

哦，好发现...谢谢信息...晚上会尝试一下，如果有效我会接受你的答案。 - simonC

它对我有用！谢谢... @simonC 你忘记接受这个答案了吗？ - Armin