I have a pgsql table which grows quite large. I would like to rotate it using a cron job - the data is only used to generate reports and after a week or two, it is not required.
The proper way to do this is to set up table partitioning.
You establish a check constraint on a column, then use a rule driven by that check to direct rows inserted into the parent table to one of several child tables. In your case, I'd suggest weekly partitioning. If you want to let users query a single partition, you just name the partitions logically - say 2009_week_32, or 2009_august_week_1 and have them issues queries against the proper table.
If you want them to query multiple tables together, you set up a view that does a union select across multiple tables, and users query the view.
When you want to remove data that is no longer required, you just alter the table to change the rule and drop the table containing the old data.
This is a bit more work than disabledleopard's answer, but the technique is fairly common across other database engines. The downside to just using a "delete where" on a timestamp column is that it can take a very long time on large tables and that the data is written to the journal. Truncate is designed to get around the journal writing issue, but you can't specify a where clause. Dropping a table is very quick, as you're not dealing with individual rows.
You will need to have some kind of maintenance to set up rules to cover future dates. Some people script this monthly (just check 5 days before the end of the month if the next month's partition exists, and if not create it and perform the alter table magic to change the rules) while others just set up several months or years of rules in advance and perform the maintenance work manually every quarter / year as appropriate.
my thought would be to alter the schema of this table to log the timestamp the data was inserted.
this way what is currently creating the table need not change.
Then you're cron could then just be this command (should work with postgres 8.x but I don't have a server running to confirm sorry)
Edit: I forgot to mention the "RETURNING *" bit makes psql return full details of all deleted rows as well as the usual "N rows removed" so this log could be used with a bit of awk magic to re-insert data if removed prematurely. Also, audit logs for automatic deletes are always a good idea.