Types and SRF's

Поиск
Список
Период
Сортировка
От Jerry LeVan
Тема Types and SRF's
Дата
Msg-id F0F31D66-FB7F-11D8-BE09-000393779D9C@eku.edu
обсуждение исходный текст
Ответы Re: Types and SRF's  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Types and SRF's  (Pierre-Frédéric Caillaud<lists@boutiquenumerique.com>)
Список 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 по дате отправления:

Предыдущее
От: "Vidyasagara Guntaka"
Дата:
Сообщение: Not able to build libpq for Windows
Следующее
От: Gaetano Mendola
Дата:
Сообщение: Re: Forcing a stored procedure recompile