postgresql 包含受先前 DELETE 影响的行数的变量？（在函数中）

Variable containing the number of rows affected by previous DELETE? (in a function)

提问by Bruno Bronosky

I have a function that is used as an INSERT trigger. This function deletes rows that would conflict with [the serial number in] the row being inserted. It works beautifully, so I'd really rather not debate the merits of the concept.

我有一个用作 INSERT 触发器的函数。此函数删除与插入的行中的 [序列号] 冲突的行。它工作得很好，所以我真的不想争论这个概念的优点。

DECLARE
re1 feeds_item.shareurl%TYPE;
BEGIN
SELECT regexp_replace(NEW.shareurl, '/[^/]+(-[0-9]+\.html)$','/[^/]+\1') INTO re1;
RAISE NOTICE 'DELETEing rows from feeds_item where shareurl ~ ''%''', re1;

DELETE FROM feeds_item where shareurl ~ re1;
RETURN NEW;
END;

I would like to add to the NOTICE an indication of how many rows are affected (aka: deleted). How can I do that (using LANGUAGE 'plpgsql')?

我想在通知中添加一个指示有多少行受到影响（又名：删除）。我该怎么做（使用 LANGUAGE 'plpgsql'）？

UPDATE:Base on some excellent guidance from "Chicken in the kitchen", I have changed it to this:

更新：根据“厨房里的鸡”的一些出色指导，我已将其更改为：

DECLARE
re1 feeds_item.shareurl%TYPE;
num_rows int;
BEGIN
SELECT regexp_replace(NEW.shareurl, '/[^/]+(-[0-9]+\.html)$','/[^/]+\1') INTO re1;

DELETE FROM feeds_item where shareurl ~ re1;
IF FOUND THEN
    GET DIAGNOSTICS num_rows = ROW_COUNT;
    RAISE NOTICE 'DELETEd % row(s) from feeds_item where shareurl ~ ''%''', num_rows, re1;
END IF;
RETURN NEW;
END;

采纳答案by UltraCommit

In Oracle PL/SQL, the system variable to store the number of deleted / inserted / updated rows is:

在 Oracle PL/SQL 中，存储删除/插入/更新行数的系统变量是：

SQL%ROWCOUNT

After a DELETE / INSERT / UPDATE statement, and BEFORE COMMITTING, you can store SQL%ROWCOUNT in a variable of type NUMBER. Remember that COMMIT or ROLLBACK reset to ZERO the value of SQL%ROWCOUNT, so you have to copy the SQL%ROWCOUNT value in a variable BEFORE COMMIT or ROLLBACK.

在 DELETE / INSERT / UPDATE 语句和 BEFORE COMMITTING 之后，您可以将 SQL%ROWCOUNT 存储在类型为 NUMBER 的变量中。请记住，COMMIT 或 ROLLBACK 会将 SQL%ROWCOUNT 的值重置为零，因此您必须在 COMMIT 或 ROLLBACK 之前将 SQL%ROWCOUNT 值复制到变量中。

Example:

例子：

BEGIN
   DECLARE
      affected_rows   NUMBER DEFAULT 0;
   BEGIN
      DELETE FROM feeds_item
            WHERE shareurl = re1;

      affected_rows := SQL%ROWCOUNT;
      DBMS_OUTPUT.
       put_line (
            'This DELETE would affect '
         || affected_rows
         || ' records in FEEDS_ITEM table.');
      ROLLBACK;
   END;
END;

