問題描述

我有以下功能:

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.

這篇關于計算受 plpgsql 函數影響的行數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！