php 计算受plpgsql函数影响的行数

5f0d552i 于 2023-03-16 发布在 PHP

关注(0)|答案(1)|浏览(134)

我有以下功能：

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();

总是一。
是否可以修复此问题：函数不返回任何值（因为它是void），而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返回类型实现返回零长度结果，但如果我在找不到资源时强制它抛出PDOException，那就没有必要了...

php

来源：https://stackoverflow.com/questions/18193450/count-the-rows-affected-by-plpgsql-function

1条答案

按热度按时间

brccelvz1#

您可以用途：

GET DIAGNOSTICS integer_var = ROW_COUNT;

..并让函数返回计数。详细说明见手册。
示例：

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

您的第一条语句是：

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

无效语法-在plpgsql函数中，您需要对没有目标的SELECT语句使用PERFORM：

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

SELECT在PL/pgSQL函数中引发异常

但是随后的DELETE语句也会锁定该行，不需要从FOR UPDATE开始手动锁定。
添加的OUT del_ct int声明了一个OUT参数，它可以像任何变量一样赋值，并且在函数结束时自动返回，它还避免了显式RETURNS声明的需要。

赞(0）回复(0）举报 2023-03-16

我来回答

php 计算受plpgsql函数影响的行数

1条答案

相关问题

热门标签

最新问答