A record is a special data type that holds one database row from the SELECT statement. Records are similar to row types. The only difference between row types and records is that there is no predefined structure for records. Records can also be used in select statements and FOR loops. To declare a record, use the following:
An assignment can be expressed by using:
SELECT INTO target expressions FROM ...;
The possible values for target are:
a row variable
a comma separated list of variables
PL/pgSQL's interpretation of target is the opposite of PostgreSQL's definition. PostgreSQL views the target as a newly created table. While PL/pgSQL allows any of the items previously listed as the target value. To create a table from a SELECT result, use the CREATE TABLE AS SELECT command.
The following example declares a RECORD , full_name variable, and an alias name for the input. Then it assigns the result of a select statement to the RECORD variable. It uses the dot notation to access the fields in the RECORD variable and assigns the concatenation of the first and last names to the full_name variable. Then it displays the full_name :
Example 9-26. Using a RECORD in PL/pgSQL
CREATE FUNCTION disp_customer10() RETURNS text AS' DECLARE -- defines a record and text variable -- to hold the customer record and concatenated name cust_rec RECORD; full_name TEXT; BEGIN -- searches for customer id equal to 10 -- and then assigns the row into the cust_rec SELECT INTO cust_rec * FROM customer WHERE cust_id = 10; -- concatenates the first and last name -- of the cust_rec record full_name := cust_rec.firstname || '' '' || cust_rec.lastname; -- displays the customer full name return full_name; END; ' LANGUAGE 'plpgsql';
The result of the disp_customer10() function is:
customer ---------------- Dr.John Nathan (1 row)
There will always be one result for this function because it is coded to display only customer 10. For a more versatile function definition, assign a user supplied input number to the SELECT query. See the disp_customer() function for more details.
To check if the assignment for the SELECT INTO statement successfully occurred, use the FOUND command. FOUND is a boolean variable that is used immediately after a SELECT INTO has been executed. Using our previous disp_customer10() example , we will modify it check if the SELECT INTO statement successfully found a row matching the customer id.
Example 9-27. The FOUND keyword in PL/pgSQL
CREATE FUNCTION disp_customer (integer) RETURNS text AS ' DECLARE -- defines a record and text variable -- to hold the customer record and name cust_rec RECORD; full_name TEXT; -- declaring an alias name for the input cust ALIAS FOR $1; BEGIN -- searches for the customer id supplied by the input -- and then assigns the row into the cust_rec record SELECT INTO cust_rec * FROM customer WHERE cust_id = cust; -- if a customer was found with a matching customer id IF FOUND THEN -- assigns the last and first name found -- into the full name variable full_name := cust_rec.firstname || '' '' || cust_rec.lastname; -- displays the full_name return full_name; ELSE -- displays an error message -- if the customer id was not found RAISE EXCEPTION ''The customer id % does not exist!'', cust; END IF; END; ' LANGUAGE 'plpgsql';
Notice that this assignment statement was moved beneath the IF FOUND condition:
full_name := cust_rec.firstname || '' '' || cust_rec.lastname;If the assignment statement was left outside of this condition, then a run-time error message is generated when a supplied customer id does not exist. The message says something to this effect:
ERROR: record cust_rec is unassigned yet
This error is generated because it could not find the customer row which matches the customer id number. Therefore, it cannot assign a first or last name to the full_name variable. So it generates this error message.
By moving the assignment statement beneath the IF FOUND condition, we are telling it to only assign the first and last name of the customer row when it finds a row that matches the customer id number. If not, the full_name variable remains unassigned and our error message is displayed.
Using the existing customer id number 10, we tested this function and the result is:
disp_customer ---------------- Dr.John Nathan (1 row)
When customer id number 11 is supplied to this function, the resulting output is:
ERROR: The customer id 11 does not exist!This error message is displayed because a customer 11 doesn't exist. That means our function was successful in testing the condition when a row is not FOUND .
Another method to check for the results of a SELECT INTO is by using the IS NULL condition. The IF tests for a RECORD/ROW that is NULL or is NOT NULL . If there is more than one NULL row, it will only move the first row into the target field. The remaining rows are discarded. Using the publish table, let's check for NULL sugg_price columns returned by the SELECT INTO statement. This statement searches for the supplied isbn number and assigns the resulting row into the publish_rec record. The function is defined as:
Example 9-28. Using IS NULL in PL/pgSQL
CREATE FUNCTION check_price(integer) RETURNS text AS' DECLARE -- defines a record and alias name -- to hold the publish record and isbn publish_rec RECORD; user_isbn ALIAS FOR $1; BEGIN -- searches for the isbn supplied by the input -- and then assigns the matching row into -- the publish_rec record SELECT INTO publish_rec * FROM publish WHERE isbn=user_isbn; -- checks if the sugg_price column contains any data IF publish_rec.sugg_price IS NULL THEN -- if it does not contain data, -- then this message is displayed RAISE EXCEPTION ''There is no suggested price for the isbn number %.'', user_isbn; ELSE -- if it contains data, then display the sugg_price return publish_rec.sugg_price; END IF; END; ' LANGUAGE 'plpgsql';
The publish table contains these data values:
isbn | publ_name | sugg_price ---------+------------------+------------ 2567841 | ODG Books | $12.99 8741205 | Children's Books | $19.99 602071 | ABC Books | $4.55 676883 | Reading Rainbow | $13.29 355184 | ABC Books | $16.85 214872 | Young Readers | $6.00 321092 | Quail Press | (7 rows)
If an isbn number of 214872 is supplied, then this price is displayed:
check_price ----------- $6.00 (1 row)
On the other hand, if the isbn number of 321092 is supplied, then this error message is displayed:
ERROR: There is no suggested price for the isbn number 321092.