How do I create a function in PostgreSQL using evolutions in the Play framework?

Question

With the Play Framework 2.1

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:

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

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 = ''

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.

Edit: 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

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.

I've just did an experiment where I try to insert the create function sql totally outside of the play framework. The example is here: http://elijah.zupancic.name/files/PgCreateFunction.tar.gz<

It turns out that it is very reproducible.

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

Ureter

What you're seeing is debug output from Pg showing the parameterised query and its arguments. Each param is sent separately over the fe/be protocol and does not need to be escaped by the client. The issue you see here is almost certainly only with Pg's debug representation of the parameters unconditionally using ''; the actual param will be fine. If it were failing to escape, you'd see one big SQL query with all the parameters substituted into it instead. – Baxie

It seems more likely that you're encountering a PgJDBC issue with parsing of dollar quotes during multistatement separation. A self-contained compileable example would be helpful. sscce.org . If you add details in an edit, please comment here so I get notified. – Baxie

Hi Craig, I've added the SSCCE as you requested. Play have a look. It does reproduce in the most simple play setup that I could create. – Ureter

I don't speak Play - I know Maven and Ant project structure, but Play uses its own. How do you run it? The readme is just the default generated one. – Baxie

Install the play framework. Then type: play run – Ureter

Then just navigate to the address displayed. It will prompt you to run the evolution. – Ureter

I just added a Java only example. You will need to edit the only class by changing the database credentials and compile in order to get it to work. Then you will need to do a create table as detailed in the class. – Ureter

Thanks. Didn't get a chance to download Play and fiddle with that, so thanks for the boiled-down test case. I'll check it out shortly. – Baxie

Looking at #3499983 you need to replace $$ with $BODY$. – Flurry

@Ureter Your JDBC-only test is flawed. See the JavaDoc for PreparedStatement.execute() at docs.oracle.com/javase/6/docs/api/java/sql/… . "Returns: true if the first result is a ResultSet object; false if the first result is an update count or there is no result." . You're treating it as a success/failure return, but it isn't; so long as it doesn't throw an exception it succeeded. – Baxie

@Ureter I ran it as java -cp target/PgCreateFunction-1.0-SNAPSHOT.jar:$HOME/.m2/repository/postgresql/postgresql/9.1-901-1.jdbc4/postgresql-9.1-901-1.jdbc4.jar PgCreateFunction for this test, since the pom.xml doesn't create a rollup archive with MainClass in the manifest and dependencies embedded. – Baxie

@Ureter It executes fine if you remove the incorrect pre.execute test, and creates the row as expected. – Baxie

Unfortunately, I can't reproduce it in a pure Java/Maven implementation. I made a mistake thinking that I had. This is still a problem. I will try to file a bug with Play!. – Ureter

Answer 1

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.

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;

Aforesaid

Answer 2

You can use one sign of dollar in return statement. Simple example:

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';

Link to postgres docs: https://www.postgresql.org/docs/current/sql-createfunction.html

Pampas