Databases - Practical PostgreSQL

Databases - Practical PostgreSQL
Previous Page	Home	Next Page

Calling Functions

The normal syntax to call another PL/pgSQL function from within PL/pgSQL is to either reference the function in a SQL SELECT statement, or during the assignment of a variable. For example:

  SELECT 
function_identifier
(
arguments
);
  
variable_identifier
 := 
function_identifier
(
arguments
);

The use of assignments and SELECT statements to execute functions is standard in PL/pgSQL because all functions in a PostgreSQL database must return a value of some type. Use the PERFORM keyword to call a function and ignore its return data. Example 11-50 shows the syntax of the PERFORM keyword.

Example 11-50. Syntax of the PERFORM keyword

PERFORM 
function_identifier
(
arguments
);

Example 11-51 demonstrates the use of PERFORM to invoke a PL/pgSQL function, and shows how to call another PL/pgSQL function through assignment (via a SELECT INTO statement). The ship_item function is a useful wrapper to the add_shipment function. It accepts basic information, makes sure the customer and book both exist, and then sends the information to add_shipment.

Example 11-51. Using the PERFORM keyword

CREATE FUNCTION ship_item (text,text,text) RETURNS integer AS '
  DECLARE
     
     -- Declare function argument aliases.
    l_name ALIAS FOR $1;
    f_name ALIAS FOR $2;
    book_isbn ALIAS FOR $3;
     
     -- Declare a variable to hold the book ID number.  This variable
     -- is necessary to check for the existence of the provided ISBN.
    book_id INTEGER;
     
     -- Declare a variable to hold the customer ID number.  This variable
     -- is necessary to check for the existence of the customer.
    customer_id INTEGER;
  
  BEGIN
     
     -- Retrieve the customer ID number with a previously created
     -- function.
    SELECT INTO customer_id get_customer_id(l_name,f_name);
     
     -- If the customer does not exist, return -1 and exit.  The
     -- get_customer_id function returns a -1 if the customer is not found.
    IF customer_id = -1 THEN
      RETURN -1;
    END IF;
     
     -- Retrieve the ID number of the book with the specified ISBN.
    SELECT INTO book_id book_id FROM editions WHERE isbn = book_isbn;
     
     -- If the book does not exist in the system, return a -1.
    IF NOT FOUND THEN
      RETURN -1;
    END IF;
     
     -- If the book and customer both exist, add the shipment.
    PERFORM add_shipment(customer_id,book_isbn);
     
     -- Return 1 to indicate the function was successful.
    RETURN 1;
  END;
' LANGUAGE 'plpgsql';