CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 1852

How to check if deletion was successful in PostgreSQL function?

I have a PostgreSQL function which deletes rows from table. There can be a situation where a row that I want to delete was already deleted by someone else.. In that case I want to abort the function and roll back everything it's done.

The manual doesn't say how do I know if DELETE statement return without doing any deletion.

I wrote this function:

CREATE OR REPLACE FUNCTION func1()
  RETURNS integer AS
$BODY$
declare 
    line record;
    errorint int;
for line in select * from tab  where ...
LOOP
        DELETE from x WHERE...
        if 0 row deleted then:
            errorint =1;
            raise exception 'delete error';
end loop;               
return 0;           
exception
        when raise_exception then
            return errorint;
end;                
$BODY$
  LANGUAGE plpgsql VOLATILE

The DELETE statement will delete 1 or 0 row only. The WHERE gives only one row. meaning that success is when it says "Query returned successfully: 1 rows affected" and fail is when it says "Query returned successfully: 0 rows affected". I just don't know how to check what it returns. If there is another way to check it that would be acceptable as well.

Basically my question is how to convert the if 0 row deleted then to PostgreSQL syntax.

Upvotes: 4

Views: 7249

Answers (1)

Houari
Houari

Reputation: 5621

What you want to do is a mutual exclusion, and i think you have to look for table/row locking. But if you want a rapid answer that gives you how much rows were deleted, please look to this answer

Upvotes: 3

Related Questions