Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id BAC019FBE64 for ; Wed, 21 Feb 2007 15:31:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 42329-01-7 for ; Wed, 21 Feb 2007 15:30:57 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169]) by postgresql.org (Postfix) with ESMTP id 28AD79FBDF9 for ; Wed, 21 Feb 2007 15:30:51 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so983522ugf for ; Wed, 21 Feb 2007 11:30:50 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=IaqiG1LXhN6L8H0TiQzBtDrjW/+sQYaqSuASlD/d1z/EuEXXQEsw4T+2GRD9c114wrK75tas6HlM0CCnHXrJhRc8yYt9PpfG8JmoxVd8wOrWDe7zXxMP9XJydZ9PZU0VJdpEYBwCTOlIvetd/44tty5GYeEZDP4cNzqf2S+Kn4o= Received: by 10.78.158.11 with SMTP id g11mr57495hue.1172086250268; Wed, 21 Feb 2007 11:30:50 -0800 (PST) Received: by 10.78.35.18 with HTTP; Wed, 21 Feb 2007 11:30:49 -0800 (PST) Message-ID: Date: Wed, 21 Feb 2007 14:30:50 -0500 From: "Merlin Moncure" To: "Dan Harris" Subject: Re: General advice on user functions Cc: "PostgreSQL Performance" In-Reply-To: <45DC9074.3040901@drivefaster.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <45DC9074.3040901@drivefaster.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/219 X-Sequence-Number: 23273 On 2/21/07, Dan Harris 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