使用JDBC创建PostgreSQL触发器 [英] Create PostgreSQL trigger using JDBC

问题描述

我正在尝试在 Play2.0 数据库演化脚本中创建PostgreSQL触发器. sql代码相对简单，并且可以在pgAdminIII中正常运行:

I am trying to create a PostgreSQL trigger in a Play2.0 database evolution script. The sql code is relatively easy and runs fine in pgAdminIII:

CREATE OR REPLACE FUNCTION update_modified() RETURNS TRIGGER AS $$
  BEGIN
    NEW.modified = now();
    RETURN NEW;
  END;
$$ LANGUAGE 'plpgsql';

但是，运行演进时出现错误:ERROR: unterminated dollar-quoted string at or near "$$ BEGIN NEW.modified = now()". SQL代码似乎在被截断 函数中遇到的第一个分号.我使用的是PostgreSQL的"9.1-901.jdbc4" JDBC驱动程序.

However, I get an error when running the evolution: ERROR: unterminated dollar-quoted string at or near "$$ BEGIN NEW.modified = now()". The SQL code seems to get truncated at the first semicolon encountered in the function. I am using the "9.1-901.jdbc4" JDBC driver for PostgreSQL.

更新:

The code in Evolutions.scala (line 219+) performs a simple split on the ;. Seems to be faulty in the framework itself:

// Execute script
s.sql.split(";").map(_.trim).foreach {
  case "" =>
  case statement => execute(statement)
}

有解决方案吗?

推荐答案

您可以尝试以下操作.

    String sql = "CREATE OR REPLACE FUNCTION update_modified() RETURNS TRIGGER AS $$ " +
            "BEGIN " +
                "NEW.modified = now(); " +
                "RETURN NEW; " +
                "END; " +
            "$$ LANGUAGE 'plpgsql';";
    DataSource ds = getDataSource();
    try {
        Connection conn = ds.getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st.executeUpdate(sql);
        st.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

希望这对您有用.

这篇关于使用JDBC创建PostgreSQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！