Job in Postgresql(A question not an opening)

[newbiepg]

I have two columns in a table, one containing a date
and another an approval flag.
I want that after the system date crosses the date in the column, the approval flag be changed to 'N'.
We have jobs in Oracle , is it possible to have a similar job in PG?
Thanks

 

[rycamor]

I think at the moment PostgreSQL is only event-driven. However, it wouldn't be too hard to emulate this behavior, with a couple of workarounds.

1. If there is any table or view that you KNOW for certain will be run every day in your database (or whatever regular time interval you require), then you can simply attach a trigger, which will call a procedure which checks the date, and updates your table appropriately.

2. If you can run a cron job on your system to call a certain postgreSQL function every day (hour|minute), this function can simply check the dates in that table and update the appropriate column

3. (This method might have performance implications, but it has the best chance of ensuring data itegrity) PostgreSQL cannot have an on-select trigger, since triggers only fire upon modification of a table. But, PostgreSQL RULEs can be on-select. Thus, you might be able to define an rule which calls your procedure to do the updates, before continuing with the table/view select. (see

http://www.postgresql.org/idocs/index.php?sql-createrule.html)

Caveat: I don't know about the atomicity of the above operation. Since PostgreSQL doesn't support nested transactions, it might happen that the SELECT (the SELECT inside the rule) still returns the "old" view of the data, even thought the procedure has been called, and that the table update won't be visible until after any particular SELECT query. If this is the case, you might have to include some CASE statements in your SELECT query inside the rule, to return the right column data. Better yet, create a view that encapsulates this logic, and attach a RULE to the view which handles the update "after the fact".

OR, If the above is too messy, and if you are using PostgrSQL 7.3 then you can return sets directly from a procedure (function). In that case simply have the rule do a query to a procedure, and the procedure itself can return the correct data, as it updates the table also. -------------------------------------------

PHP/Perl/SQL/Javascript --

http://brainscraps.com/resume/