Loading User-Defined Functions

 # 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:

 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.

Creating User-Defined Functions

 # 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);

Creating In-Memory Tables with functions

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 SQL

  $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 "Prof" and "Class" functions return tables which can be used like any SQL table.

More complex functions might do something like scrape an RSS feed, or search a file system and put the results in AoA. For example, to search a directory with SQL:

 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 DBI data source.

SQL-92/ODBC Compatibility

 ### 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

Aggregate Functions

Aggregate functions are handled elsewhere, see SQL::Parser for documentation.

Date and Time Functions

CURRENT_DATE aka CURDATE

 # purpose   : find current date
 # arguments : none
 # returns   : string containing current date as yyyy-mm-dd

CURRENT_TIME aka CURTIME

 # purpose   : find current time
 # arguments : optional seconds precision
 # returns   : string containing current time as hh:mm:ss (or ss.sss...)

CURRENT_TIMESTAMP aka NOW

 # 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...)

UNIX_TIMESTAMP

 # 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

String Functions

 # 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

BIT_LENGTH

 # purpose   : length of the string in bits
 # arguments : string

CHARACTER_LENGTH aka CHAR_LENGTH

 # purpose   : find length in characters of a string
 # arguments : a string
 # returns   : a number - the length of the string in characters

COALESCE aka NVL aka IFNULL

 # 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
 #

CONCAT

 # 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

CONV

 # purpose   : convert a number X from base Y to base Z (from base 2 to 64)
 # arguments : X (can by a number or string depending on the base), Y, Z (Z defaults to 10)
               Valid bases for Y and Z are: 2, 8, 10, 16 and 64
 # returns   : either a string or number, in base Z
 # notes     : Behavioral table
 #
 #      base | valuation
 #     ------+-----------
 #         2 | binary, base 2 - (0,1)
 #         8 | octal, base 8 - (0..7)
 #        10 | decimal, base 10 - (0..9)
 #        16 | hexadecimal, base 16 - (0..9,a..f)
 #        64 | 0-63 from MIME::Base64
 #

DECODE

 # purpose   : compare the first argument against
 #             succeeding 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'
 #                    )

INSERT

 # 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

HEX & OCT & BIN

 # purpose   : convert number X from decimal to hex/octal/binary; equiv. to CONV(X, 10, 16/8/2)
 # arguments : X

LEFT & RIGHT

 # purpose   : leftmost or rightmost L characters in STR, or NULL for any NULL args
 # arguments : STR1, L

LOCATE aka POSITION

 # 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)

LOWER & UPPER aka LCASE & UCASE

 # purpose   : lower-case or upper-case a string
 # arguments : a string
 # returns   : the sting lower or upper cased

LTRIM & RTRIM

 # purpose   : left/right counterparts for TRIM
 # arguments : string

OCTET_LENGTH

 # purpose   : length of the string in bytes (not characters)
 # arguments : string

REGEX

 # 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

REPEAT

 # purpose   : string composed of STR1 repeated C times, or NULL for any NULL args
 # arguments : STR1, C

REPLACE aka SUBSTITUTE

 # 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'

SOUNDEX

 # 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"

SPACE

 # purpose   : a string of spaces
 # arguments : number of spaces

SUBSTRING

  SUBSTRING( string FROM start_pos [FOR length] )

Returns the substring starting at start_pos and extending for "length" character or until the end of the string, if no "length" is supplied. Examples:

  SUBSTRING( 'foobar' FROM 4 )       # returns "bar"

  SUBSTRING( 'foobar' FROM 4 FOR 2)  # returns "ba"

Note: The SUBSTRING function is implemented in SQL::Parser and SQL::Statement and, at the current time, can not be over-ridden.

SUBSTR

 # purpose   : same as SUBSTRING, except with comma-delimited params, instead of
               words (NULL for any NULL args)
 # arguments : string, start_pos, [length]

TRANSLATE

 # 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

TRIM

  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 TRIM function is implemented in SQL::Parser and SQL::Statement and, at the current time, can not be over-ridden.

UNHEX

 # 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

Numeric Functions

 # purpose   : find the absolute value of a given numeric expression
 # arguments : numeric expression

CEILING (aka CEIL) & FLOOR

 # purpose   : rounds up/down to the nearest integer
 # arguments : numeric expression

EXP

 # purpose   : raise e to the power of a number
 # arguments : numeric expression

LOG

 # purpose   : base B logarithm of X
 # arguments : B, X or just one argument of X for base 10

LN & LOG10

 # purpose   : natural logarithm (base e) or base 10 of X
 # arguments : numeric expression

MOD

 # purpose   : modulus, or remainder, left over from dividing X / Y
 # arguments : X, Y

POWER aka POW

 # purpose   : X to the power of Y
 # arguments : X, Y

RAND

 # 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)

ROUND

 # purpose   : round X with Y number of decimal digits (precision)
 # arguments : X, optional Y defaults to 0

SIGN

 # purpose   : returns -1, 0, 1, NULL for negative, 0, positive, NULL values, respectively
 # arguments : numeric expression

SQRT

 # purpose   : square root of X
 # arguments : X

TRUNCATE aka TRUNC

 # purpose   : similar to ROUND, but removes the decimal
 # arguments : X, optional Y defaults to 0

Trigonometric Functions

ACOS

ACOSEC

ACOSECH

ACOSH

ACOT

ACOTAN

ACOTANH

ACOTH

ACSC

ACSCH

ASEC

ASECH

ASIN

ASINH

ATAN

ATANH

COS

COSEC

COSECH

COSH

COT

COTAN

COTANH

COTH

CSC

CSCH

SEC

SECH

SIN

SINH

TAN

TANH

Takes a single parameter. All of Math::Trig's aliases are included.

ATAN2

The y,x version of arc tangent.

DEG2DEG

DEG2GRAD

DEG2RAD

Converts out-of-bounds values into its correct range.

GRAD2DEG

GRAD2GRAD

GRAD2RAD

RAD2DEG

RAD2GRAD

RAD2RAD

Like their Math::Trig's counterparts, accepts an optional 2nd boolean parameter (like TRUE) to keep prevent range wrapping.

DEGREES

RADIANS

DEGREES and RADIANS are included for SQL-92 compatibility, and map to RAD2DEG and DEG2RAD, respectively.

PI

PI can be used without parentheses.

System Functions

 # purpose   : name of the database / username
 # arguments : none

Special Utility Functions

 CREATE TABLE foo AS IMPORT(?)    ,{},$external_executed_sth
 CREATE TABLE foo AS IMPORT(?)    ,{},$AoA

RUN

Takes the name of a file containing SQL statements and runs the statements; see SQL::Parser for documentation.