Return more than one row of data from PL/pgSQL functions

Example: I create table as below:

create table department(id int primary key, name text);

create table employee(id int primary key, name text, salary int,

departmentid int references department);

insert into department values (1, 'Management');
insert into department values (2, 'IT');

insert into employee values (1, 'John Smith', 30000, 1);
insert into employee values (2, 'Jane Doe', 50000, 1);
insert into employee values (3, 'Jack Jackson', 60000, 2);

1/. Use Function Return: 

create function GetEmployees() returns setof employee as 

'select * from employee;' language 'sql'; 

2/. Query Data from Return Function:

select * from GetEmployees() where id > 2;

This is great, but what if you wanted to return something more complicated, for example, a list of departments and the total salary of all employees in that department. If you want to return an existing record type, you need to make a dummy type to hold the output type, for example:
create type holder as (departmentid int, totalsalary int8); Here we are defining a new type named holder which is a composite type of an integer named departmentid and a bigint named totalsalary. We can then define functions that return sets of this type. For this function we'll write a version in SQL and then a version in PL/pgSQL:

create function SqlDepartmentSalaries() returns setof holder as ' select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid ' language 'sql'; create or replace function PLpgSQLDepartmentSalaries() returns setof holder as ' declare r holder%rowtype; begin for r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid loop return next r; end loop; return; end ' language 'plpgsql'; 

 The SQL is very similar to the GetEmployee() function above. It returns a rowset of rows defined by the type holder (int, int8). The rows that it returns are defined by the group by query in its body.
The PL/pgSQL function is a little more complicated, but let's go through it. The function starts off by declaring a variable r to be of the rowtype holder. This variable will be used to store the rows coming from the query in the main body of the function. The main body does a loop over the group by query stated setting r to each row in sequence. The body of the loop is the new return form, 'return next' which means that an output row is queued into the return set of the function. This does not cause the function to return. Currently, SRF returning PL/pgSQL functions must generate the entire result set before returning although if the set becomes large it will be written to disk. This limitation may be removed in a future version.
These functions are used in the same fashion as the first function,

select * from PLpgSQLDepartmentSalaries(); 

 A PL/pgSQL function can also do additional operations on the records or only queue up only some records. For example, if you wanted to get an idea of operating expenses for departments where the overhead is 75% for departments whose total salaries were greater than 70,000 and 50% otherwise and only wanted to return department ids for departments whose salaries plus overhead was greater than 100,000 you might write something like:

create or replace function ExpensiveDepartments() returns setof int as ' declare r holder%rowtype; begin for r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid loop if (r.totalsalary > 70000) then r.totalsalary := CAST(r.totalsalary * 1.75 as int8); else r.totalsalary := CAST(r.totalsalary * 1.5 as int8); end if; if (r.totalsalary > 100000) then return next r.departmentid; end if; end loop; return; end ' language 'plpgsql';