On Thu, 23 Mar 2006, Bradley Kieser wrote: > All, > > I know that this isn't strictly an admin question but please forgive me > for asking. > I am writing a (admin) job which does a select off a PG database based > on three columns: > > last_backup: timestamp > backup_unit: integer - Represents day, week, quarter, annual, etc. The > text is stored in backup_code (e.g. 'days', 'months') > backup_period: integer - Represents the skip factor. > > i.e. if backup_unit is 1 (days) and period is 3, then together they > represent "every three days". > > Obviously I want to do something like: > > select id, client > from backupSchedule > where last_backup + interval backup_period backup_code <= CURRENT_DATE > > > However, interval seems to only take text such as > inverval '3 days' > > and I get an error even with this: > > select id, client > from backupSchedule > where last_backup + interval backup_period::text || backup_code <= > CURRENT_DATE > > Can someone please point me to the right statement to use for > column-based interval arithmetic? > The docs all give hardcoded text strings in examples. Not found one yet > with a proper column-based query! Well, that's because the interval <blah> syntax is for interval literals. CAST( backup_period || ' ' || backup_code AS interval) should give you an interval. If the units were constant, I'd say that using integer * interval is a better idea, but I think you'd need a function that say took backup_unit and gave back an interval of 1 <unit> to make that work, but that would possibly be cleaner overall.
