Auto update a field

kbum · #1 (**permalink**) 05-06-11, 10:45

Hello. I have a table with a follow fields:
date_next date
status character

I need update a the field status if the date_next < current_date.
This update need to be automatic. The check would be maked daily.

In DB2 i maked a Event Monitor. But how i can make this in PostgreSQL?

I'm using the PostgreSQL 8.2.

shammat · #2 (**permalink**) 05-06-11, 10:49

Don't update anything.

Create a view that returns the status based on the current_date

kbum · #3 (**permalink**) 05-06-11, 10:57

But the idea is, if the date_next is less than the current date, the status field must be updated.

Currently the update is done manually. I would like to automate this update.

shammat · #4 (**permalink**) 05-06-11, 11:11

No, you don't need the update. Just create a view that calculates the status "on-the-fly" and retrieve the status information through the view.

Code:

CREATE VIEW status_info
AS 
SELECT some_column, 
       date_next
       case 
          when date_next < current_date then 'STATUS_ONE'
          else 'STATUS_OTHER'
       end as status
FROM your_table

One rule in relational databases: do not store things you can determine from existing data.

If your application cannot be changed to use the view, rename the table, create the view with the name of the table and create RULEs to make the view updateable.

Then you never need to worry about the status column, it will always be correct without any background jobs.

Internet Services

Web Development

Digital Marketing

Consumer Tech