Returning a record

All of our function examples so far have featured a simple scalar value in the RETURN clause. For more complex return types, we have several choices. One option is to return a set of records conforming to a table definition. For the sake of this 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. By the way, you also need to preserve the settings for every version of the software you have ever deployed.

Looking at the existing CREATE TABLE statement for the table you have to work with, we find:

CREATE TABLE application_settings_old (
version varchar(200),
key varchar(200),
value varchar(2000));

When you run a select statement against the table, you find out that there are not very many settings, but there have been...