Re: Types and SRF's
От | Pierre-Frédéric Caillaud |
---|---|
Тема | Re: Types and SRF's |
Дата | |
Msg-id | opsdmy8peicq72hf@musicbox обсуждение исходный текст |
Ответ на | Types and SRF's (Jerry LeVan <jerry.levan@eku.edu>) |
Ответы |
Re: Types and SRF's
|
Список | pgsql-general |
Your query looks suspiciously complicated... Why not process all 12 months in one shot with something like this : - only one subquery - no join - date between can make an index scan on date select category, sum(amount) as sum_amount, extract (month from date) as month from all_accounts where (date between beginning of the year and end of the year) group by category,month order by category,month ) Not what you wanted but probably massively faster. Or you can do this (in approximate SQL): create type annual_report_type as ( sums numeric(9,2)[12] ); create type my_type as ( month integer, amount numeric ); CREATE AGGREGATE my_sum takes one input which is my_type and sums the amount into the month column of annual_report_type Then : select category, my_sum( my_type(month,amount) as report, extract (month from date) as month from all_accounts where (date between beginning of the year and end of the year) group by category,month order by category,month ) Dunno if this would work, it would be nice I think.
В списке pgsql-general по дате отправления: