C#, PostgreSQL syntax error at end of input-CodePudding

I can't seem to be able to create migration with simplest sql to create PostgreSQL procedure.

Error is:

Exception data: Severity: ERROR SqlState: 42601 MessageText: syntax error at end of input Position: 186 File: scan.l Line: 1184 Routine: scanner_yyerror

Sql code works fine when executed in pgAdmin.

protected override void Up(MigrationBuilder migrationBuilder)
{
    var sp = @"CREATE PROCEDURE reset_primary_holder()
                LANGUAGE SQL
                BEGIN ATOMIC

                SELECT * FROM id.users;

                END;";
    migrationBuilder.Sql(sp);
}

What am I doing wrong?

CodePudding user response：

Npgsql (the ADO.NET driver, not the EF provider) by default parses SQL to find semicolons and rewrite statements, and the new PostgreSQL syntax breaks that parser; see https://github.com/npgsql/npgsql/issues/4445.

The general recommended fix is to add the following to the start of your program:

AppContext.SetSwitch("Npgsql.EnableSqlRewriting", false);

This disables statement parsing/rewriting altogether; it means you need to use positional parameter placeholders ($1, $2 instead of @p1, @p2) - see the docs.

However, EF is unfortunately not yet compatible with this mode, so you'll have to make do without this syntax...

CodePudding user response：

In the end only viable solution was to to use different syntax:

protected override void Up(MigrationBuilder migrationBuilder)
{
    var sp = @"CREATE PROCEDURE my_procedure()
            AS $$
            UPDATE id.users
            SET ...;
            Another statement;
            $$ LANGUAGE sql;";            
    migrationBuilder.Sql(sp);
}

This syntax is not mentioned in documentation on stored procedures and since this is my first PG procedure / function it was not immediately obvious to me that there is alternative.

Thanks @Shay for pointing me in the right direction.