PL/PGSQL Functions

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:

DECLARE

Defines variables used in the function. 

SELECT INTO

A special form of SELECT that allows query results to be placed into variables. It should not be confused with SELECT * INTO.

RETURN

Exits and returns a value from the function.

Figure  shows a more complicated PL/PGSQL function.  

        test=> CREATE FUNCTION spread(text)
        test-> RETURNS text
        test-> AS 'DECLARE
        test'>         str text;
        test'>         ret text;
        test'>         i   integer;
        test'>         len integer;
        test'>
        test'>     BEGIN
        test'>         str := upper($1);
        test'>         ret := '''';           -- start with zero length
        test'>         i   := 1;
        test'>         len := length(str);
        test'>         WHILE i <= len LOOP  
        test'>             ret := ret || substr(str, i, 1) || '' '';
        test'>             i := i + 1;
        test'>         END LOOP;  
        test'>         RETURN ret;
        test'>     END;'
        test-> LANGUAGE 'plpgsql';
        CREATE
        test=> SELECT spread('Major Financial Report');
                            spread                    
        ----------------------------------------------
         M A J O R   F I N A N C I A L   R E P O R T 
        (1 row)  

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:

%TYPE

Data type that matches a database column.

RECORD

Data type that stores the result of a SELECT.

FOUND

A predefined BOOLEAN variable that represents the status of the previous SELECT INTO.

Many other PL/PGSQL features are covered in the User's Manual mentioned in Appendix .

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)