如何在Play框架中使用PostgreSQL的evolutions创建一个函数？

Question

使用Play Framework 2.1，我在数据库演化(evolution)中定义了以下SQL语句：

CREATE OR REPLACE FUNCTION idx(myArray anyarray, myElement anyelement) RETURNS int AS $$
 SELECT i FROM (
  SELECT generate_series(array_lower(myArray,1),array_upper(myArray,1))
 ) g(i)
 WHERE myArray[i] = anyElement
 LIMIT 1; $$ LANGUAGE sql IMMUTABLE;

当我执行进化时，出现以下错误：

We got the following error: ERROR: unterminated dollar-quoted string at or near 
"$$ SELECT i FROM ( SELECT generate_series(array_lower(myArray,1),
 array_upper(myArray,1)) ) g(i) WHERE myArray[i] = anyElement LIMIT 1" Position:
 87 [ERROR:0, SQLSTATE:42601], while trying to run this SQL script:

我正在使用PostgreSQL驱动程序版本9.1-901.jdbc4。

我查看了postgres查询日志并发现Play试图执行以下操作：

LOG:  execute <unnamed>: insert into play_evolutions values($1, $2, $3, $4, $5, $6, $7)
PST DETAIL:  parameters: $1 = '1',
                         $2 = 'c834d463ebd9916b0a3388040300a0926514faef',
                         $3 = '2013-03-05 00:00:00',
                         $4 = '-- THE EVOLUTION UP STATEMENTS GO HERE',
                         $5 = '-- THE EVOLUTION DOWN STATEMENTS GO HERE',
                         $6 = 'applying_up',
                         $7 = ''

因为某些原因，Play正在尝试在文本列中插入未经适当转义的SQL。有其他人找到解决方法吗？您认为这是JDBC问题而不是Play问题吗？此外，有人已经成功地将Liquibase与Play 2.1一起使用了吗？

另外，仅更改$$为'也不起作用。在这种情况下，我们会得到不同的错误，但我们仍然无法执行演化。

编辑：我已经添加了一个全新的play项目示例。请下载：http://elijah.zupancic.name/files/play_evolution_problem.tar.gz 要使示例工作，您需要像演化1.sql上的第一条评论中所示那样创建一个新数据库。然后，您需要配置conf/application.conf以连接到正确端口和具有正确用户的postgres。

我刚刚进行了一个实验，尝试在Play框架之外完全插入创建函数SQL。示例在此处：http://elijah.zupancic.name/files/PgCreateFunction.tar.gz<。

结果发现这是非常可复制的。

编辑：事实证明我无法在Java中复制它。

- Elijah

---

你所看到的是来自Pg的调试输出，显示参数化查询及其参数。每个参数都单独通过前端/后端协议发送，客户端无需转义。这里你看到的问题几乎肯定只是Pg调试表示参数时无条件使用''; 实际参数是没问题的。如果它未能转义，你将会看到一个大SQL查询，其中所有参数都被替换进去了。 - Craig Ringer

看起来你遇到了一个PgJDBC问题，这个问题与多语句分离期间的美元引号解析有关。提供一个自包含的可编译示例将会很有帮助。http://sscce.org/。如果你在编辑中添加了细节，请在此处发表评论以便我得到通知。 - Craig Ringer

嗨，Craig，我已经按照你的要求添加了SSCCE。请看一下。它在我能创建的最简单的Play设置中进行了复制。 - Elijah

查看 https://dev59.com/HXA75IYBdhLWcg3wDUgo，您需要将 $$ 替换为 $BODY$。 - Mark Rotteveel

@Elijah 如果你移除错误的 pre.execute 测试，它会正常执行，并按预期创建行。 - Craig Ringer

Answer 1

这是Play解析evolutions的方式导致的问题。由于它在分号上解析每个语句，因此无法处理存储过程定义。这个问题已经在Play 2.1中得到解决，通过使您能够通过重复使用嵌入式分号来指定嵌入式分号。例如，请参见https://github.com/playframework/Play20/pull/649。

对我来说，使用“;;”解决了类似的问题，使用Play 2.1。我建议您重新定义您的evolution，并再次尝试：

CREATE OR REPLACE FUNCTION idx(myArray anyarray, myElement anyelement) RETURNS int AS $$
 SELECT i FROM (
  SELECT generate_series(array_lower(myArray,1),array_upper(myArray,1))
 ) g(i)
 WHERE myArray[i] = anyElement
 LIMIT 1;; $$ LANGUAGE sql IMMUTABLE;

- Brian Clapper

Answer 2

你可以在返回语句中使用一个美元符号。简单示例：

CREATE OR REPLACE FUNCTION sys_extract_utc(
    time_in timestamp with time zone)
    RETURNS timestamp without time zone
    LANGUAGE 'sql'
    PARALLEL SAFE
    return $1 at time zone 'UTC';

链接到PostgreSQL文档：https://www.postgresql.org/docs/current/sql-createfunction.html

- Mikhail Ionkin