Re: General advice on user functions

On 2/21/07, Dan Harris <fbsd(at)drivefaster(dot)net> wrote:
> I have a new task of automating the export of a very complex Crystal
> Report. One thing I have learned in the last 36 hours is that the
> export process to PDF is really, really, slooww..
>
> Anyway, that is none of your concern. But, I am thinking that I can
> somehow utilize some of PG's strengths to work around the bottleneck in
> Crystal. The main problem seems to be that tens of thousands of rows of
> data must be summarized in the report and calculations made. Based on
> my recent experience, I'd say that this task would be better suited to
> PG than relying on Crystal Reports to do the summarizing.
>
> The difficulty I'm having is that the data needed is from about 50
> different "snapshots" of counts over time. The queries are very simple,
> however I believe I am going to need to combine all of these queries
> into a single function that runs all 50 and then returns just the
> count(*) of each as a separate "column" in a single row.
>
> I have been Googling for hours and reading about PL/pgsql functions in
> the PG docs and I have yet to find examples that returns multiple items
> in a single row. I have seen cases that return "sets of", but that
> appears to be returning multiple rows, not columns. Maybe this I'm
> barking up the wrong tree?
>
> Here's the gist of what I need to do:
>
> 1) query count of rows that occurred between 14 months ago and 12 months
> ago for a given criteria, then count the rows that occurred between 2
> months ago and current. Repeat for 50 different where clauses.
>
> 2) return each count(*) as a "column" so that in the end I can say:
>
> select count_everything( ending_date );
>
> and have it return to me:
>
> count_a_lastyear count_a_last60 count_b_lastyear count_b_last60
> ---------------- -------------- ---------------- --------------
> 100 150 200 250
>
> I'm not even sure if a function is what I'm after, maybe this can be
> done in a view? I am embarrassed to ask something that seems like it
> should be easy, but some key piece of knowledge is escaping me on this.

this could be be done in a view, a function, or a view function combo.
you can select multiple counts at once like this:

select (select count(*) from foo) as foo, (select count(*) from bar) as bar;

but this may not be appropriate in some cases where something complex
is going on. you may certainly return multiple columns from a single
call using one of two methods:

* out parameters (8.1+)
* custom type

both of which basically return a record instead of a scalar. any
function call can be wrapped in a view which can be as simple as

create view foo as select * from my_count_proc();

this is especially advised if you want to float input parameters over
a table and also filter the inputs via 'where'.

merlin