Postgres function(via npgsql) call to ExecuteNonQuery returns -1 as result for rows affected

Question

I have a simple function that just inserts the parameter values provided to it into columns in a table.

When I run the function via the ExecuteNonQuery() method on the command object I always get -1, even if the insert took place.

If i run the same query as a Text command it gives be the correct result of 1.

I'm new to postgresql/npgsql. Is there trick to making the function feed back the number of rows affected? Something like "set nocount off" in SQL Server?

Answer 1

I haven't used Npgsql, but the documentation has an example with the following code:

NpgsqlCommand command = new NpgsqlCommand("insert into table1 values(1, 1)", conn);
Int32 rowsaffected;

try
{
    rowsaffected = command.ExecuteNonQuery();
}

If you are talking about some PostgreSQL function like this:

CREATE FUNCTION myinsert(integer) RETURNS void
LANGUAGE 'sql' AS 'INSERT INTO mytable VALUES ($1)';

and you are doing something like SELECT myinsert(1);, you can't get the number of affected rows, because you are running a SELECT and what the function does internally is opaque to you.