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

Question

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

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; 

Answer 1

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

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.

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 )

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.

Answer 2

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

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.