1

I'm using DbUp to deploy to a PostgreSQL-14 database. When I try to create a function or procedure using SQL-language syntax, DbUp throws an Npgsql.PostgresException 42601, claiming there's a syntax error. I've run the below code successfully using pgAdmin, so I'm not sure why DbUp is having a hard time with it (unless it doesn't support postgres 14?)

Here is my script:

CREATE OR REPLACE FUNCTION test_function() RETURNS VARCHAR(11)
LANGUAGE SQL
BEGIN ATOMIC
    SELECT 'Hello World';
END;

and here is the error:

ERROR:  syntax error at end of input at character 114
STATEMENT:  CREATE OR REPLACE FUNCTION test_function() RETURNS VARCHAR(11)
 LANGUAGE SQL
 BEGIN ATOMIC
         SELECT 'Hello World'

I'm aware I could rewrite the function in plpgsql language, but I want the dependency tracking that SQL language offers.

Chris Kelly
  • 13
  • 3

1 Answers1

0

@ChrisKelly thanks, I wasn't aware of this new syntax! Unfortunately this breaks Npgsql's internal SQL parser, since the semicolon causes the statement to be split... I've opened this issue to track this as an Npgsql bug; some workarounds are suggested there.

If you can't use the workarounds in that issue, then as I posted previously you can use this alternative syntax, even if it's inferior (dependency tracking and so on):

CREATE OR REPLACE FUNCTION test_function() RETURNS VARCHAR(11)
LANGUAGE SQL
RETURN 'Hello World';
Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
  • 1
    Thanks for the response Shay! That page mentions that for `sql_body` a block statement using `BEGIN ATOMIC` `END` should still be valid. Trying to execute the code example [at the bottom of this answer](https://dba.stackexchange.com/a/300512) also works in pgAdmin but not in DbUp, giving me the same error as before (syntax error just before the `END`). I have the same issue when trying to create a procedure, too – Chris Kelly May 09 '22 at 02:32
  • @ChrisKelly thanks for raising this, I've learned something... Edited my answer above. – Shay Rojansky May 09 '22 at 11:17
  • Unfortunately [DbUp uses named parameters for calling the inserts into its Journal tables](https://github.com/DbUp/DbUp/blob/d2455e93af4e2769154005b74b18993e211484b0/src/dbup-core/Support/TableJournal.cs#L107-L116), [even for Postgres](https://github.com/DbUp/DbUp/blob/d2455e93af4e2769154005b74b18993e211484b0/src/dbup-postgresql/PostgresqlTableJournal.cs#L29), so disabling SqlRewriting is off the table for us. I might open a PR to make those parameters positional for Postgres – Chris Kelly May 11 '22 at 05:16
  • Yeah, that's the problem with the new raw SQL mode: there's lots of components out there that already use named parameters on PG. – Shay Rojansky May 11 '22 at 19:00