RETURNING clause with multiple affected rows

Question

Is there a way using Npgsql to use a prepared UPDATE statement that can return back multiple affected rows from the RETURNING clause?

I've been able to achieve this when there is one affected row returned by marking the RETURNING parameters as Out direction parameters and fetching their returned values out of the command parameters, but this only works when there is one updated row:

string sql = "UPDATE my_schema.jobs SET order_number=@order_number WHERE job_id = @job_id RETURNING job_id, order_number, created_time, state;"

var stmt = new NpgsqlCommand(sql, connection, transaction);

stmt.Parameters.Add("job_id", NpgsqlDbType.Bigint);
stmt.Parameters.Add("order_number", NpgsqlDbType.Varchar);

var outParam = new NpgsqlParameter("created_time", NpgsqlDbType.Bigint) { Direction = System.Data.ParameterDirection.Output };
stmt.Parameters.Add(outParam);

outParam = new NpgsqlParameter("state", NpgsqlDbType.Varchar) { Direction = System.Data.ParameterDirection.Output };
stmt.Parameters.Add(outParam);

await stmt.PrepareAsync(cancel);

...

stmt.Parameters["job_id"].Value = 10;
stmt.Parameters["order_number"].Value = "BT100";

int recordsAffected = await stmt.ExecuteNonQueryAsync(cancel);

if (recordsAffected != 0)
{
   long returnedJobId = stmt.Parameters["job_id"].Value as long;
   string returnedOrder = stmt.Parameters["order_number"].ToString();
   long returnedCreated = stmt.Parameters["created_time"].Value as long;
   string returnedState = stmt.Parameters["state"].ToString();
}

But PSQL statements can return multiple rows from the RETURNING clause if more than one row matches the WHERE clause. Is there any way with the NpgsqlCommand class to get access to that returned set of rows affected by the update via a non-query command execution?

Answer 1

UPDATE ... RETURNING ... returns a regular resultset, with as many rows in it as were updated in the database. You can consume that resultset just like you would any regular SELECT query, with DbCommand.ExecuteReader().

OUT parameters are suitable only for cases where a scalar value is returned (or a fixed number of scalar values), but not where an arbitrary number of rows (with an arbitrary number of columns).

In general, OUT parameters aren't really useful with Npgsql/PostgreSQL - wherever you're thinking of using OUT parameters, you could just as well simply process the output as a regular resultset. Read this section of the docs for more info..