Built-in & user-defined sql functions
SELECT Func(args); SELECT * FROM Func(args); SELECT * FROM x WHERE Funcs(args); SELECT * FROM x WHERE y < Funcs(args);
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. \s-1DBD::CSV\s0, \s-1DBD::DBM\s0, 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 \s-1SQL\s0, functions (either built-in or user-defined) may occur anywhere in a \s-1SQL\s0 statement that values, column names, table names, or predicates may occur. When using the modules through a \s-1DBD\s0 or in any other context in which the \s-1SQL\s0 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 \s-1SELECT\s0 statement that contains a \s-1FROM\s0 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 \s-1UDF\s0 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 \s-1CREATE\s0 \s-1FUNCTION\s0 or \s-1LOAD\s0 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 \s-1SQL\s0 identifier naming rules. Subroutines loaded with \s-1CREATE\s0 \s-1FUNCTION\s0 can have any valid perl subroutine name. Subroutines loaded with \s-1LOAD\s0 must start with \s-1SQL_FUNCTION_\s0 and then the actual function name. For example:
package Qux::Quimble; sub SQL_FUNCTION_FOO { ... } sub SQL_FUNCTION_BAR { ... } sub some_other_perl_subroutine_not_a_function { ... } 1;
# in another package $dbh->do("LOAD Qux::Quimble");
# This loads FOO and BAR as SQL functions.
User-defined functions (UDFs) are perl subroutines that return values appropriate to the context of the function in a \s-1SQL\s0 statement. For example the built-in \s-1CURRENT_TIME\s0 returns a string value and therefore may be used anywhere in a \s-1SQL\s0 statement that a string value can. Here' the entire perl code for the function:
# CURRENT_TIME # # arguments : none # returns : string containing current time as hh::mm::ss # sub SQL_FUNCTION_CURRENT_TIME { sprintf "%02s::%02s::%02s",(localtime)[2,1,0] }
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:
sub FOO { my($self,$sth,@params); my $dbh = $sth->{Database}; # $dbh->do( ...), etc. }
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:
sub MULTIPLY { my($self,$sth,@params); return $params[0] * $params[1]; }
# first make the function available # $dbh->do("CREATE FUNCTION MULTIPLY");
# then multiply col3 in each row times seven # my $sth=$dbh->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,7)"); $sth->execute; # # or # my $sth=$dbh->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,?)"); $sth->execute(7);
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:
1. create a function that returns an AoA,
sub Japh {[ [qw( id word )], [qw( 1 Hacker )], [qw( 2 Perl )], [qw( 3 Another )], [qw( 4 Just )], ]}
2. make your database handle aware of the function
$dbh->do("CREATE FUNCTION 'Japh');
3. Access the data in the AoA from \s-1SQL\s0
$sth = $dbh->prepare("SELECT word FROM Japh ORDER BY id DESC");
Or here's an example that does a join on two in-memory tables:
sub Prof {[ [qw(pid pname)],[qw(1 Sue )],[qw(2 Bob)],[qw(3 Tom )] ]} sub Class {[ [qw(pid cname)],[qw(1 Chem)],[qw(2 Bio)],[qw(2 Math)] ]} $dbh->do("CREATE FUNCTION $_) for qw(Prof Class); $sth = $dbh->prepare("SELECT * FROM Prof NATURAL JOIN Class");
The \*(L"Prof\*(R" and \*(L"Class\*(R" functions return tables which can be used like any \s-1SQL\s0 table.
More complex functions might do something like scrape an \s-1RSS\s0 feed, or search a file system and put the results in AoA. For example, to search a directory with \s-1SQL:\s0
sub Dir { my($self,$sth,$dir)=@_; opendir D, $dir or die "'$dir':$!"; my @files = readdir D; my $data = [[qw(fileName fileExt)]]; for (@files) { my($fn,$ext) = /^(.*)(\.[^\.]+)$/; push @$data, [$fn,$ext]; } return $data; } $dbh->do("CREATE FUNCTION Dir"); printf "%s\n", join' ',@{ $dbh->selectcol_arrayref(" SELECT fileName FROM Dir('./') WHERE fileExt = '.pl' ")};
Obviously, that function could be expanded with File::Find and/or stat to provide more information and it could be made to accept a list of directories rather than a single directory.
Table-Returning functions are a way to turn *anything* that can be modeled as an AoA into a \s-1DBI\s0 data source.
All \s-1ODBC\s0 3.0 functions are available except for the following:
### 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.
\s-1ODBC\s0 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
\s-1MIN\s0, \s-1MAX\s0, \s-1AVG\s0, \s-1SUM\s0, \s-1COUNT\s0
Aggregate functions are handled elsewhere, see SQL::Parser for documentation.
These functions can be used without parentheses.
\s-1CURRENT_DATE\s0 aka \s-1CURDATE\s0
# purpose : find current date # arguments : none # returns : string containing current date as yyyy-mm-dd
\s-1CURRENT_TIME\s0 aka \s-1CURTIME\s0
# purpose : find current time # arguments : optional seconds precision # returns : string containing current time as hh:mm:ss (or ss.sss...)
\s-1CURRENT_TIMESTAMP\s0 aka \s-1NOW\s0
# purpose : find current date and time # arguments : optional seconds precision # returns : string containing current timestamp as yyyy-mm-dd hh:mm:ss (or ss.sss...)
\s-1UNIX_TIMESTAMP\s0
# purpose : find the current time in UNIX epoch format # arguments : optional seconds precision (unlike the MySQL version) # returns : a (64-bit) number, possibly with decimals
\s-1ASCII\s0 & \s-1CHAR\s0
# purpose : same as ord and chr, respectively (NULL for any NULL args) # arguments : string or character (or number for CHAR); CHAR can have any amount of numbers for a string
\s-1BIT_LENGTH\s0
# purpose : length of the string in bits # arguments : string
\s-1CHARACTER_LENGTH\s0 aka \s-1CHAR_LENGTH\s0
# purpose : find length in characters of a string # arguments : a string # returns : a number - the length of the string in characters
\s-1COALESCE\s0 aka \s-1NVL\s0 aka \s-1IFNULL\s0
# purpose : return the first non-NULL value from a list # arguments : 1 or more expressions # returns : the first expression (reading left to right) # which is not NULL; returns NULL if all are NULL #
\s-1CONCAT\s0
# purpose : concatenate 1 or more strings into a single string; # an alternative to the '||' operator # arguments : 1 or more strings # returns : the concatenated string # # example : SELECT CONCAT(first_string, 'this string', ' that string') # returns "<value-of-first-string>this string that string" # note : if any argument evaluates to NULL, the returned value is NULL
\s-1CONV\s0
# purpose : convert a number X from base Y to base Z (from base 2 to 92) # arguments : X (can by a number or string depending on the base), Y, Z (Z defaults to 10) # returns : either a string or number, in base Z # notes : # * Supports negative and decimal numbers # * Will use big numbers if it has to, so accuracy is at near absolute levels # * Letters are case-sensitive after base 36 # * Base character sets are: (second set is for compatibility with base 64) # 2 to 62 = 0-9, A-Z, a-z # 62 to 92 = A-Z, a-z, 0-9, +/_=~|,;:?!@#$%^&*()<>{}[]\`'"
\s-1DECODE\s0
# purpose : compare the first argument against # succeding arguments at position 1 + 2N # (N = 0 to (# of arguments - 2)/2), and if equal, # return the value of the argument at 1 + 2N + 1; if no # arguments are equal, the last argument value is returned # arguments : 4 or more expressions, must be even # of arguments # returns : the value of the argument at 1 + 2N + 1 if argument 1 + 2N # is equal to argument1; else the last argument value # # example : SELECT DECODE(some_column, # 'first value', 'first value matched' # '2nd value', '2nd value matched' # 'no value matched' # )
\s-1INSERT\s0
# purpose : string where L characters have been deleted from STR1, beginning at S, # and where STR2 has been inserted into STR1, beginning at S. NULL for any NULL args. # arguments : STR1, S, L, STR2
\s-1HEX\s0 & \s-1OCT\s0 & \s-1BIN\s0
# purpose : convert number X from decimal to hex/octal/binary; equiv. to CONV(X, 10, 16/8/2) # arguments : X
\s-1LEFT\s0 & \s-1RIGHT\s0
# purpose : leftmost or rightmost L characters in STR, or NULL for any NULL args # arguments : STR1, L
\s-1LOCATE\s0 aka \s-1POSITION\s0
# 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)
\s-1LOWER\s0 & \s-1UPPER\s0 aka \s-1LCASE\s0 & \s-1UCASE\s0
# purpose : lower-case or upper-case a string # arguments : a string # returns : the sting lower or upper cased
\s-1LTRIM\s0 & \s-1RTRIM\s0
# purpose : left/right counterparts for TRIM # arguments : string
\s-1OCTET_LENGTH\s0
# purpose : length of the string in bytes (not characters) # arguments : string
\s-1REGEX\s0
# purpose : test if a string matches a perl regular expression # arguments : a string and a regex to match the string against # returns : boolean value of the regex match # # example : ... WHERE REGEX(col3,'/^fun/i') ... matches rows # in which col3 starts with "fun", ignoring case
\s-1REPEAT\s0
# purpose : string composed of STR1 repeated C times, or NULL for any NULL args # arguments : STR1, C
\s-1REPLACE\s0 aka \s-1SUBSTITUTE\s0
# purpose : perform perl subsitution on input string # arguments : a string and a substitute pattern string # returns : the result of the substitute operation # # example : ... WHERE REPLACE(col3,'s/fun(\w+)nier/$1/ig') ... replaces # all instances of /fun(\w+)nier/ in col3 with the string # between 'fun' and 'nier'
\s-1SOUNDEX\s0
# purpose : test if two strings have matching soundex codes # arguments : two strings # returns : true if the strings share the same soundex code # # example : ... WHERE SOUNDEX(col3,'fun') ... matches rows # in which col3 is a soundex match for "fun"
\s-1SPACE\s0
# purpose : a string of spaces # arguments : number of spaces
\s-1SUBSTRING\s0
SUBSTRING( string FROM start_pos [FOR length] )
Returns the substring starting at start_pos and extending for \*(L"length\*(R" character or until the end of the string, if no \*(L"length\*(R" is supplied. Examples:
SUBSTRING( 'foobar' FROM 4 ) # returns "bar"
SUBSTRING( 'foobar' FROM 4 FOR 2) # returns "ba"
Note: The \s-1SUBSTRING\s0 function is implemented in SQL::Parser and SQL::Statement and, at the current time, can not be over-ridden.
\s-1SUBSTR\s0
# purpose : same as SUBSTRING, except with comma-delimited params, instead of words (NULL for any NULL args) # arguments : string, start_pos, [length]
\s-1TRANSLATE\s0
# purpose : transliteration; replace a set of characters in a string with another set of characters (a la tr///), or NULL for any NULL args # arguments : string, string to replace, replacement string
\s-1TRIM\s0
TRIM ( [ [LEADING|TRAILING|BOTH] ['trim_char'] FROM ] string )
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
Note: The \s-1TRIM\s0 function is implemented in SQL::Parser and SQL::Statement and, at the current time, can not be over-ridden.
\s-1UNHEX\s0
# purpose : convert each pair of hexadecimal digits to a byte (or a Unicode character) # arguments : string of hex digits, with an optional encoding name of the data string
\s-1ABS\s0
# purpose : find the absolute value of a given numeric expression # arguments : numeric expression
\s-1CEILING\s0 (aka \s-1CEIL\s0) & \s-1FLOOR\s0
# purpose : rounds up/down to the nearest integer # arguments : numeric expression
\s-1EXP\s0
# purpose : raise e to the power of a number # arguments : numeric expression
\s-1LOG\s0
# purpose : base B logarithm of X # arguments : B, X or just one argument of X for base 10
\s-1LN\s0 & \s-1LOG10\s0
# purpose : natural logarithm (base e) or base 10 of X # arguments : numeric expression
\s-1MOD\s0
# purpose : modulus, or remainder, left over from dividing X / Y # arguments : X, Y
\s-1POWER\s0 aka \s-1POW\s0
# purpose : X to the power of Y # arguments : X, Y
\s-1RAND\s0
# purpose : random fractional number greater than or equal to 0 and less than the value of X # arguments : X (with optional seed value of Y)
\s-1ROUND\s0
# purpose : round X with Y number of decimal digits (precision) # arguments : X, optional Y defaults to 0
\s-1SIGN\s0
# purpose : returns -1, 0, 1, NULL for negative, 0, positive, NULL values, respectively # arguments : numeric expression
\s-1SQRT\s0
# purpose : square root of X # arguments : X
\s-1TRUNCATE\s0 aka \s-1TRUNC\s0
# purpose : similar to ROUND, but removes the decimal # arguments : X, optional Y defaults to 0
All of these functions work exactly like their counterparts in Math::Trig; go there for documentation.
Takes a single parameter. All of Math::Trig's aliases are included.
The y,x version of arc tangent.
Converts out-of-bounds values into its correct range.
Like their Math::Trig's counterparts, accepts an optional 2nd boolean parameter (like \s-1TRUE\s0) to keep prevent range wrapping.
\s-1DEGREES\s0 and \s-1RADIANS\s0 are included for \s-1SQL-92\s0 compatibility, and map to \s-1RAD2DEG\s0 and \s-1DEG2RAD\s0, respectively.
\s-1PI\s0 can be used without parentheses.
\s-1DBNAME\s0 & \s-1USERNAME\s0 (aka \s-1USER\s0)
# purpose : name of the database / username # arguments : none
\s-1IMPORT\s0
CREATE TABLE foo AS IMPORT(?) ,{},$external_executed_sth CREATE TABLE foo AS IMPORT(?) ,{},$AoA
\s-1RUN\s0
Takes the name of a file containing \s-1SQL\s0 statements and runs the statements; see SQL::Parser for documentation.
If you make a generally useful \s-1UDF\s0, 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 [email protected] mailing list (see <http://dbi.perl.org>).
Thanks in advance :-).
Dean Arnold supplied \s-1DECODE\s0, \s-1COALESCE\s0, \s-1REPLACE\s0, many thanks! Brendan Byrd added in the Numeric/Trig/System functions and filled in the \s-1SQL92/ODBC\s0 gaps for the date/string functions.
Copyright (c) 2005 by Jeff Zucker: jzuckerATcpan.org Copyright (c) 2009,2010 by Jens Rehsack: rehsackATcpan.org
All rights reserved.
The module may be freely distributed under the same terms as Perl itself using either the \*(L"\s-1GPL\s0 License\*(R" or the \*(L"Artistic License\*(R" as specified in the Perl \s-1README\s0 file.