Building a record in a function

From: Robert Fitzpatrick <lists(at)webtent(dot)net>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Building a record in a function
Date: 2007-02-27 15:03:28
Message-ID: 1172588608.2686.25.camel@columbus.webtent.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message George Nychis 2007-02-27 15:05:13 Re: performance of partitioning?
Previous Message Tom Lane 2007-02-27 15:00:01 Re: How does filter order relate to query optimization?