postgresql – 包含受以前DELETE影响的行数的变量？ (在功能中)

发布时间：2020-12-13 16:27:48 所属栏目：百科来源：网络整理

导读：我有一个用作INSERT触发器的函数.此功能删除与插入行中的[序列号]冲突的行.它精美地工作,所以我真的不会辩论这个概念的优点. DECLAREre1 feeds_item.shareurl%TYPE;BEGINSELECT regexp_replace(NEW.shareurl,'/[^/]+(-[0-9]+.html)$','/[^/]+1') INTO re1

我有一个用作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;

我想添加到“通知”中,指示有多少行受到影响(又称：删除).我怎么做(使用LANGUAGE’plpgsql’)？

更新：
基于“厨房里的鸡”的一些很好的指导,我把它改成了：

DECLARE
re1 feeds_item.shareurl%TYPE;
num_rows int;
BEGIN
SELECT regexp_replace(NEW.shareurl,'/[^/]+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;

在Oracle PL / SQL中,用于存储删除/插入/更新行数的系统变量为：

SQL%ROWCOUNT

在DELETE / INSERT / UPDATE语句之后,并且在进行之前,可以将SQL％ROWCOUNT存储在类型为NUMBER的变量中.请记住,COMMIT或ROLLBACK将SQL值ROWCOUNT的值重置为零,因此必须在COMMIT或ROLLBACK之前的变量B中复制SQL％ROWCOUNT值.

例：

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;

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

On 4/7/07,Karthikeyan Sundaram wrote:

Hi,

06002

inserted or not.

In oracle we can say like this

06003

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

Regards skarthi

Maybe:

07001

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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!