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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
