计算受 plpgsql 函数影响的行数

2021-12-26 00:00:00 postgresql count php pdo plpgsql

我有以下功能:

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;

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

    • 但随后的 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.

相关文章