Can the return value of DELETE be modified in PostgreSQL

Question

I am trying to prevent users from being able to irrevocably delete spaces in a Confluence wiki. My first quick thought was to rename the spaces table to allspaces, add a new column deleted, and create a view spaces in place of the old table. The view will only return non-deleted spaces. I have created three rules to allow INSERT, UPDATE, and DELETE on the spaces view. The DELETE rule just changes the deleted field and thus removes it from the view yet all the data stays in the database.

The problem is now DELETE statements on spaces return DELETE 0 from PostgreSQL. This causes Hibernate to flip out and toss an exception and Confluence blows up.

Is there anyway to have PostgreSQL return rows modified instead of rows deleted on an INSTEAD rule.

Answer 1

I'm not really following what you want to achieve, but perhaps you could put the delete statement in a function that returns VOID and then call it it.

CREATE OR REPLACE FUNCTION delete_space(pid integer)
  RETURNS void AS
$BODY$ 
DECLARE aid INTEGER;
BEGIN
    delete from spaces where id=pid;
    return;
END;
$BODY$
  LANGUAGE 'plpgsql';

To use:

select * from delete_space(3);