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

Get PostgreSQL Server Programming - Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.