An attribute is a characteristic that can be used to further define a variable. There are two attributes provided to declare variables with the same structure as a database table or view. They are the %TYPE and %ROWTYPE attributes.
The %TYPE attribute specifies that the variable has the same structure as a database column. The advantage of using this attribute is that it does not need to know before hand the data type of the database object you are referencing. If the data type changes, then you do not need to change the function definition. For instance, the book table contains the isbn column. To reference that column, use the command:
As you can see, the dot(.) operator is used to refer to the column in the book table. The columns and tables must exist in the database for this to work. If the columns and tables do not exist, then it cannot build a column or table with the same structure. Again, the syntax to use the %TYPE attribute is:
|The %TYPE and %ROWTYPE attribute|
Note that declaring a variable with the structure of a column or table does not mean that the variable now holds the data values in the column or table. To put the same values in the column or table into the variable requires using a SELECT INTO statement.
For more information on the SELECT INTO statement refer to Chapter 4 .
The second attribute is the %ROWTYPE . It declares a row with the structure of the specified table. The table must be an existing table or view in the database. After you have declared a variable as a ROWTYPE , you can access the columns in that row by using the dot(.) notation. The syntax to use a %ROWTYPE attribute is:
This function uses a variable which has the same structure as a row in the customer table. First, it copies the row in the customer table which matches the customer number passed into it. Then it displays the corresponding customer name:
Example 9-19. The PL/pgSQL %ROWTYPE Variable
CREATE FUNCTION display_cust(integer) RETURNS text AS' DECLARE -- defines an alias name -- for the customer id number input cust_num ALIAS FOR $1; -- defines a row type to hold the information cust_info customer%ROWTYPE; BEGIN -- puts information into the newly declared rowtype SELECT INTO cust_info * FROM customer WHERE cust_id=cust_num; -- displays the customer lastname -- extracted from the rowtype return cust_info.lastname; END; ' LANGUAGE 'plpgsql';
If you pass customer id number 10 into this function, it will return the name Nathan :
select display_cust(10); display_cust -------------- Nathan (1 row)