I have found also this interesting SOLUTION (source: http://markmail.org/message/grqap2pncqd6w3sp)

我也发现了这个有趣的解决方案（来源：http: //markmail.org/message/grqap2pncqd6w3sp）

On 4/7/07, Karthikeyan Sundaram wrote:

Hi,

I am using 8.1.0 postgres and trying to write a plpgsql block.  In that I am inserting a row.  I want to check to see if the row has been

inserted or not.

In oracle we can say like this

begin
  insert into table_a values (1);
  if sql%rowcount > 0
  then
    dbms.output.put_line('rows inserted');
  else
    dbms.output.put_line('rows not inserted');
 end if;  end;

Is there something equal to sql%rowcount in postgres? Please help.

Regards skarthi

Maybe:

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

Click on the link above, you'll see this content:

37.6.6. Obtaining the Result Status There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command, which has the form:

GET DIAGNOSTICS variable = item [ , ... ];This command allows retrieval of system status indicators. Each item is a key word identifying a state value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are ROW_COUNT, the number of rows processed by the last SQL command sent down to the SQL engine, and RESULT_OID, the OID of the last row inserted by the most recent SQL command. Note that RESULT_OID is only useful after an INSERT command into a table containing OIDs.

An example:

GET DIAGNOSTICS integer_var = ROW_COUNT; The second method to determine the effects of a command is to check the special variable named FOUND, which is of type boolean. FOUND starts out false within each PL/pgSQL function call. It is set by each of the following types of statements:

A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.

A PERFORM statement sets FOUND true if it produces (and discards) a row, false if no row is produced.

UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.

A FETCH statement sets FOUND true if it returns a row, false if no row is returned.

A FOR statement sets FOUND true if it iterates one or more times, else false. This applies to all three variants of the FOR statement (integer FOR loops, record-set FOR loops, and dynamic record-set FOR loops). FOUND is set this way when the FOR loop exits; inside the execution of the loop, FOUND is not modified by the FOR statement, although it may be changed by the execution of other statements within the loop body.

FOUND is a local variable within each PL/pgSQL function; any changes to it affect only the current function.

2007 年 4 月 7 日，Karthikeyan Sundaram 写道：

你好，

I am using 8.1.0 postgres and trying to write a plpgsql block.  In that I am inserting a row.  I want to check to see if the row has been

插入与否。

在oracle中我们可以这样说

begin
  insert into table_a values (1);
  if sql%rowcount > 0
  then
    dbms.output.put_line('rows inserted');
  else
    dbms.output.put_line('rows not inserted');
 end if;  end;

postgres 中是否有等于 sql%rowcount 的东西？请帮忙。

问候斯卡蒂

也许：

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

点击上面的链接，你会看到这个内容：

37.6.6. 获取结果状态 有多种方法可以确定命令的效果。第一种方法是使用 GET DIAGNOSTICS 命令，其格式为：

GET DIAGNOSTICS variable = item [ , ... ];该命令允许检索系统状态指示器。每个项目都是一个关键字，用于标识要分配给指定变量（应该是正确的数据类型以接收它）的状态值。当前可用的状态项是 ROW_COUNT，最后一个发送到 SQL 引擎的 SQL 命令处理的行数，以及 RESULT_OID，最近的 SQL 命令插入的最后一行的 OID。请注意，RESULT_OID 仅在对包含 OID 的表执行 INSERT 命令后才有用。

一个例子：

获取诊断 integer_var = ROW_COUNT; 确定命令效果的第二种方法是检查名为 FOUND 的特殊变量，它是布尔类型的。FOUND 在每个 PL/pgSQL 函数调用中以 false 开始。它由以下每种类型的语句设置：

SELECT INTO 语句将 FOUND 设置为在分配了行时为真，如果没有返回行则为假。

如果 PERFORM 语句生成（并丢弃）一行，则将 FOUND 设置为 true，如果没有生成行，则设置为 false。

如果至少有一行受到影响，UPDATE、INSERT 和 DELETE 语句将 FOUND 设置为 true，如果没有行受到影响，则设置为 false。

如果 FETCH 语句返回一行，则将 FOUND 设置为 true，如果没有返回行，则设置为 false。

如果 FOR 语句迭代一次或多次，则将 FOUND 设置为 true，否则设置为 false。这适用于 FOR 语句的所有三种变体（整数 FOR 循环、记录集 FOR 循环和动态记录集 FOR 循环）。FOUND 在 FOR 循环退出时以这种方式设置；在循环执行过程中，FOUND 不会被 FOR 语句修改，尽管它可能会被循环体内其他语句的执行所改变。

FOUND 是每个 PL/pgSQL 函数中的局部变量；对它的任何更改仅影响当前功能。

回答by Roelof Rossouw

For a very robust solution, that is part of PostgreSQL SQL and not just plpgsql you could also do the following:

对于非常强大的解决方案，它是 PostgreSQL SQL 的一部分，而不仅仅是 plpgsql，您还可以执行以下操作：

with a as (DELETE FROM feeds_item WHERE shareurl ~ re1 returning 1)
select count(*) from a;

You can actually get lots more information such as:

您实际上可以获得更多信息，例如：

with a as (delete from sales returning amount)
select sum(amount) from a;

to see totals, in this way you could get any aggregate and even group and filter it.

要查看总数，通过这种方式，您可以获得任何聚合甚至分组并对其进行过滤。

回答by Aron Elias Herrera Ponte

I would to share my code (I had this idea from Roelof Rossouw):

我想分享我的代码（我从 Roelof Rossouw 那里得到了这个想法）：

CREATE OR REPLACE FUNCTION my_schema.sp_delete_mytable(_id integer)
  RETURNS integer AS
$BODY$
  DECLARE
    AFFECTEDROWS integer;
  BEGIN
    WITH a AS (DELETE FROM mytable WHERE id = _id RETURNING 1)
    SELECT count(*) INTO AFFECTEDROWS FROM a;
    IF AFFECTEDROWS = 1 THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  EXCEPTION WHEN OTHERS THEN
    RETURN 0;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;