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
Answertopia.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

Argument Variables

PL/pgSQL functions can accept argument variables of different types. Function arguments allow you to pass information from the user into the function that the function may require. Arguments greatly extend the possible uses of PL/pgSQL functions. User input generally provides a function with the data it will either operate on or use for operation. Users pass arguments to functions when the function is called by including them within parentheses, separated by commas.

Arguments must follow the argument list defined when the function is first created. Example 11-21 shows a pair of example function calls from psql .

Example 11-21. Function call examples

booktown=# 
SELECT get_author('John');

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

booktown=# 
SELECT get_author(1111);

  get_author
--------------
 Ariel Denham
(1 row)

Note: The get_author(text) and get_author(integer) functions are discussed later in this chapter.

Each function argument that is received by a function is incrementally assigned to an identifier that begins with the dollar sign ($) and is labeled with the argument number. The identifier $1 is used for the first argument, $2 is used for the second argument, and so forth. The maximum number of function arguments that can be processed is sixteen, so the argument identifiers can range from $1 to $16. Example 11-22 shows a function that doubles an integer argument variable that is passed to it.

Example 11-22. Directly using argument variables

CREATE FUNCTION double_price (float) RETURNS float AS '
  DECLARE
  BEGIN
     
     -- Return the argument variable multiplied by two.
    return $1 * 2;
  
  END;
' LANGUAGE 'plpgsql';

Referencing arguments with the dollar sign and the argument's order number can become confusing in functions that accept a large number of arguments. To help in functions where the ability to better distinguish argument variables from one another is needed (or just when you wish to use a more meaningful name for an argument variable), PL/pgSQL allows you to create variable aliases .

Aliases are created with the ALIAS keyword and give you the ability to designate an alternate identifier to use when referencing argument variables. All aliases must be declared in the declaration section of a block before they can be used (just like normal variables). Example 11-23 shows the syntax of the ALIAS keyword.

Example 11-23. Syntax of the ALIAS keyword

CREATE FUNCTION 
function_identifier
 (
arguments
) RETURNS 
type
 AS '
  DECLARE
    
identifier
 ALIAS FOR $1;
    
identifier
 ALIAS FOR $2;
  BEGIN
    [...]
  END;
' LANGUAGE 'plpgsql';

Example 11-24 creates a simple function to demonstrate the use of aliases in a PL/pgSQL function. The triple_ price() function accepts a floating point number as the price and returns that number multiplied by three.

Example 11-24. Using PL/pgSQL aliases

CREATE FUNCTION triple_price (float) RETURNS float AS '
  DECLARE
     
     -- Declare input_price as an alias for the argument variable
     -- normally referenced with the $1 identifier.
    input_price ALIAS FOR $1;
  
  BEGIN
     
     -- Return the input price multiplied by three.
    
    RETURN input_price * 3;
  
  END;
 ' LANGUAGE 'plpgsql';

Now, if we use the triple_ price function within a SQL SELECT statement in a client such as psql , we receive the results shown in Example 11-25.

Example 11-25. Result of the triple_price( ) function

booktown=# 
SELECT double_price(12.50);

 double_price
--------------
           25
(1 row)
Databases - Practical PostgreSQL
Previous Page Home Next Page

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