Database Reference
In-Depth Information
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 re-
turn 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 proced-
ure 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 quite a few versions of them. So you 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
in-
sert
statement and a function that conveniently returns our data to us in the new
table format.
Let's go ahead and define the function:
