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 |
Thread: | |
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 | Date | Subject | |
---|---|---|---|
Next Message | Matt Clark | 2004-08-31 18:59:55 | Re: Table UPDATE is too slow |
Previous Message | Gaetano Mendola | 2004-08-31 18:53:04 | Re: Forcing a stored procedure recompile |