Question 90017 in PostgreSQL Question posted in
The official documentation can be found here.

C#, PostgreSQL syntax error at end of input

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?
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search