Lists: | pgsql-general |
---|
From: | Jerry LeVan <jerry(dot)levan(at)eku(dot)edu> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Types and SRF's |
Date: | 2004-08-31 18:59:52 |
Message-ID: | F0F31D66-FB7F-11D8-BE09-000393779D9C@eku.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jerry LeVan <jerry(dot)levan(at)eku(dot)edu> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Types and SRF's |
Date: | 2004-08-31 19:14:52 |
Message-ID: | 14026.1093979692@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Jerry LeVan <jerry(dot)levan(at)eku(dot)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
From: | Jerry LeVan <jerry(dot)levan(at)eku(dot)edu> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Types and SRF's |
Date: | 2004-08-31 19:23:10 |
Message-ID: | 31AB1438-FB83-11D8-BE09-000393779D9C@eku.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Doh, I was using \dT and \dT+....
Thanks
Jerry
On Aug 31, 2004, at 3:14 PM, Tom Lane wrote:
> Jerry LeVan <jerry(dot)levan(at)eku(dot)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
>
From: | Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com> |
---|---|
To: | "Jerry LeVan" <jerry(dot)levan(at)eku(dot)edu>, "Postgres General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Types and SRF's |
Date: | 2004-09-01 07:03:51 |
Message-ID: | opsdmy8peicq72hf@musicbox |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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.
From: | Jerry LeVan <jerry(dot)levan(at)eku(dot)edu> |
---|---|
To: | Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com> |
Cc: | "Postgres General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Types and SRF's |
Date: | 2004-09-01 12:24:44 |
Message-ID: | E7DFF7FD-FC11-11D8-BE09-000393779D9C@eku.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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:
>
> 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.
>