So far, all of our function examples have featured a simple scalar value in the RETURN clause. In PL/pgSQL, you can also define set-returning functions (SRF). These functions can return either a type defined by an existing table or a generic record type. Let's take a look at a simple example:

CREATE TABLE  names(id serial, name varchar);
INSERT  INTO names(name) VALUES('John');
INSERT  INTO names(name) VALUES('Martin');
INSERT  INTO names(name) VALUES('Peter');

CREATE OR REPLACE FUNCTION GetNames() RETURNS SETOF names AS 'SELECT * FROM names;' LANGUAGE 'sql';

We just defined a very simple function, GetNames(), which will simply return all the rows from our newly defined names table.

If you run the GetNames() function now, you will get the following output:

postgres=# select GetNames();
  getnames  
------------
 (1,John)
 (2,Martin)
 (3,Peter)
(3 rows)

You can use an SRF in place of a table or as a subquery in the FROM clause of a query. Here's an example:

postgres=# select...