PL/PGSQL is another language intended for server-side functions. It is a true programming language. While SQL functions allow only argument substitution, PL/PGSQL includes features such as variables, conditional evaluation, and looping.
PL/PGSQL is not installed in each database by default. To use it in database test, you must install it by running createlang plpgsql test from the operating system prompt.
Figure shows a PL/PGSQL version
of the SQL function getstatename from Figure .
test=> CREATE FUNCTION getstatename2(text)
test-> RETURNS text
test-> AS 'DECLARE ret TEXT;
test'> BEGIN
test'> SELECT INTO ret CAST(name AS TEXT)
test'> FROM statename
test'> WHERE code = $1;
test'> RETURN ret;
test'> END;'
test'> LANGUAGE 'plpgsql';
CREATE
It illustrates several PL/PGSQL features:
It accepts a text argument and returns the argument in uppercase, with a space between each character. The next SELECT uses this result to display a report heading. This function illustrates the use of variables and WHILE loops in PL/PGSQL.
Figure shows an even more complicated PL/PGSQL
function.
test=> CREATE FUNCTION getstatecode(text)
test-> RETURNS text
test-> AS 'DECLARE
test'> state_str statename.name%TYPE;
test'> statename_rec record;
test'> i integer;
test'> len integer;
test'> matches record;
test'> search_str text;
test'>
test'> BEGIN
test'> state_str := initcap($1); -- capitalization match column
test'> len := length(trim($1));
test'> i := 2;
test'>
test'> SELECT INTO statename_rec * -- first try for an exact match
test'> FROM statename
test'> WHERE name = state_str;
test'> IF FOUND
test'> THEN RETURN statename_rec.code;
test'> END IF;
test'>
test'> WHILE i <= len LOOP -- test 2,4,6,... chars for match
test'> search_str = trim(substr(state_str, 1, i)) || ''%'';
test'> SELECT INTO matches COUNT(*)
test'> FROM statename
test'> WHERE name LIKE search_str;
test'>
test'> IF matches.count = 0 -- no matches, failure
test'> THEN RETURN NULL;
test'> END IF;
test'> IF matches.count = 1 -- exactly one match, return it
test'> THEN
test'> SELECT INTO statename_rec *
test'> FROM statename
test'> WHERE name LIKE search_str;
test'> IF FOUND
test'> THEN RETURN statename_rec.code;
test'> END IF;
test'> END IF;
test'> i := i + 2; -- >1 match, try 2 more chars
test'> END LOOP;
test'> RETURN '''' ;
test'> END;'
test-> LANGUAGE 'plpgsql';
It takes a state name as a parameter and finds the proper state code. Because
state names are longer than state codes, they are often misspelled. This function
deals with misspellings by performing lookups in several ways. First, it attempts
to find an exact match. If that attempt fails, it searches for a unique state
name that matches the first 2, 4, or 6 characters, up to the length of the supplied
string. If a unique state is not found, the function returns an empty string
(''). Figure shows several getstatecode()
function calls.
test=> SELECT getstatecode('Alabama');
getstatecode
--------------
AL
(1 row)
test=> SELECT getstatecode('ALAB');
getstatecode
--------------
AL
(1 row)
test=> SELECT getstatecode('Al');
getstatecode
--------------
AL
(1 row)
test=> SELECT getstatecode('Ail');
getstatecode
--------------
(1 row)
The getstatecode() function illustrates three new PL/PGSQL features:
Figure shows a PL/PGSQL function
that provides a server-side interface for maintaining the statename table.
test=> CREATE FUNCTION change_statename(char(2), char(30))
test-> RETURNS boolean
test-> AS 'DECLARE
test'> state_code ALIAS FOR $1;
test'> state_name ALIAS FOR $2;
test'> statename_rec RECORD;
test'>
test'> BEGIN
test'> IF length(state_code) = 0 -- no state code, failure
test'> THEN RETURN ''f'';
test'> ELSE
test'> IF length(state_name) != 0 -- is INSERT or UPDATE?
test'> THEN
test'> SELECT INTO statename_rec *
test'> FROM statename
test'> WHERE code = state_code;
test'> IF NOT FOUND -- is state not in table?
test'> THEN INSERT INTO statename
test'> VALUES (state_code, state_name);
test'> ELSE UPDATE statename
test'> SET name = state_name
test'> WHERE code = state_code;
test'> END IF;
test'> RETURN ''t'';
test'> ELSE -- is DELETE
test'> SELECT INTO statename_rec *
test'> FROM statename
test'> WHERE code = state_code;
test'> IF FOUND
test'> THEN DELETE FROM statename
test'> WHERE code = state_code;
test'> RETURN ''t'';
test'> ELSE RETURN ''f'';
test'> END IF;
test'> END IF;
test'> END IF;
test'> END;'
test-> LANGUAGE 'plpgsql';
The function change_statename performs INSERT, UPDATE, and
DELETE operations on the statename table. The function is called
with a state code and state name. If the state code is not in the table, it
is inserted. If it already exists, the state name is updated. If the function
is called with an empty state name (''), the state is deleted from the table.
The function returns true ('t') if statename was
changed, and false ('f') if statename was unmodified. Figure
shows examples of its use.
test=> DELETE FROM statename;
DELETE 1
test=> SELECT change_statename('AL','Alabama');
change_statename
------------------
t
(1 row)
test=> SELECT * FROM statename;
code | name
------+--------------------------------
AL | Alabama
(1 row)
test=> SELECT change_statename('AL','Bermuda');
change_statename
------------------
t
(1 row)
test=> SELECT * FROM statename;
code | name
------+--------------------------------
AL | Bermuda
(1 row)
test=> SELECT change_statename('AL','');
change_statename
------------------
t
(1 row)
test=> SELECT change_statename('AL',''); -- row was already deleted
change_statename
------------------
f
(1 row)