我有以下功能:
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 一起使用:
I use this with PHP PDO:
$stmt = $pdo->prepare('SELECT * FROM user_delete(?)');
$stmt->execute(array($user['id']));
结果包含现在
array(
array('user_delete' => '')
)
所以
$stmt->rowCount();
总是一个.
是否可以解决这个问题:通过函数不返回任何内容(因为它是无效的),并且通过 rowCount 返回受影响行的计数?
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?
解决方案:
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
返回类型实现返回零长度结果,但是如果我在找不到资源时强制它抛出 PDOException
就没有必要了...
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 ...
您可以使用:
GET DIAGNOSTICS integer_var = ROW_COUNT;
.. 并让函数返回计数.手册中的详细信息.
.. and let the function return the count. Details in the manual.
示例:
CREATE OR REPLACE FUNCTION user_delete(id int, OUT del_ct int) 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$ LANGUAGE plpgsql;
您将此作为第一个声明:
You had this as 1st statement:
SELECT * FROM "user" WHERE user_id = $1 FOR UPDATE;
无效语法 - 在 plpgsql 函数中,您需要将 PERFORM
用于没有目标的 SELECT
语句:
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;
但随后的 DELETE
语句也同样锁定该行.不需要 使用 为更新
开始.
But the ensuing DELETE
statement locks the row just as well. No need for manual locking with FOR UPDATE
to begin with.
添加的OUT del_ct int
声明了一个OUT
参数,该参数可以像任何变量一样赋值,并在函数结束时自动返回.它还消除了对显式 RETURNS
声明的需要.
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.
这篇关于计算受 plpgsql 函数影响的行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!