Date: Wed, 8 Nov 2006 00:13:36 -0600
From: "Jim C. Nasby" <jim@nasby.net>
To: louis gonzales <gonzales@linuxlouis.net>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Is there anyway to...
Message-ID: <20061108061336.GV90133@nasby.net>
References: <20061102171624.GE21312@localhost>
	<454A357E.6010409@linuxlouis.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <454A357E.6010409@linuxlouis.net>
User-Agent: Mutt/1.5.13 (2006-08-11)

Moving to -general (and please start a new thread instead of hijacking
an existing one).

On Thu, Nov 02, 2006 at 01:14:22PM -0500, louis gonzales wrote:
> Hello all,
> Is there an existing mechanism is postgresql that can automatically 
> increment/decrement on a daily basis w/out user interaction?  The use 
> case I'm considering is where a student is in some type of contract with 
> an instructor of some sort, and that contract puts a time limit on the 
> student requiring her to pay a fee by a certain day.  IF that day comes 
> to pass - or a certain number of days elapse - and that payment 
> requirement hasn't been met, I want to trigger a function.
> 
> The one requirement I want to impose is, that no end user of the DB 
> application, needs to do anything to set the trigger, other than the 
> initialization of making the student of this type.
> 
> An example would be:
> Day1 - Application user(typically the instructor) creates a profile for 
> a new student - John Doe, which sets a 30 day time limit for John Doe to 
> pay $100.00
> Day2 -> Day31 - John Doe didn't make the payment
> Day 31 - Trigger of event occurs when the instructor logs in.
> 
> Basically on Day 1 when John Doe's profile was created, I want a 
> decrement counter to occur daily on his profile(some attribute/timer) 
> and nothing should happen until day 31 when he doesn't pay.

While you could setup a cron job to decrement some counter every day, I
think that's not the best approach. Instead, I'd run a query once a day
that finds all students that are past-due and takes some kind of action.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)