postgres migration error; unterminated dollar-quoted string

Question

Well this is abit strange, could anyone help me point out where this function may be wrong. I have a function similar to

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;

$$  LANGUAGE plpgsql

When i run it directly in the pgAdmin sql console, there are no errors but running it in a migration script using db-migration-maven-plugin i get the error.

Error executing: CREATE FUNCTION check_password(uname TEXT, pass TEXT) 
                 RETURNS BOOLEAN AS $$ DECLARE passed BOOLEAN
org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted 
                        string at or near "$$ DECLARE passed BOOLEAN"
Position: 74

Sorry, facing the same problem now. Have you found the way to fix it? — Alex Kartishev, Mar 20 '14 at 06:33
I avoided writing long functions all together. It is an issue with the migration software (carbonfive). I thought of making a few changes to it since it is opensourced but haven't got time to. — ivanorone, Mar 25 '14 at 07:59
I got a similar error message using Aqua Data Studio, which uses the Redshift JDBC driver. In my case, the fix was to go to "Options" and un-tick '; Statement Separator'. It seems the parser assumed that the first semicolon was the end of the entire function definition, and it hadn't seen the closing $$ yet. Your example function was created without errors on my system (version 9.5) when that box was un-ticked. With the '; Statement Separator' ticked, I got the same error. Hope that helps. — Jason, Jan 23 '17 at 05:15

score 2 · Answer 1 · answered Nov 25 '13 at 19:51

2

The SQL generated by your migration scripts probably have some kind of $$ quotes in them that gets interpreted as a string somewhere.

A quick and dirty fix could be to change $$ to $func$ or even $check_password$ , though there might be other functions further down that suffer the same problem.

The better, more long term approach will be to locate the offending $$.

answered Nov 25 '13 at 19:51

Denis de Bernardy

75,850
13
131
154

1

i thought so at first, changed $$ to $func$ but still that never helped – ivanorone Nov 25 '13 at 19:58
@ivan_d_coder: it failed with the exact same error message, or in a different location? – Denis de Bernardy Nov 25 '13 at 20:00
Double check the SQL that's getting output. The whole migration thingy. There necessarily are unterminated strings in there that you haven't noted, or some other syntax error that make the string look unterminated. – Denis de Bernardy Nov 25 '13 at 20:14
Well, i discovered one interesting thing, the migration plugin (db-migration-maven-plugin) runs the function part by part not as a whole as specified using the $$. So what happens is that it executes the statements just after it encounters a termination instead of wrapping the entire block in the $$ $$ and executing it as one. What i would like to know now is how to make it execute the entire statements in the $$......$$ block as one. – ivanorone Dec 06 '13 at 07:32

score 2 · Answer 2 · answered Apr 03 '14 at 11:22

2

@ivanorone: There is a bug filed for db-migration-maven-plugin: https://code.google.com/p/c5-db-migration/issues/detail?id=9 There is a patch included but looking at its source, it doesn't really fix the problem properly. Besides that, the project seems to be idling (last commit 2010).

There is another plugin, that I am trying to use instead, Flyway: http://flywaydb.org/ Switching to it was pretty easy and it works fine so far.

answered Apr 03 '14 at 11:22

Zdeněk

33
6

1

I agree, flywaydb seems to be more active than carbon5 b-migration-maven-plugin. I hadn't taken it seriously but will take a look soon – ivanorone Apr 03 '14 at 11:32

score 1 · Answer 3 · answered Jan 13 '16 at 12:55

Execute your query as single batch(Hint: USE ctrl+F5).When I ran your query in Postgres SQl (Greenplum Interface) I got similiar error as u stated above. I found that db is executing query by splitting it based on termination in our query(Semicolon). As we you have terminated thrice in your query, It executes it one by one statement. So, to execute it as a whole batch run as using execute as single batch in your execution option.

I hope it helps you:):)

Awesome. Works perfectly fine. – Melaku Minas Kasaye Feb 23 '19 at 21:14 — Melaku Minas Kasaye, Feb 23 '19 at 21:14

score 0 · Answer 4 · answered Aug 13 '15 at 12:28

Solution for Grails Database Migration Plugin

changeSet(author: "...", id: "...") {
    sql(splitStatements: false, '''
        CREATE FUNCTION trigger_func() RETURNS TRIGGER AS $$
        DECLARE var text;
        BEGIN
        ...
        END $$ LANGUAGE plpgsql;
    ''')
}

score 0 · Answer 5 · answered Mar 03 '20 at 11:19

0

The best solution seems to be to tell the plugin to only accept the semicolon as a delimiter when it's on a new line. This works well where you need to define code blocks like this.

Ref: sql-maven-plugin with multiple delimiters

answered Mar 03 '20 at 11:19

Phil Horder

402
6
13

postgres migration error; unterminated dollar-quoted string

5 Answers5