postgresqlnpgsqldbup

DbUp throwing Npgsql exception when using SQL-language syntax for PostgreSQL functions

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.

Solution

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