Dapper Execute function that can call PostgreSQL function and return rows affected from update or insert

Question

I am trying to create a postgresql function that does an update. I want to get the rows affected. I know this can be done by using GET DIAGNOSTICS rows_affected = ROW_COUNT. But I am trying to use the Dapper Execute function which can return rows affected on its own. As far as I've been able to determine, that only works if you've got a hardcoded query you're calling, e.g. in C#

IDbConnection dbConnection = new NpgsqlConnection(strConnection)
string command = "UPDATE myschema.mytable SET email = '' WHERE email NOT LIKE '%@%';"
int rowsAffected = dbConnection.Execute(command);

I would like to create a function that does the same thing, and have Dapper call it with Execute as opposed to Query. Here is what I have so far:

CREATE FUNCTION "Xtream"."fnUpdateUserID" () RETURNS VOID
AS $$
BEGIN
UPDATE myschema.mytable
SET email = ''
WHERE email NOT LIKE '%@%';
END;
$$ LANGUAGE 'plpgsql';

So my question is, is there a way to get the rows affected without adding something like an int return to that function? In other words, get access to this information: "Query returned successfully: 2 rows affected, 11 msec execution time." that returns when calling UPDATE myschema.mytable SET email = '' WHERE email NOT LIKE '%@%' directly in a postgresql query window. I know RETURNS VOID doesn't work for what I'm trying.

To phrase this in another way, is there any way at all to use Dapper Execute on a PostgreSQL insert or update function?

Answer 1

Since I have been unable to find a way to do what I wanted, I went ahead and used the GET DIAGNOSTICS solution I mentioned above. I am posting it as an answer for posterity. I still welcome other solutions if they exist.

Change the function as follows:

CREATE FUNCTION "Xtream"."fnUpdateUserID" () RETURNS integer
AS $$
DECLARE rows_affected integer;
BEGIN
    UPDATE myschema.mytable
    SET email = ''
    WHERE email NOT LIKE '%@%';
    GET DIAGNOSTICS rows_affected = ROW_COUNT;
    RETURN rows_affected;
END;
$$ LANGUAGE 'plpgsql';

Then change the Dapper call like so:

IDbConnection dbConnection = new NpgsqlConnection(strConnection)
string command = "UPDATE myschema.mytable SET email = '' WHERE email NOT LIKE '%@%';"
int rowsAffected = dbConnection.Query<int>(command).FirstOrDefault;