Types and SRF's
Hi,
I am trying to get my feet wet in SRF's
I had to define a type in order to get my first attempt
at a srf for an sql language function ie..
create type annual_report_type as
( category text,
jan numeric(9,2),
feb numeric(9,2),
mar numeric(9,2),
apr numeric(9,2),
may numeric(9,2),
jun numeric(9,2),
jul numeric(9,2),
aug numeric(9,2),
sep numeric(9,2),
oct numeric(9,2),
nov numeric(9,2),
dec numeric(9,2) ,
total numeric(9,2) )
and then use this type as
create or replace function annual_report(integer) returns setof
annual_report_type
as '
select a.category, (select sum(amount) from all_accounts where
category=a.category and
extract (month from date) = 1 and extract (year from date) = $1) as
jan,
(select sum(amount) from all_accounts where category=a.category and
extract (month from date) = 2 and extract (year from date) = $1) as
feb,
...
...
(select sum(amount) from all_accounts where category=a.category and
extract (year from date) = $1) as total
from all_accounts a
group by category
order by category
' language sql
The above seems to be working fine...
I would feel a bit more comfortable if I could recover the definition
of the type at a later time, I cannot seem to find the definition of
the type in pg_type (there is an entry but the definition does not seem
to
be visible).
It does not seem possible to replace "annual_report_type" in the
function
definition with just the type...All of the placements fail for me in
any case.
Any suggestions as to how I can remember the rowtype? or (embed the
definiton
of the type in the definition of the function without having to create
an explicit type?
Jerry
Jerry LeVan <jerry.levan@eku.edu> writes:
I would feel a bit more comfortable if I could recover the definition
of the type at a later time,
Try "\d annual_report_type" in psql.
regards, tom lane
Doh, I was using \dT and \dT+....
Thanks
Jerry
On Aug 31, 2004, at 3:14 PM, Tom Lane wrote:
Show quoted text
Jerry LeVan <jerry.levan@eku.edu> writes:
I would feel a bit more comfortable if I could recover the definition
of the type at a later time,Try "\d annual_report_type" in psql.
regards, tom lane
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.
Thank you for the response Pierre,
select category, sum(amount) as sum_amount, extract (month from date)
as month
from all_accounts where (extract(year from date)=2003)
group by category,month order by category,month
is certainly much faster than what I am doing but as you pointed out,
I want the table to have a column for each month ( and a grand total
as the last column).
I have not used arrays and aggregates, I will take a look....
Jerry
On Sep 1, 2004, at 3:03 AM, Pierre-Frédéric Caillaud wrote:
Show quoted text
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 dateselect 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_typeThen :
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.