But that said, views are an alternative to consider. Unfortunately I've already encountered cases where PostgreSQL chooses bad query plans, and so I need to create a temporary table then query it. That is not something that I can do in a view. I've also encountered cases where it is easier to do some simple calculations outside of the database. (Example: what is the spread from one event to another in months? The catch is that if the first event happened Jan 31, and the second one Feb 2, the spread is 1 month. That kind of date arithmetic is very messy in SQL.)

In fact both of these happened on the very data set that I'd like to be able to pull into Excel. :-(

Now there is another workaround, and that is to create stored procedures in the database and select from that. I have reasons for not doing that as well, but if I can't make an intermediate layer work then I'd seriously consider going there.

Apologies for mentioning MySQL, I'm less familiar with PostgreSQL.

Is the date manipulation issue that much more cumbersome with SQL than perl?

select period_diff(extract(year_month from date1), extract(year_month 
+from date2)) as month_spread from foo;
[download]

I guess a big consideration for this is whether the data is allowed to be a little stale, in which case you don't need any complex technology and can simply periodically recreate tables in the db via a scheduled perl job.

If it does need to be 'on demand' (i.e. someone hitting refresh in Excel needs/wants/expects the *live* data rather than something 5 mins old) then this might be accomplished with a db trigger which shells out to run a perl script to repopulate a table. Although the HTTP route might still be better here I guess.

It also depends on data set sizes, time to run the 'report', etc.

Um, period_diff is a MySQL extension. So is year_month. (Most useful ways of dealing with dates in databases tend to be extensions.)

After messing around with http://www.postgresql.org/docs/8.2/static/functions-datetime.html the best that I could find in PostgreSQL looks something like this:

SELECT
    12 * extract(year from date1) + extract(month from date1)
      - 12 * extract(year from date2) - extract (month from date2)
        as month_spread
FROM foo
[download]

(I guess my Perl solution was longer, but it was more obvious code for me.)

But if views do the job, then that may be the way to go. I'd also think about how often the data really needs to be "refreshed", and then maybe just generate the data daily/weekly/monthly/whatever.