Functions enable you to access specialized routines from SQL. They take one or more arguments and return a result.
Suppose you want to uppercase a value or column. No command
will perform this operation, but a function can handle it. POSTGRESQL
has a function called upper that takes a single string argument and returns
the argument in uppercase. The function call upper(col) calls the function
upper with col as its argument and returns it in uppercase. Figure
shows an example of the use of the upper function.
test=> SELECT * FROM functest;
name
------
Judy
(1 row)
test=> SELECT upper(name) FROM functest;
upper
-------
JUDY
(1 row)
POSTGRESQL provides many functions. Table shows
the most common ones, organized by the data types supported.
If you call a function with a type for which it is not defined,
you will get an error message, as shown in the first query of Figure .
test=> SELECT date_part('year', '5/8/1971');
ERROR: Function 'date_part(unknown, unknown)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
test=> SELECT date_part('year', CAST('5/8/1971' AS DATE));
date_part
-----------
1971
(1 row)
In the first query, 5/8/1971 is a character string, not a date. The second query converts 5/8/1971 to a date, so date_part() can be used.