我希望能够通过play框架的演化脚本为Postgres数据库创建以下触发器。如果我在SQL ID-e工具中运行它,它可以正常工作,但是当我尝试通过play应用程序中的演化/迁移脚本运行它时,会返回错误:
错误信息:
我们遇到了以下错误:ERROR: 在运行此SQL脚本时,出现未终止的美元引号字符串错误,位置在 "$BODY$ BEGIN IF NEW.status <> OLD.status and NEW.status = 'FT' THEN update "match" set processed = true" 的附近。[ERROR:0, SQLSTATE:42601]。
堆栈跟踪:
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