Building a record in a function

Поиск
Список
Период
Сортировка
От Robert Fitzpatrick
Тема Building a record in a function
Дата
Msg-id 1172588608.2686.25.camel@columbus.webtent.org
обсуждение исходный текст
Ответы Re: Building a record in a function  (Richard Huxton <dev@archonet.com>)
Список pgsql-general
Haven't done a whole lot of plsql returning records, only those based on
a query. I was wondering, can I build a record from the results of the
query using other values for some fields in the record? I know 'return
next' requires a record and want to build my own record to include some
argument values in the record. I have my record declared and my query
results in that record. So, how do you construct a record in a function?
Below is my work...

  period := $1;
  rep := $2;
  FOR dateval IN SELECT (period::date+(s||'month')::interval)::date from generate_series(0,11)s
  loop
    SELECT INTO picks
      COUNT(public.view_pick_1_data.units)::numeric AS units
    FROM
      public.view_pick_1_data
    WHERE
      (public.view_pick_1_data.rep = rep) AND
      (public.view_pick_1_data.start_date = dateval."date")
    GROUP BY
      public.view_pick_1_data.rep,
      public.view_pick_1_data.start_date;
    return next picks;
  end loop;
  return;

I need the period in the record. My record type has three fields of
text, date and numeric where I need the word units in the first field,
the period from the loop query in the second and count from the picks
query in the last field. If you could return fields, which I see you
cannot, it would look like this:

return next 'units', dateval."date", picks.units

The final objective is a crosstab based on the return of this function.

--
Robert


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Unable to restore dump due to client encoding issues -- or, when is SQL_ASCII really UTF8
Следующее
От: Tom Lane
Дата:
Сообщение: Re: How to debug this crash?