Count the rows affected by plpgsql function

Question

I have the following function:

CREATE FUNCTION user_delete(IN id INT4)
  RETURNS VOID
AS
  $BODY$
  BEGIN
    SELECT * FROM "user" WHERE user_id = id FOR UPDATE;
    DELETE FROM user_role WHERE user_id = id;
    DELETE FROM user_permission WHERE user_id = id;
    DELETE FROM permission_cache WHERE user_id = id;
    DELETE FROM access WHERE user_id = id;
    DELETE FROM "user" WHERE user_id = id;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

I use this with PHP PDO:

$stmt = $pdo->prepare('SELECT * FROM user_delete(?)');
$stmt->execute(array($user['id']));

The result contains now

array(
    array('user_delete' => '')
)

and so the

$stmt->rowCount();

is always one.

Is it possible to fix this: by the function return nothing (because it is void), and by the rowCount return the count of the affected rows?

Solution:

php:

public function delete($id)
{
    try {
        $this->__call('user_delete', array($id));
    } catch (\PDOException $e) {
        if ($e->getCode() === 'UE404')
            throw new NotFoundException();
        else
            throw $e;
    }
}

sql:

CREATE FUNCTION user_delete(IN id INT4)
  RETURNS VOID
AS
  $BODY$
  BEGIN
    DELETE FROM user_role WHERE user_id = id;
    DELETE FROM user_permission WHERE user_id = id;
    DELETE FROM permission_cache WHERE user_id = id;
    DELETE FROM access WHERE user_id = id;
    DELETE FROM "user" WHERE user_id = id;
    IF NOT FOUND THEN
      RAISE SQLSTATE 'UE404' USING MESSAGE = 'not found for delete';
    END IF;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

I can achieve return zero length result with setof void return type, but that is not necessary if I force it to throw PDOException when the resource is not found ...

Answer 1

You can use:

GET DIAGNOSTICS integer_var = ROW_COUNT;

.. and let the function return the count. Details in the manual.

Example:

CREATE OR REPLACE FUNCTION user_delete(id int, OUT del_ct int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   i int;  -- helper var
BEGIN
   DELETE FROM user_role WHERE user_id = $1;
   GET DIAGNOSTICS del_ct = ROW_COUNT;  -- init

   DELETE FROM user_permission WHERE user_id = $1;
   GET DIAGNOSTICS i = ROW_COUNT;  del_ct := del_ct + i;

   DELETE FROM permission_cache WHERE user_id = $1;
   GET DIAGNOSTICS i = ROW_COUNT;  del_ct := del_ct + i;

   DELETE FROM access WHERE user_id = $1;
   GET DIAGNOSTICS i = ROW_COUNT;  del_ct := del_ct + i;

   DELETE FROM "user" WHERE user_id = $1;
   GET DIAGNOSTICS i = ROW_COUNT;  del_ct := del_ct + i;
END
$func$;

You had this as 1st statement:

SELECT * FROM "user" WHERE user_id = $1 FOR UPDATE;

Invalid syntax - inside a plpgsql function you need to use PERFORM for SELECT statements without target:

PERFORM * FROM "user" WHERE user_id = $1 FOR UPDATE;

• SELECT raises exception in PL/pgSQL function

But the ensuing DELETE statement locks the row just as well. No need for manual locking with FOR UPDATE to begin with.

The added OUT del_ct int declares an OUT parameter that can be assigned like any variable and is returned at the end of the function automatically. It also obviates the need for an explicit RETURNS declaration.