Обсуждение: Building a record in a function

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

Robert Fitzpatrick wrote:
> 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,
    CURRENT_DATE AS "date",
    'x'::text AS some_text_field

That's the simplest way.
Alternatively, you can use CREATE TYPE to make the required row-type and
fill a variable of that type.
--
   Richard Huxton
   Archonet Ltd