Calculate interest on postgresql with trigger/function

Question

I'm currently working on a simple banking application.

I have built a postgresql database, with the right tables and functions.

My problem is, that I am not sure how to calculate the interest rate on the accounts. I have a function that will tell me the balance, on a time.

If we say that we have a 1 month period, where I want to calculate the interest on the account. The balance looks like this:

  February     Balance
  1.           $1000
  3.           $300
  10.          $700
  27.          $500

  Balance on end of month: $500

My initial thoughts are to make a for loop, looping from the 1st in the month, to the last day in month, and adding the interest earned for that particular day in a row.

The function I want to use at end of month should be something like addInterest(startDate,endDate,accountNumber), which should insert one row into the table, adding the earned rate.

Could someone bring me on the right track, or show me some good learning resources on PL/PGSQL?

Edit

I have been reading a bit on cursors. Should I use a cursor to walk through the table?

I find it a bit confusing to use cursors, anyone here with some well explained examples?

Answer 1

There are various ways of interest calculation in banking system.

Interest = Balance x Rate x Days / Year

Types of Balances

• Periodical Aggregate Balance
• Daily Aggregate Balance

Types of Rates

• Fixed Rate Dynamic Rate (according to balance)
• Dynamic Rate (according to term)
• Dynamic Rate (according to schedule)

Types of Days/Schedules

• End of Day Processing (One day)
• End of Month Processing (One month)
• End of Quarter Processing (Three months)
• End of Half Processing (Six months)
• End of Year Processing (One year)

Year Formula

• A year could consist of 365 or 366 days.
• Your user might want to override number of days in a year, maintain a separate year variable property in your application.

Conclusion

Interest should be calculated as a routine task. Best approach would be that would run on a schedule depending upon the frequency setup of individual accounts.

Answer 2

The manual has a section about loops and looping through query results. There are also examples of trigger functions written in pl/pgsql. The manual is very complete, it's the best source I know of.