Can the return value of DELETE be modified in PostgreSQL

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开发者_JS百科 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.

---

Not sure how well this would scale (depending on the number of spaces you have to manage), but I would periodically backup the space to XML. This can be done easily through the API using Confluence CLI:

confluence --action exportSpace --space "spaceName" --file "target/output/confluencecli/spaceName.xml"

You could rotate these backups based on age and keep only the most recent ones in the event of a space deletion by a user.

To take this a step further, you could modify the action (confluence/spaces/removespace.vm) that actually deletes the space and insert the logic to backup the space to XML before the removal is confirmed. This would scale much nicer!

---

You could add an ON DELETE trigger which saves the deleted row to an archive table. You can add a rescue feature to your application to recover deleted rows.

---

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