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 ...
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;
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.