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
