From: Merlin Moncure <mmoncure@gmail.com>
To: Dan Harris <fbsd@drivefaster.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: General advice on user functions
Date: Wed, 21 Feb 2007 14:30:50 -0500
Message-ID: <b42b73150702211130x2152b9aejc94352c74db246ed@mail.gmail.com> (raw)
In-Reply-To: <45DC9074.3040901@drivefaster.net>
References: <45DC9074.3040901@drivefaster.net>
On 2/21/07, Dan Harris <fbsd@drivefaster.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: mmoncure@gmail.com, fbsd@drivefaster.net
Subject: Re: General advice on user functions
In-Reply-To: <b42b73150702211130x2152b9aejc94352c74db246ed@mail.gmail.com>
References: <45DC9074.3040901@drivefaster.net> <b42b73150702211130x2152b9aejc94352c74db246ed@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox