PostgreSQL Server Programming - Second Edition

Returning a record

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 ...