Rules / Triggers and Return Row Count

From: "mj" <mjrother(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Rules / Triggers and Return Row Count
Date: 2006-09-10 13:51:10
Message-ID: 1157896270.157097.68420@m79g2000cwm.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am attempting to accomplish a soft delete with a system using
PostgreSQL, Hibernate, and EJB3(JBoss). I have set up tables with a
column to indicate deleted state. What is the best way to accomplish
this? I have looked into triggers and rules but end up with the same
problem. How can I return to Hibernate that 1 row was effected by the
SQL delete. With a trigger, you must return NULL if you do not want the
action to continue. With a rule, it works fine but the number of rows
effected that is returned is always 0. I has something to do with
command status but I don't can't find an example that works.

My current rule is
CREATE RULE XXXXXX AS ON DELETE TO XXXX_VIEW
DO INSTEAD UPDATE XXXXXX SET DELETED = 1 WHERE OLD.ID = ID;

When you execute a SQL delete, the row is flagged with DELETED=1 but
the returned row count = 0 and Hibernate throws and exception.

Does anyne have a work around for this problem?

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-09-10 14:55:00 Re: execute in pl/pgsql
Previous Message Dave Cramer 2006-09-10 12:21:02 Re: [JDBC] Idle in transaction state.