where to store report info

[Jonathan Ellis]

On Tue, Dec 9, 2008 at 5:50 PM, Kyle Waters <unum at unum5.org> wrote:
> I almost sent this to udbug but felt it was more of a general question.
> I have a database with lots of information in it, and find that I need to
> run a report the is going to take up to several minutes to run.  It's ok if
> I only run this report once a day in the morning before everyone comes in.
>  Now the question that perplexes me is how should I store this report.  I
> think it would be good for it to be viewable as a web page by many people in
> the company, but one user will need to be able to download it as a csv file.
> So part of me wants to treat this like a materialized view.  Though I don't
> want to do it all in pg/sql.  Is that an acceptable solution?  Grab
> information out of the database, process it and then stick it back into a
> table.  Is there a better more acceptable way of doing this?

yes, but you can also write it in pl/python, pl/php, or pretty miuch
anything that stikes your fancy, and assign that function to a
trigger.  (pl/python can import modules from the filesystem, too,
making it easier to re-use existing code.  I imagine the other
languages can do the same.)  using a trigger means your data will be
always consistent instead of waiting for a cron job or whatever.

also, one common use for replication is to copy stuff like this to a
separate server where people can run long-ass queries w/o affecting
the live db.  Sounds like you don't need that yet but if you start to
get more reports like this, and I suspect you will, that's something
to keep in mind as an option.

-Jonathan