如何使用Play框架中的演进功能在PostgreSQL中创建函数? [英] How do I create a function in PostgreSQL using evolutions in the Play framework?

问题描述

使用Play Framework 2.1

我在自己的演变过程中定义了以下SQL:

I have the following SQL defined in my 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;

执行演化时，出现以下错误:

When I execute the evolution, I get the following error:

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.

I'm using the PostgreSQL driver version 9.1-901.jdbc4.

我查看了postgres查询日志，发现Play尝试执行以下操作:

I looked at the postgres query logs and found out that Play is trying to do the following:

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兼容吗?

So, for some reason Play is trying to insert SQL into a text column without properly escaping. Has anyone else found a work around for this? Do you think this is a JDBC problem and not a Play problem? Also, has anyone got Liquibase working with Play 2.1?

此外，仅将$$更改为'也不起作用.在那种情况下，我们会得到一个不同的错误，但是我们仍然无法执行进化.

Also, just changing the $$ to ' does not work either. In that case we get a different error, but we still can't execute the evolution.

我已经添加了一个全新游戏项目的示例.请从以下地址下载: http://elijah.zupancic.name/files/play_evolution_problem.tar.gz

I've added added an example from a brand new play project. Please download at: http://elijah.zupancic.name/files/play_evolution_problem.tar.gz

要使示例正常工作，您将需要创建一个新数据库，如对Evolution 1.sql的第一条评论所示.然后，您需要配置conf/application.conf以在正确的端口上并以正确的用户连接到postgres.

To get the example working you will need to create a new database as shown in the first comment on the evolution 1.sql. Then you will need to configure your conf/application.conf to connect to postgres on the correct port and with the correct user.

我刚刚做了一个实验，尝试将create函数sql完全插入到play框架之外.示例在此处: http://elijah.zupancic.name/files/PgCreateFunction.tar. gz <

事实证明它是可重复的.

It turns out that it is very reproducible.

事实证明我无法用Java复制它.

It turns out that I can't reproduce it in Java.

推荐答案

这是Play如何解析进化过程的产物.由于它解析每个分号语句，因此无法处理存储过程定义.在Play 2.1中，通过允许您将嵌入的分号加倍来​​指定它们，从而解决了该问题.例如，请参见 https://github.com/playframework/Play20/pull/649

This is an artifact of how Play parses evolutions. Since it parses each statement on semicolons, it can't handle stored procedure definitions. The problem has been addressed in Play 2.1 by allowing you to specify embedded semicolons by doubling them. See https://github.com/playframework/Play20/pull/649, for instance.

使用;;使用Play 2.1为我解决了类似的问题.我建议您按照以下方式重新定义您的演变，然后重试:

Using ;; solved a similar problem for me, using Play 2.1. I'd suggest that you redefine your evolution as follows, and try again:

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;

这篇关于如何使用Play框架中的演进功能在PostgreSQL中创建函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！