У меня есть следующая функция:
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; Я использую это с PHP PDO:
$stmt = $pdo->prepare('SELECT * FROM user_delete(?)'); $stmt->execute(array($user['id']));
Результат теперь
array( array('user_delete' => '') )
и поэтому
$stmt->rowCount();
всегда один.
Можно ли исправить это: функцией вернуть ничего (потому что оно пустое), а rowCount возвращает количество затронутых строк?
Решение:
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;
Я могу достичь результата с нулевой длиной с помощью setof void return type, но это не обязательно, если я заставляю его бросать PDOException, когда ресурс не найден …
Вы можете использовать
GET DIAGNOSTICS integer_var = ROW_COUNT;
.. и пусть функция возвращает счет. Подробности в руководстве здесь.
Пример:
CREATE FUNCTION x.user_delete(id int, OUT del_ct int) RETURNS int AS $func$ DECLARE i int; -- helper var BEGIN SELECT * FROM "user" WHERE user_id = id FOR UPDATE; DELETE FROM user_role WHERE user_id := id; GET DIAGNOSTICS i = ROW_COUNT; del_ct = i; DELETE FROM user_permission WHERE user_id = id; GET DIAGNOSTICS i = ROW_COUNT; del_ct := del_ct + i; DELETE FROM permission_cache WHERE user_id = id; GET DIAGNOSTICS i = ROW_COUNT; del_ct := del_ct + i; DELETE FROM access WHERE user_id = id; GET DIAGNOSTICS i = ROW_COUNT; del_ct := del_ct + i; DELETE FROM "user" WHERE user_id = id; GET DIAGNOSTICS i = ROW_COUNT; del_ct := del_ct + i; END $func$ LANGUAGE plpgsql;