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;
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.
