CREATE a stored procedure using plain query text via NpgsqlConnection::ExecuteAsync

Question

I am trying to create a stpored procedure via Npsql from C#

    private async Task TestExecute(string cmdQuery)
    {
        var cnnString = $"Server=localhost;User Id=admin;Password=somepass;Database=MyTestdatabase";
        NpgsqlConnection connection = new NpgsqlConnection(cnnString);
        await connection.OpenAsync();

        var cmdQuery2 = "CREATE OR REPLACE PROCEDURE public.select_data()" +
            " LANGUAGE sql" +
            " BEGIN ATOMIC" +
            " SELECT tbl.id," +
            "     tbl.val" +
            "    FROM tbl;" +
            " END";

        // All of these fails
        var r0 = await connection.ExecuteScalarAsync(cmdQuery2);
        var r1 = await connection.ExecuteAsync(cmdQuery2);
        var r2 = await connection.QueryAsync(cmdQuery2);
    }

This is the script:

CREATE OR REPLACE PROCEDURE public.select_data()
 LANGUAGE sql
BEGIN ATOMIC
 SELECT tbl.id,
     tbl.val
    FROM tbl;
END

It works perfectly when executing from the pgAdmin. However I have this error when trying to execute it with any of Execute/Query methods.

What is even more interesting I don't see the command from the pg activity by executing this script:

SELECT query, * FROM pg_stat_activity 
where query <> ''
order by query_start desc; 

So looks like CREATE OR REPLACE Procedure fails at some validation level even before reach Postgre server.

Btw similar script for creating a function works fine even with "ExecuteReaderAsync"

CREATE OR REPLACE FUNCTION public.fn_add_values(a integer, b integer)
 RETURNS integer
 LANGUAGE sql
RETURN (a + b)

Answer 1

Semicolon... It's all about semicolon.

Your sql looks like this:

CREATE OR REPLACE PROCEDURE public.select_data()
LANGUAGE sql
BEGIN ATOMIC
SELECT tbl.id,
    tbl.val
    FROM tbl

so it's invalid. Semicolon terminates the statement.

Tell Npgsql to do not parse and rewrite your statement, or just use Dollar-Quoted String Constants

CREATE OR REPLACE PROCEDURE ...
LANGUAGE plpgsql
AS $$
BEGIN
    ...
END;
$$

One more note:

You have been hit by this issue:
https://github.com/npgsql/npgsql/issues/4445
which was mentioned in this blog post:
https://www.roji.org/parameters-batching-and-sql-rewriting