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