CODEWITHSUNDEEP
postgresqlrules
videocakes
videocakes

Reputation: 57

How does one call a function from a postgresql rule that has access to NEW and OLD?

I'm new to postgresql (and therefore rules) and I've looked around, but can't really find an example calling a 'global' function.

I am using a normalized database where rows will be flagged as deleted rather than deleted. However, I would like to retain the DELETE FROM... functionality for the end user, by using an instead of delete rule to update the table's deleted_time column. Each table should, therefore, be able to use a common function, but I am not sure how this would be called in this context, or how it would have access to NEW and OLD?

CREATE OR REPLACE RULE rule_tablename_delete AS ON DELETE
TO tablename DO INSTEAD (
   /// call function here to update the table's delete_time column
);

Is this even the correct approach? (I note that INSTEAD OF triggers are restricted to views only)

Upvotes: 0

Views: 1685

Answers (1)

user330315
user330315

Reputation:

Just use an UPDATE statement:

create rule rule_tablename_delete as
  on delete to tablename
do instead
   update tablename 
      set delete_time = current_timestamp
    where id = old.id
      and delete_time is null;

Assuming that the id column is the primary key of that table.

Some more examples are in the manual: http://www.postgresql.org/docs/9.1/static/rules-update.html

Upvotes: 4

Related Questions