Types and SRF's
От | Jerry LeVan |
---|---|
Тема | Types and SRF's |
Дата | |
Msg-id | F0F31D66-FB7F-11D8-BE09-000393779D9C@eku.edu обсуждение исходный текст |
Ответы |
Re: Types and SRF's
Re: Types and SRF's |
Список | 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
В списке pgsql-general по дате отправления: