包含受先前删除影响的行数的变量?(函数)

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.

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

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')?

我想要添加到通知中，显示有多少行受影响(又名:删除)。我如何做到这一点(使用语言“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;

3 个解决方案

#1

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.

在删除/插入/更新语句之后，在提交之前，可以将SQL%ROWCOUNT存储在类型号的变量中。记住，提交或回滚将SQL%ROWCOUNT的值重置为零，因此必须在提交或回滚之前将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:

在4月7日，Karthikeyan Sundaram写道:

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

在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;
Is there something equal to sql%rowcount in postgres? Please help.

在postgres中是否存在与sql%rowcount相等的内容?请帮助。

Regards skarthi

问候skarthi

Maybe:

可能:

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

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:

37.6.6。获得结果状态有几种方法可以确定命令的效果。第一种方法是使用GET DIAGNOSTICS命令，该命令具有以下形式:

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.

获取诊断变量=项[，…);该命令允许检索系统状态指示器。每个项都是一个关键字，它标识将被分配给指定变量的状态值(应该是正确的数据类型来接收它)。当前可用的状态项是ROW_COUNT，通过最后一条SQL命令处理的行数到SQL引擎，以及RESULT_OID，这是最近的SQL命令插入的最后一行的OID。注意，RESULT_OID仅在将插入命令插入包含oid的表后才有用。

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:

获取诊断integer_var = ROW_COUNT;确定命令效果的第二种方法是检查名为FOUND的特殊变量，该变量是boolean类型的。在每个PL/pgSQL函数调用中，find都是false。它是由下列每一种陈述所设定的:

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

如果一个行被赋值，则一个SELECT INTO语句集被发现为true，如果没有返回行，则为false。

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

如果生成(并丢弃)一行，则发现执行语句集为true;如果不生成行，则为false。

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

如果至少有一行受到影响，则查找为true，如果没有行受到影响，则查找为false。

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

如果返回一行，则获取语句集为true;如果不返回行，则为false。

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.

一个FOR语句集如果迭代一次或多次，就会发现为真，否则为假。这适用于FOR语句的所有三个变体(循环为整数，循环为记录集，循环为动态记录集)。当FOR循环退出时，find设置为这种方式;在执行循环的过程中，find不会被FOR语句修改，尽管可以通过执行循环主体中的其他语句来更改。

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

find是每个PL/pgSQL函数中的一个局部变量;对它的任何更改只影响当前函数。

#2

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

对于非常健壮的解决方案，这是PostgreSQL的一部分，而不仅仅是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.

要查看总数，这样就可以得到任何聚合，甚至是组并过滤它。

#3

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;

#1