Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Attributes

PL/pgSQL provides variable attributes to assist you in working with database objects. These attributes are %TYPE and %ROWTYPE. Use attributes to declare a variable to match the type of a database object (using the %TYPE attribute) or to match the row structure of a row (with the %ROWTYPE attribute). A variable should be declared using an attribute when it will be used within the code block to hold values taken from a database object. Knowledge of the database object's type is not required when using attributes to declare variables. If an object's type changes in the future, your variable's type will automatically change to that data type without any extra code.

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.

Example 11-27. Declaring a variable using %TYPE


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.

Example 11-28. Using the %TYPE attribute

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.

Example 11-29. Results of 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.

Example 11-30. Using the %ROWTYPE attribute

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.

Example 11-31. Results of the new get_author( ) function

booktown=# 
SELECT get_author(1212);

  get_author
--------------
 John Worsley
(1 row)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire