The %TYPE attribute

The %TYPE attribute is used to declare a variable with the data type of a referenced database object (most commonly a table column). The format for declaring a variable in this manner is shown in Example 11-27.

variable_name
 
table_name
.
column_name
%TYPE

Example 11-28 shows the code for a function that uses %TYPE to store the last name of an author. This function uses string concatenation with the concatenation operator (||), which is documented in a later section. The use of the SELECT INTO statement was discussed earlier in this chapter.

Focus on the use of the %TYPE attribute in Example 11-28. Essentially, a variable is declared as being the same type as a column within the authors table. SELECT is then used to find a row with a first_name field that matches the name the user passed to the function. The SELECT statement retrieves the value of that row's last_name column and insert it into the l_name variable. An example of the user's input to the function is shown right after Example 11-28, in Example 11-29, and more examples of user input can be found later in this chapter.

CREATE FUNCTION get_author (text) RETURNS text AS '
  DECLARE
      
      -- Declare an alias for the function argument,
      -- which should be the first name of an author.
     f_name ALIAS FOR $1;
       
       -- Declare a variable with the same type as
       -- the last_name field of the authors table.
     l_name authors.last_name%TYPE;
  
  BEGIN
      
      -- Retrieve the last name of an author from the
      -- authors table whose first name matches the
      -- argument received by the function, and
      -- insert it into the l_name variable.
     SELECT INTO l_name last_name FROM authors WHERE first_name = f_name;
       
       -- Return the first name and last name, separated
       -- by a space.
     return f_name || '' '' || l_name;
  
  END;
' LANGUAGE 'plpgsql';

Example 11-29 shows the results of using the get_author() function.

booktown=# 
SELECT get_author('Andrew');

   get_author
-----------------
 Andrew Brookins
(1 row)

The %ROWTYPE Attribute

%ROWTYPE is used to declare a PL/pgSQL record variable with the same structure as the rows in a table you specify. It is similar to the RECORD data type, but a variable declared with %ROWTYPE will have the exact structure of a table's row, whereas a RECORD variable is not structured and will accept a row from any table.

Example 11-30 overloads the get_author() function that was created in Example 11-28 to accomplish a similar goal. Notice, though, that this new version of get_author() accepts an argument of type integer rather than text, and checks for the author by comparing their id against the passed integer argument.

Notice also that this function is implemented using a variable declared with %ROWTYPE. The use of %ROWTYPE to accomplish a simple task such as this may make it seem overly complicated, but as you learn more about PL/pgSQL, the importance of %ROWTYPE will become more apparent.

The use of the dot (.) within the found_author variable in Example 11-30 references a named field value in found_author.

CREATE FUNCTION get_author (integer) RETURNS text AS '
  DECLARE
    
    -- Declare an alias for the function argument,
    -- which should be the id of the author.
    author_id ALIAS FOR $1;
    
    -- Declare a variable that uses the structure of
    -- the authors table.
    found_author authors%ROWTYPE;
  
  BEGIN
    
    -- Retrieve a row of author information for
    -- the author whose id number matches
    -- the argument received by the function.
    SELECT INTO found_author * FROM authors WHERE id = author_id;
    
    -- Return the first
    RETURN found_author.first_name || '' '' || found_author.last_name;
  
  END;
' LANGUAGE 'plpgsql';

Observe the use of the asterisk (*) for the column list in Example 11-30. Since found_author is declared with the %ROWTYPE attribute on the authors table, it is created with the same data structure as the authors table. The asterisk can therefore be used to populate the found_author variable with each column value selected from the SELECT INTO statement in Example 11-31.

booktown=# 
SELECT get_author(1212);

  get_author
--------------
 John Worsley
(1 row)