psycopg2 throws unterminated dollar-quoted string error at or near "$body$"

Question

I'm trying to build an alembic migration revision to create a User Defined Function in PostgreSQL server version 10.2, however the script keeps throwing an error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) unterminated dollar-quoted string at or near "$body$
                select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'), '"
    LINE 6:         as $body$
                       ^
     [SQL: "\n        create or replace function naturalsort(text)\n          returns bytea\n          language sql\n          immutable strict\n        as $body$ \n            select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'), '\x00') from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r; \n        $body$;\n    "] (Background on this error at: http://sqlalche.me/e/f405)

The script I'm running:

def upgrade():
    conn = op.get_bind()

    conn.execute('DROP FUNCTION IF EXISTS "naturalsort"')

    conn.execute("""
        create or replace function naturalsort(text)
          returns bytea
          language sql
          immutable strict
        as $body$ 
            select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'), '\x00') from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r; 
        $body$;
    """)

The code should allow for natural sorting columns and is a copy/paste from rhodium toad.

As dollar-quoted strings are a way to re-write string constants, the alternative would be (note the double single-quotes as per the postgresql documentation: "To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a double-quote character (")."):

conn.execute("""
    create or replace function naturalsort(text)
      returns bytea
      language sql
      immutable strict
    as 'select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), ''SQL_ASCII''), ''\x00'') from regexp_matches($1, ''0*([0-9]+)|([^0-9]+)'', ''g'') r;';
""")

however this throws a similar error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) unterminated quoted string at or near "'select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), ''SQL_ASCII''), ''"
LINE 6:         as 'select string_agg(convert_to(coalesce(r[2], leng...
                   ^
 [SQL: "\n        create or replace function naturalsort(text)\n          returns bytea\n          language sql\n          immutable strict\n        as 'select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), ''SQL_ASCII''), ''\x00'') from regexp_matches($1, ''0*([0-9]+)|([^0-9]+)'', ''g'') r;';\n    "] (Background on this error at: http://sqlalche.me/e/f405)

Strange thing is that these queries execute fine in pgAdmin and alembic seems to generate valid SQL (alembic upgrade : --sql):

DROP FUNCTION IF EXISTS "naturalsort";

create or replace function naturalsort(text)
          returns bytea
          language sql
          immutable strict
        as $body$
            select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'), '') from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r;
        $body$;;

UPDATE alembic_version SET version_num='ad99fdcb28bc' WHERE alembic_version.version_num = 'ff00ac684617';

COMMIT;

Any clues to why psycopg2 keeps throwing the unterminated dollar-quoted string at or near "$body$ error?

you might want to try passing `$body$` as a string argument to the `execute` method instead of having in the query itself. — moshevi, Nov 12 '18 at 08:19
@moshevi, thanks for responding. You mean like this?: `conn.execute(""" create or replace function naturalsort(text) returns bytea language sql immutable strict as %s; """ % "$body$ select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'), '\x00') from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r; $body$")` Running this still gives me the same error (`unterminated dollar-quoted string at or near "$body$`) unfortunately. — Chris Calculus, Nov 16 '18 at 16:09

score 3 · Accepted Answer · answered Jun 05 '19 at 09:34

def upgrade():
    op.execute('DROP FUNCTION IF EXISTS "naturalsort"')

    op.execute("""
      create or replace function naturalsort(text)
      returns bytea
      language sql
      immutable strict
    as $my_body$
        select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'), '') from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r;
    $my_body$;;
    """)


def downgrade():
    op.execute('DROP FUNCTION IF EXISTS "naturalsort"')

When executing the upgrade it succeeds without an error about syntax. Actually executing the function in postgres also gives back the desired naturalsort result using this query:

SELECT * FROM public.requirement ORDER BY naturalsort(eid) asc

Using PvdLs comment about my_body worked partially, it will throw a syntax error if I add '\x00' where the empty string is now (after SQL_ASCII)

score 1 · Answer 2 · answered Nov 17 '18 at 13:16

1

$body is an alias which can not contain $, replace $body$ with a name like my_body and it should work

answered Nov 17 '18 at 13:16

PvdL

1,578
1
20
33

Unfortunately this doesn't work: `sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "some_tag" LINE 6: as some_tag select string_agg(convert_to(coalesce(r...` We're making use of dollar quoted string constants to circumvent the use of quotes in the inner SQL query. It is described [here](https://www.postgresql.org/docs/9/sql-syntax-lexical.html) in §4.1.2.4. – Chris Calculus Nov 19 '18 at 08:57

psycopg2 throws unterminated dollar-quoted string error at or near "$body$"

2 Answers2