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 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 * from GetNames() where id > 2; id | name ----+------- 3 | Peter (1 row)
In addition to the table types, we can also return generic types from an SRF. We can change our example a little to demonstrate this. Let's define a new return type and a new function:
CREATE TYPE nametype AS (id int, name varchar);
CREATE FUNCTION PlpgGetNames() RETURNS SETOF nametype AS
$$
DECLARE
r nametype%rowtype;
BEGIN
FOR r IN SELECT id, name FROM names LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END ;
$$
LANGUAGE 'plpgsql';
The PlpgGetNames() function declares a variable r to be of rowtype nametype. This variable is used to store the rows queried in the loop. The function does a loop over the names table and sets r to each row in the result set. The RETURN NEXT command means that an output row is queued into the return set of the function. This does not cause the function to return. Finally, the RETURN statement after the loop returns all the rows, which were queued earlier using RETURN NEXT.
Let's run our new function, as shown here:
postgres=# SELECT PlpgGetNames(); plpggetnames -------------- (1,John) (2,Martin) (3,Peter) (3 rows)
For the sake of a second example, we will assume that you are in the middle of a big software development upgrade procedure that uses a name/value pair table structure to store settings. You have been asked to change the table structure from the key and value columns to a series of columns, where the column name is now the name of the key. This is similar to the pivot tables in Excel. By the way, you also need to preserve the settings for every version of the software you have ever deployed.
If you take a look at the existing CREATE TABLE statement for the table you have to work with, you will find the following:
CREATE TABLE application_settings_old ( version varchar(200), key varchar(200), value varchar(2000));
When you run a SELECT statement against the table, you will find out that there aren't many settings, but there have been quite a few versions of the settings. So, let's make a new table that is a little more explicit:
CREATE TABLE application_settings_new ( version varchar(200), full_name varchar(2000), description varchar(2000), print_certificate varchar(2000), show_advertisements varchar(2000), show_splash_screen varchar(2000));
Transforming the settings data into this new format can be accomplished with an INSERT statement and a function that conveniently returns our data in the new table format.
Let's add some test data, as follows:
INSERT INTO application_settings_old VALUES('3456','full_name','test_name');
INSERT INTO application_settings_old VALUES('3456','description','test_description');
INSERT INTO application_settings_old VALUES('3456','print_certificate','yes');
INSERT INTO application_settings_old VALUES('3456','show_advertisements','yes');
INSERT INTO application_settings_old VALUES('3456','show_splash_screen','no');
Let's go ahead and define the function:
CREATE OR REPLACE FUNCTION
flatten_application_settings(app_version varchar(200))
RETURNS setof application_settings_new
AS $$
BEGIN
-- Create a temporary table to hold a single row of data
IF EXISTS (SELECT relname FROM pg_class WHERE relname='tmp_settings')
THEN
TRUNCATE TABLE tmp_settings;
ELSE
CREATE TEMP TABLE tmp_settings (LIKE application_settings_new);
END IF;
--the row will contain all of the data for this version
INSERT INTO tmp_settings (version) VALUES (app_version);
-- add the details to the record for this application version
UPDATE tmp_settings
SET full_name = (SELECT value
FROM application_settings_old
WHERE version = app_version
AND key='full_name'),
description = (SELECT value
FROM application_settings_old
WHERE version = app_version
AND key='description'),
print_certificate = (SELECT value
FROM application_settings_old
WHERE version = app_version
AND key='print_certificate'),
show_advertisements = (SELECT value
FROM application_settings_old
WHERE version = app_version
AND key='show_advertisements'),
show_splash_screen = (SELECT value
FROM application_settings_old
WHERE version = app_version
AND key='show_splash_screen');
--hand back the results to the caller
RETURN QUERY SELECT * FROM tmp_settings;
END;
$$ LANGUAGE plpgsql;
The preceding function returns a single row of data to the calling query. The row contains all the settings that were previously defined as key/value pairs, but are explicitly defined fields now. The function and the final table can also be enhanced to transform the data types of the settings to something more explicit. However, I'll leave that one up to you.
We then proceed to use the function in order to do the transformation:
INSERT INTO application_settings_new SELECT ( flatten_application_settings(version)).* FROM ( SELECT version FROM application_settings_old GROUP BY version) AS ver;
Voilá! The data is now available in a tabular form in the new table structure.