Re: General advice on user functions
От | Merlin Moncure |
---|---|
Тема | Re: General advice on user functions |
Дата | |
Msg-id | b42b73150702211130x2152b9aejc94352c74db246ed@mail.gmail.com обсуждение исходный текст |
Ответ на | General advice on user functions (Dan Harris <fbsd@drivefaster.net>) |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: