pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (4+ messages)  latest in thread

Message-ID: <b42b73150702211130x2152b9aejc94352c74db246ed@mail.gmail.com>
Permalink:  ../b42b73150702211130x2152b9aejc94352c74db246ed@mail.gmail.com/
Also on:    postgresql.org/message-id/b42b73150702211130x2152b9aejc94352c74db246ed@mail.gmail.com

 · 

reply

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