This module contains the built-in functions for SQL::Parser and SQL::Statement. All of the functions are also available in any DBDs that subclass those modules (e.g. DBD::CSV, DBD::DBM, DBD::File, DBD::AnyData, DBD::Excel, etc.).

This documentation covers built-in functions and also explains how to create your own functions to supplement the built-in ones. It's easy. If you create one that is generally useful, see below for how to submit it to become a built-in function.

When using SQL::Statement/SQL::Parser directly to parse SQL, functions (either built-in or user-defined) may occur anywhere in a SQL statement that values, column names, table names, or predicates may occur. When using the modules through a DBD or in any other context in which the SQL is both parsed and executed, functions can occur in the same places except that they can not occur in the column selection clause of a SELECT statement that contains a FROM clause.

# valid for both parsing and executing
SELECT MyFunc(args);
SELECT * FROM MyFunc(args);
SELECT * FROM x WHERE MyFuncs(args);
SELECT * FROM x WHERE y < MyFuncs(args);
# valid only for parsing (won't work from a DBD)
SELECT MyFunc(args) FROM x WHERE y;

In addition to the built-in functions, you can create any number of your own user-defined functions (UDFs). In order to use a UDF in a script, you first have to create a perl subroutine (see below), then you need to make the function available to your database handle with the CREATE FUNCTION or LOAD commands:

# load a single function "foo" from a subroutine
# named "foo" in the current package
$dbh->do(" CREATE FUNCTION foo EXTERNAL ");
# load a single function "foo" from a subroutine
# named "bar" in the current package
$dbh->do(" CREATE FUNCTION foo EXTERNAL NAME bar");
# load a single function "foo" from a subroutine named "foo"
# in another package
$dbh->do(' CREATE FUNCTION foo EXTERNAL NAME "Bar::Baz::foo" ');
# load all the functions in another package
$dbh->do(' LOAD "Bar::Baz" ');

Functions themselves should follow SQL identifier naming rules. Subroutines loaded with CREATE FUNCTION can have any valid perl subroutine name. Subroutines loaded with LOAD must start with SQL_FUNCTION_ and then the actual function name. For example:

User-defined functions (UDFs) are perl subroutines that return values appropriate to the context of the function in a SQL statement. For example the built-in CURRENT_TIME returns a string value and therefore may be used anywhere in a SQL statement that a string value can. Here' the entire perl code for the function:

More complex functions can make use of a number of arguments always passed to functions automatically. Functions always receive these values in @_:

sub FOO {
my($self,$sth,@params);
}

The first argument, $self, is whatever class the function is defined in, not generally useful unless you have an entire module to support the function.

The second argument, $sth is the active statement handle of the current statement. Like all active statement handles it contains the current database handle in the {Database} attribute so you can have access to the database handle in any function:

In actual practice you probably want to use $sth->{Database} directly rather than making a local copy, so $sth->{Database}->do(...).

The remaining arguments, @params, are arguments passed by users to the function, either directly or with placeholders; another silly example which just returns the results of multiplying the arguments passed to it:

A function can return almost anything, as long is it is an appropriate return for the context the function will be used in. In the special case of table-returning functions, the function should return a reference to an array of array references with the first row being the column names and the remaining rows the data. For example:

### SQL-92 / ODBC Functions
# CONVERT / CAST - Complex to implement, but a draft is in the works.
# DIFFERENCE - Function is not clearly defined in spec and has very limited applications
# EXTRACT - Contains a FROM keyword and requires rather freeform datetime/interval expression
### ODBC 3.0 Time/Date Functions only
# DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE, MONTH, MONTHNAME, QUARTER, SECOND, TIMESTAMPDIFF,
# WEEK, YEAR - Requires freeform datetime/interval expressions. In a later release, these could
# be implemented with the help of Date::Parse.

ODBC 3.0 functions that are implemented with differences include:

# SOUNDEX - Returns true/false, instead of a SOUNDEX code
# RAND - Seed value is a second parameter with a new first parameter for max limit
# LOG - Returns base X (or 10) log of number, not natural log. LN is used for natural log, and
# LOG10 is still available for standards compatibility.
# POSITION - Does not use 'IN' keyword; cannot be fixed as previous versions of SQL::Statement defined
# the function as such.
# REPLACE / SUBSTITUTE - Uses a regular expression string for the second parameter, replacing the last two
# parameters of the typical ODBC function

# purpose : starting position (one-based) of the first occurrence of STR1
within STR2; 0 if it doesn't occur and NULL for any NULL args
# arguments : STR1, STR2, and an optional S (starting position to search)

Removes all occurrences of <trim_char> from the front, back, or both sides of a string.

BOTH is the default if neither LEADING nor TRAILING is specified.
Space is the default if no trim_char is specified.
Examples:
TRIM( string )
trims leading and trailing spaces from string
TRIM( LEADING FROM str )
trims leading spaces from string
TRIM( 'x' FROM str )
trims leading and trailing x's from string

If you make a generally useful UDF, why not submit it to me and have it (and your name) included with the built-in functions? Please follow the format shown in the module including a description of the arguments and return values for the function as well as an example. Send them to the dbi-dev@perl.org mailing list (see http://dbi.perl.org).