New in 8.3: RETURN QUERY! See the Docs about the new feature. They added a nice RETURN NEXT example, too. (I wrote this back in 2008 - 8.3 is pretty old now!)
This example arose from a need to repeatedly run a complicated query with aggregate values on a large database. A view would be too much work for such a small subset of data, as it would have to calculate the sums for the whole database before applying the WHERE clause! A function returning a set of composite type values was the solution.
Basically, the function was the basis of a report of monies collected from apartment renters. There are a number of properties, and each property has 1..n units, and each unit has 0..n occupants. Each occupant makes 0..n payments in the report period.
First, a composite type (much like a RECORD type) must be created so it can be used as the return type of the function. If the function was going to return a record exactly like an existing table, the tablename itself could be specified as the return type. Since this is going to be a mixture of data from different tables, I needed to create one myself.
-- Much like defining a table without constraints CREATE TYPE foo_type AS ( unit_id integer, occupant_id integer, unit_name varchar(30), total_paid numeric(10, 2), /* etc... */ );
Because the report is property based, I figured I would create a function that takes the property id and date range and spits out the sums for all the units and occupants for that property and time period. Minor WHERE clauses to exclude unoccupied units and such won't be nearly as wasteful as it would have been had I made it a view. Notice how it returns "SETOF foo_type" rather than just "foo_type". Also note the use of RETURN NEXT for each record included in the results and the plain RETURN when there are no more records to return:
CREATE OR REPLACE FUNCTION foo_func(integer, date, date) RETURNS SETOF foo_type AS ' DECLARE prop_id_in ALIAS FOR $1; start_in ALIAS FOR $2; end_in ALIAS FOR $3; foo foo_type; occ RECORD; BEGIN FOR occ IN SELECT x.*, y.* FROM /* ... Big, complicated query with multiple left joins here ... */ WHERE x.property_id = prop_id_in AND y.start_date BETWEEN start_in AND end_in LOOP foo.unit_id := occ.unit_id; /* ... another query to fill in more bits of the foo type ... */ RETURN NEXT foo; END LOOP; RETURN; END; ' LANGUAGE plpgsql STRICT;
Now, when generating reports from my client application, I just SELECT the results of this function for each property that I want to report on:
// This Delphi example loops through a property-filled TCheckListBox... Query.SQL.Text := 'SELECT foo_func(:PropID, :StartDate, :EndDate)'; for i := 0 to Properties.Items.Count - 1 do begin if Properties.Checked[i] then begin Query.ParamByName('PropID').AsInteger := Integer(Properties.Items.Objects[i]); Query.ParamByName('StartDate').AsDateTime := StartDateEdit.DateTime; Query.ParamByName('EndDate').AsDateTime := EndDateEdit.DateTime; Query.Open; // Print report here... Query.Close; end; end;
I didn't arrive at the above solution on my first try. I had a few false starts and made some mistakes on the way. I detail these dead ends here in the hope that you might avoid them.
My first approach to the problem was a function that returned only one composite type value with the aggregates for the given unit & occupant:
-- Notice the lack of "SETOF": CREATE OR REPLACE FUNCTION foo_func(integer, date, date) RETURNS foo_type AS ' BEGIN ... END;
I figured I would just add the function call as part of the select list. It turns out you can't just use an asterisk (*) to refer to all the fields of the function return value at once:
-- This doesn't work! SELECT x.unit_id, y.occupant_id, foo_func(y.occupant_id).* FROM ...
It would let me reference the fields one at a time, but each selected field led to another invocation of the function! (Field count * Rows returned) function calls was definitely taking my efficiency in the wrong direction...
-- This results in 3 function calls for each record returned! SELECT x.unit_id, y.occupant_id, foo_func(y.occupant_id).total_paid, foo_func(y.occupant_id).deposits_paid, foo_func(y.occupant_id).movein_paid FROM ...
The function could be used as a table reference, but because I needed to include other bits of information as well as return more than one value at a time, it wasn't very handy:
-- Not terribly useful... SELECT * FROM foo_func(...);
Now, you can see how a function that returns a set of composite types was the right solution.