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)

Could you please add a minimal executable example (text, not just a screen shot), with instructions for how to execute it? From the error message, it seems possible the SQL statement was garbled, so you might want to consult the postgres statement log. — hobgoblin
– hobgoblin, Commented Jan 8 at 13:41

Sinus32 · Accepted Answer · 2025-01-10 14:48:16Z

2

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

edited Jan 10 at 14:48

answered Jan 10 at 14:29

Sinus32

1,1037 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related