Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    18

    Unanswered: Auto update a field

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Don't update anything.

    Create a view that returns the status based on the current_date

  3. #3
    Join Date
    Mar 2011
    Posts
    18
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •