Using Functions
A function is an identifier which instructs PostgreSQL to perform a programmatic operation within a SQL statement, and then return a value from that operation. The returned value is substituted into the SQL statement in place of the typed function name, similarly to the way the results from operators are (in fact, technically, operators themselves are just syntactic pointers to built-in system functions).
To use a function, you need only pass the function name, followed by its list of arguments (if any are required) which are enclosed in parentheses, and delimited by commas. What specific arguments you need to pass will depend completely on the function, and its requirements, but the general format is the same.
function_name (argument1 , argument2 ...)
PostgreSQL defines a rich set of functions for its built-in data types. To view a complete list of functions available, you may type \df within psql.
Mathematical Functions
Table 4-14. Mathematical Functions in PostgreSQL
Function | Return Type | Description | ||
|---|---|---|---|---|
abs (x) | (same as argument type) | absolute value | ||
cbrt (double precision) | double precision | cube root | ||
ceil (numeric) | numeric | smallest integer not less than argument | ||
degrees (double precision) | double precision | convert radians to degrees | ||
exp (double precision) | double precision | exponential function | ||
floor (numeric) | numeric | largest integer not greater than argument | ||
ln (double precision) | double precision | natural logarithm | ||
log (double precision) | double precision | base 10 algorithm | ||
log (base numeric, x numeric) | numeric | logarithm to specified base | ||
mod (y, x) | (same as argument types) | remainder (modulo) of the division y/x | ||
pi () | double precision | "Pi" constant | ||
pow (double precision, double precision) | double precision | raise number to the specified exponent | ||
radians (double precision) | double precision | convert degrees to radians | ||
random () | double precision | a pseudo-random value between 0.0 to 1.0 | ||
round (double precision) | double precision | round to nearest integer | ||
round (value numeric, scale integer) | numeric | round to specified number of decimal places | ||
sqrt (double precision) | double precision | square root | ||
trunc (double precision) | double precision | truncate (toward zero) | ||
trunc (value numeric, scale integer) | numeric | truncate to specified number of decimal places |
Example 4-45. Using the random() and round() Functions
The following SQL statement generates a random number from 0.0 to 1.0 with the random() function, which is multiplied by an integer constant of 53, and rounded to the nearest integer.
booktown=# SELECT round(53 * random());
round
-------
36
(1 row)
booktown=# SELECT round(53 * random());
round
-------
11
(1 row)
As you can see in Example 4-45, the first time the statement is executed, the returned value is 36. The second time the statement is executed, it returns 11. As you might expect, the results of random() are totally unpredictable. round(), on the other hand, always rounds a numeric value to the nearest whole integer.
Table 4-15. Trigonometric Functions
Function | Description |
|---|---|
acos(double precision x) | Returns the inverse cosine of the passed double precision value x |
asin(double precision x) | Returns the inverse sine of the passed double precision value x |
atan(double precision x) | Returns the inverse tangent of the passed double precision value x |
atan2(double precision x,double precision y) | Returns the inverse tangent of the quotient of double precision values (y divided by x) |
cos(double precision x) | Returns the cosine of the double precision value x |
cot(double precision x) | Returns the cotangent of the double precision value x |
sin(double precision x) | Returns the sine of the double precision value x |
tan(double precision x) | Returns the tangent of the double precision value x |
Built-in Aggregate Functions
Table 4-16. Aggregate Functions
Function | Description | Support Type | Return Type |
|---|---|---|---|
AVG (expression) | the average of all input values (arithmetic mean) | small int, integer, bigint, real, double precision, numeric, interval. | numeric for integer type inputs and double precision for floating point input (For all other data types, the result is the same as the input data type) |
COUNT (*) | gives the sum of the number of input values | type integer | |
COUNT (expression) | gives a sum of the number of the input values for which the value of expression is not NULL | ||
MAX (expression) | the maximum value of (expression) across all input values | all numeric, string, and date/time types | same type as the input expression |
MIN (expression) | the minimum value of (expression) across all input values | all numeric, string, and date/time types | same type as the input expression |
STDDEV (expression) | the sample standard deviation of the input values | smallint, integer, bigint, real, double precision, numeric. | double precision for floating point inputs, otherwise, numeric |
SUM (expression) | the sum of (expression) across all input values | smallint, integer, bigint, real, double precision, numeric, and interval. | numeric for any integer type input, double precision for bloating point input. (For all other data types, the output is the same as the input data type.) |
VARIANCE (expression) | this is a sample of the variance from the input values | the same as the data types for standard deviation |
The functions shown in Table 4-16 can be used for a wide variety of needs. If you need to find the average of the suggested prices for all of the books, then you can use the AVG function.
Example 4-46. Using the average() Function
SELECT AVG(sugg_price)
FROM publish;
avg
--------
$12.61
(1 row)These are the psql commands that displays information about functions:
- \da
Lists all aggregate commands supported by PostgreSQL.
- \df
Lists all functions and their aggregates.
- \dd
Displays comments about a specific function or group of functions.
- \do
Lists all operators and their arguments.
Aggregate Expressions
An aggregate expression represents the application of an aggregate function across the rows selected by a query. An aggregate function takes in multiple inputs and returns a single output value, such as the sum or average of the inputs. The possible syntaxes for an aggregate function are as follows:
- aggregate_name (expression)
This function call invokes the aggregate across all input rows for which the given expression yields a non-NULL value. NOTE: An aggregate function may or may not ignore a NULL value, depending on its definition, but the standard aggregate functions are set to ignore NULLs.
- aggregate_name (ALL expression)
This function call is in essence the same as the first syntax because it specifies ALL as the default.
- aggregate_name (DISTINCT expression)
This invokes the aggregate function for all distinct (non-repeating) non-NULL values of the expression found in the input rows.
- aggregate_name (*)
Here, the aggregate is invoked once for each input row, regardless if the value is NULL or non-NULL because there is no input value specified. This is generally used for the count() function.
 | Aggregate Syntax |
|---|---|
Aggregate_name is a previously defined aggregate, and expression is any expression that does not contain an aggregate expression. |
Positional Parameters
A positional parameter reference is typically used in a SQL function definition statement to indicate a parameter in a SQL function. The syntax to specify a parameter is:
$##
Where ## acts as the index position of the parameter you are searching for. For example, the following function returns the title of a book when passed the isbn number.
Example 4-47. Creating a Function
booktown=# CREATE FUNCTION isbn_to_title(text) RETURNS text booktown-# AS 'SELECT title from books where isbn = $1' booktown-# LANGUAGE 'sql';
The $1 in the function definition is substituted with the first function argument when the function is called.
| Function List in psql |
|---|---|
Remember that to see a list of PostgreSQL's built-in functions, you may use the psql command \df. |
If you want to delete your function after having created it, use the DELETE FUNCTION command. This command takes in the function name and type. Like this:
DELETE FUNCTION funcName(type);
If the inventory function was not needed anymore, then we can delete it using the command: