1SQL::Statement::FunctioUnsse(r3)Contributed Perl DocumenStQaLt:i:oSntatement::Functions(3)
2
6 SQL::Statement::Functions - built-in & user-defined SQL functions
7
9 SELECT Func(args);
10 SELECT * FROM Func(args);
11 SELECT * FROM x WHERE Funcs(args);
12 SELECT * FROM x WHERE y < Funcs(args);
13
15 This module contains the built-in functions for SQL::Parser and
16 SQL::Statement. All of the functions are also available in any DBDs
17 that subclass those modules (e.g. DBD::CSV, DBD::DBM, DBD::File,
18 DBD::AnyData, DBD::Excel, etc.).
19 This documentation covers built-in functions and also explains how to
21 create your own functions to supplement the built-in ones. It's easy.
22 If you create one that is generally useful, see below for how to submit
23 it to become a built-in function.
24
26 When using SQL::Statement/SQL::Parser directly to parse SQL, functions
27 (either built-in or user-defined) may occur anywhere in a SQL statement
28 that values, column names, table names, or predicates may occur. When
29 using the modules through a DBD or in any other context in which the
30 SQL is both parsed and executed, functions can occur in the same places
31 except that they can not occur in the column selection clause of a
32 SELECT statement that contains a FROM clause.
33 # valid for both parsing and executing
35 SELECT MyFunc(args);
37 SELECT * FROM MyFunc(args);
38 SELECT * FROM x WHERE MyFuncs(args);
39 SELECT * FROM x WHERE y < MyFuncs(args);
40 # valid only for parsing (won't work from a DBD)
42 SELECT MyFunc(args) FROM x WHERE y;
44
46 Loading User-Defined Functions
47 In addition to the built-in functions, you can create any number of
48 your own user-defined functions (UDFs). In order to use a UDF in a
49 script, you first have to create a perl subroutine (see below), then
50 you need to make the function available to your database handle with
51 the CREATE FUNCTION or LOAD commands:
52 # load a single function "foo" from a subroutine
54 # named "foo" in the current package
55 $dbh->do(" CREATE FUNCTION foo EXTERNAL ");
57 # load a single function "foo" from a subroutine
59 # named "bar" in the current package
60 $dbh->do(" CREATE FUNCTION foo EXTERNAL NAME bar");
62 # load a single function "foo" from a subroutine named "foo"
65 # in another package
66 $dbh->do(' CREATE FUNCTION foo EXTERNAL NAME "Bar::Baz::foo" ');
68 # load all the functions in another package
70 $dbh->do(' LOAD "Bar::Baz" ');
72 Functions themselves should follow SQL identifier naming rules.
74 Subroutines loaded with CREATE FUNCTION can have any valid perl
75 subroutine name. Subroutines loaded with LOAD must start with
76 SQL_FUNCTION_ and then the actual function name. For example:
77 package Qux::Quimble;
79 sub SQL_FUNCTION_FOO { ... }
80 sub SQL_FUNCTION_BAR { ... }
81 sub some_other_perl_subroutine_not_a_function { ... }
82 1;
83 # in another package
85 $dbh->do("LOAD Qux::Quimble");
86 # This loads FOO and BAR as SQL functions.
88 Creating User-Defined Functions
90 User-defined functions (UDFs) are perl subroutines that return values
91 appropriate to the context of the function in a SQL statement. For
92 example the built-in CURRENT_TIME returns a string value and therefore
93 may be used anywhere in a SQL statement that a string value can. Here'
94 the entire perl code for the function:
95 # CURRENT_TIME
97 #
98 # arguments : none
99 # returns : string containing current time as hh::mm::ss
100 #
101 sub SQL_FUNCTION_CURRENT_TIME {
102 sprintf "%02s::%02s::%02s",(localtime)[2,1,0]
103 }
104 More complex functions can make use of a number of arguments always
106 passed to functions automatically. Functions always receive these
107 values in @_:
108 sub FOO {
110 my($self,$sth,@params);
111 }
112 The first argument, $self, is whatever class the function is defined
114 in, not generally useful unless you have an entire module to support
115 the function.
116 The second argument, $sth is the active statement handle of the current
118 statement. Like all active statement handles it contains the current
119 database handle in the {Database} attribute so you can have access to
120 the database handle in any function:
121 sub FOO {
123 my($self,$sth,@params);
124 my $dbh = $sth->{Database};
125 # $dbh->do( ...), etc.
126 }
127 In actual practice you probably want to use $sth->{Database} directly
129 rather than making a local copy, so $sth->{Database}->do(...).
130 The remaining arguments, @params, are arguments passed by users to the
132 function, either directly or with placeholders; another silly example
133 which just returns the results of multiplying the arguments passed to
134 it:
135 sub MULTIPLY {
137 my($self,$sth,@params);
138 return $params[0] * $params[1];
139 }
140 # first make the function available
142 #
143 $dbh->do("CREATE FUNCTION MULTIPLY");
144 # then multiply col3 in each row times seven
146 #
147 my $sth=$dbh->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,7)");
148 $sth->execute;
149 #
150 # or
151 #
152 my $sth=$dbh->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,?)");
153 $sth->execute(7);
154 Creating In-Memory Tables with functions
156 A function can return almost anything, as long is it is an appropriate
157 return for the context the function will be used in. In the special
158 case of table-returning functions, the function should return a
159 reference to an array of array references with the first row being the
160 column names and the remaining rows the data. For example:
161 1. create a function that returns an AoA,
163 sub Japh {[
165 [qw( id word )],
166 [qw( 1 Hacker )],
167 [qw( 2 Perl )],
168 [qw( 3 Another )],
169 [qw( 4 Just )],
170 ]}
171 2. make your database handle aware of the function
173 $dbh->do("CREATE FUNCTION 'Japh');
175 3. Access the data in the AoA from SQL
177 $sth = $dbh->prepare("SELECT word FROM Japh ORDER BY id DESC");
179 Or here's an example that does a join on two in-memory tables:
181 sub Prof {[ [qw(pid pname)],[qw(1 Sue )],[qw(2 Bob)],[qw(3 Tom )] ]}
183 sub Class {[ [qw(pid cname)],[qw(1 Chem)],[qw(2 Bio)],[qw(2 Math)] ]}
184 $dbh->do("CREATE FUNCTION $_) for qw(Prof Class);
185 $sth = $dbh->prepare("SELECT * FROM Prof NATURAL JOIN Class");
186 The "Prof" and "Class" functions return tables which can be used like
188 any SQL table.
189 More complex functions might do something like scrape an RSS feed, or
191 search a file system and put the results in AoA. For example, to
192 search a directory with SQL:
193 sub Dir {
195 my($self,$sth,$dir)=@_;
196 opendir D, $dir or die "'$dir':$!";
197 my @files = readdir D;
198 my $data = [[qw(fileName fileExt)]];
199 for (@files) {
200 my($fn,$ext) = /^(.*)(\.[^\.]+)$/;
201 push @$data, [$fn,$ext];
202 }
203 return $data;
204 }
205 $dbh->do("CREATE FUNCTION Dir");
206 printf "%s\n", join' ',@{ $dbh->selectcol_arrayref("
207 SELECT fileName FROM Dir('./') WHERE fileExt = '.pl'
208 ")};
209 Obviously, that function could be expanded with File::Find and/or stat
211 to provide more information and it could be made to accept a list of
212 directories rather than a single directory.
213 Table-Returning functions are a way to turn *anything* that can be
215 modeled as an AoA into a DBI data source.
216
218 SQL-92/ODBC Compatibility
219 All ODBC 3.0 functions are available except for the following:
220 ### SQL-92 / ODBC Functions
222 # CONVERT / CAST - Complex to implement, but a draft is in the works.
224 # DIFFERENCE - Function is not clearly defined in spec and has very limited applications
225 # EXTRACT - Contains a FROM keyword and requires rather freeform datetime/interval expression
226 ### ODBC 3.0 Time/Date Functions only
228 # DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE, MONTH, MONTHNAME, QUARTER, SECOND, TIMESTAMPDIFF,
230 # WEEK, YEAR - Requires freeform datetime/interval expressions. In a later release, these could
231 # be implemented with the help of Date::Parse.
232 ODBC 3.0 functions that are implemented with differences include:
234 # SOUNDEX - Returns true/false, instead of a SOUNDEX code
236 # RAND - Seed value is a second parameter with a new first parameter for max limit
237 # LOG - Returns base X (or 10) log of number, not natural log. LN is used for natural log, and
238 # LOG10 is still available for standards compatibility.
239 # POSITION - Does not use 'IN' keyword; cannot be fixed as previous versions of SQL::Statement defined
240 # the function as such.
241 # REPLACE / SUBSTITUTE - Uses a regular expression string for the second parameter, replacing the last two
242 # parameters of the typical ODBC function
243 Aggregate Functions
245 MIN, MAX, AVG, SUM, COUNT
246 Aggregate functions are handled elsewhere, see SQL::Parser for
248 documentation.
249 Date and Time Functions
251 These functions can be used without parentheses.
252 CURRENT_DATE aka CURDATE
254 # purpose : find current date
256 # arguments : none
257 # returns : string containing current date as yyyy-mm-dd
258 CURRENT_TIME aka CURTIME
260 # purpose : find current time
262 # arguments : optional seconds precision
263 # returns : string containing current time as hh:mm:ss (or ss.sss...)
264 CURRENT_TIMESTAMP aka NOW
266 # purpose : find current date and time
268 # arguments : optional seconds precision
269 # returns : string containing current timestamp as yyyy-mm-dd hh:mm:ss (or ss.sss...)
270 UNIX_TIMESTAMP
272 # purpose : find the current time in UNIX epoch format
274 # arguments : optional seconds precision (unlike the MySQL version)
275 # returns : a (64-bit) number, possibly with decimals
276 String Functions
278 ASCII & CHAR
279 # purpose : same as ord and chr, respectively (NULL for any NULL args)
281 # arguments : string or character (or number for CHAR); CHAR can have any amount of numbers for a string
282 BIT_LENGTH
284 # purpose : length of the string in bits
286 # arguments : string
287 CHARACTER_LENGTH aka CHAR_LENGTH
289 # purpose : find length in characters of a string
291 # arguments : a string
292 # returns : a number - the length of the string in characters
293 COALESCE aka NVL aka IFNULL
295 # purpose : return the first non-NULL value from a list
297 # arguments : 1 or more expressions
298 # returns : the first expression (reading left to right)
299 # which is not NULL; returns NULL if all are NULL
300 #
301 CONCAT
303 # purpose : concatenate 1 or more strings into a single string;
305 # an alternative to the '||' operator
306 # arguments : 1 or more strings
307 # returns : the concatenated string
308 #
309 # example : SELECT CONCAT(first_string, 'this string', ' that string')
310 # returns "<value-of-first-string>this string that string"
311 # note : if any argument evaluates to NULL, the returned value is NULL
312 CONV
314 # purpose : convert a number X from base Y to base Z (from base 2 to 64)
316 # arguments : X (can by a number or string depending on the base), Y, Z (Z defaults to 10)
317 Valid bases for Y and Z are: 2, 8, 10, 16 and 64
318 # returns : either a string or number, in base Z
319 # notes : Behavioral table
320 #
321 # base | valuation
322 # ------+-----------
323 # 2 | binary, base 2 - (0,1)
324 # 8 | octal, base 8 - (0..7)
325 # 10 | decimal, base 10 - (0..9)
326 # 16 | hexadecimal, base 16 - (0..9,a..f)
327 # 64 | 0-63 from MIME::Base64
328 #
329 DECODE
331 # purpose : compare the first argument against
333 # succeeding arguments at position 1 + 2N
334 # (N = 0 to (# of arguments - 2)/2), and if equal,
335 # return the value of the argument at 1 + 2N + 1; if no
336 # arguments are equal, the last argument value is returned
337 # arguments : 4 or more expressions, must be even # of arguments
338 # returns : the value of the argument at 1 + 2N + 1 if argument 1 + 2N
339 # is equal to argument1; else the last argument value
340 #
341 # example : SELECT DECODE(some_column,
342 # 'first value', 'first value matched'
343 # '2nd value', '2nd value matched'
344 # 'no value matched'
345 # )
346 INSERT
348 # purpose : string where L characters have been deleted from STR1, beginning at S,
350 # and where STR2 has been inserted into STR1, beginning at S. NULL for any NULL args.
351 # arguments : STR1, S, L, STR2
352 HEX & OCT & BIN
354 # purpose : convert number X from decimal to hex/octal/binary; equiv. to CONV(X, 10, 16/8/2)
356 # arguments : X
357 LEFT & RIGHT
359 # purpose : leftmost or rightmost L characters in STR, or NULL for any NULL args
361 # arguments : STR1, L
362 LOCATE aka POSITION
364 # purpose : starting position (one-based) of the first occurrence of STR1
366 within STR2; 0 if it doesn't occur and NULL for any NULL args
367 # arguments : STR1, STR2, and an optional S (starting position to search)
368 LOWER & UPPER aka LCASE & UCASE
370 # purpose : lower-case or upper-case a string
372 # arguments : a string
373 # returns : the sting lower or upper cased
374 LTRIM & RTRIM
376 # purpose : left/right counterparts for TRIM
378 # arguments : string
379 OCTET_LENGTH
381 # purpose : length of the string in bytes (not characters)
383 # arguments : string
384 REGEX
386 # purpose : test if a string matches a perl regular expression
388 # arguments : a string and a regex to match the string against
389 # returns : boolean value of the regex match
390 #
391 # example : ... WHERE REGEX(col3,'/^fun/i') ... matches rows
392 # in which col3 starts with "fun", ignoring case
393 REPEAT
395 # purpose : string composed of STR1 repeated C times, or NULL for any NULL args
397 # arguments : STR1, C
398 REPLACE aka SUBSTITUTE
400 # purpose : perform perl subsitution on input string
402 # arguments : a string and a substitute pattern string
403 # returns : the result of the substitute operation
404 #
405 # example : ... WHERE REPLACE(col3,'s/fun(\w+)nier/$1/ig') ... replaces
406 # all instances of /fun(\w+)nier/ in col3 with the string
407 # between 'fun' and 'nier'
408 SOUNDEX
410 # purpose : test if two strings have matching soundex codes
412 # arguments : two strings
413 # returns : true if the strings share the same soundex code
414 #
415 # example : ... WHERE SOUNDEX(col3,'fun') ... matches rows
416 # in which col3 is a soundex match for "fun"
417 SPACE
419 # purpose : a string of spaces
421 # arguments : number of spaces
422 SUBSTRING
424 SUBSTRING( string FROM start_pos [FOR length] )
426 Returns the substring starting at start_pos and extending for "length"
428 character or until the end of the string, if no "length" is supplied.
429 Examples:
430 SUBSTRING( 'foobar' FROM 4 ) # returns "bar"
432 SUBSTRING( 'foobar' FROM 4 FOR 2) # returns "ba"
434 Note: The SUBSTRING function is implemented in SQL::Parser and
436 SQL::Statement and, at the current time, can not be over-ridden.
437 SUBSTR
439 # purpose : same as SUBSTRING, except with comma-delimited params, instead of
441 words (NULL for any NULL args)
442 # arguments : string, start_pos, [length]
443 TRANSLATE
445 # purpose : transliteration; replace a set of characters in a string with another
447 set of characters (a la tr///), or NULL for any NULL args
448 # arguments : string, string to replace, replacement string
449 TRIM
451 TRIM ( [ [LEADING|TRAILING|BOTH] ['trim_char'] FROM ] string )
453 Removes all occurrences of <trim_char> from the front, back, or both
455 sides of a string.
456 BOTH is the default if neither LEADING nor TRAILING is specified.
458 Space is the default if no trim_char is specified.
460 Examples:
462 TRIM( string )
464 trims leading and trailing spaces from string
465 TRIM( LEADING FROM str )
467 trims leading spaces from string
468 TRIM( 'x' FROM str )
470 trims leading and trailing x's from string
471 Note: The TRIM function is implemented in SQL::Parser and
473 SQL::Statement and, at the current time, can not be over-ridden.
474 UNHEX
476 # purpose : convert each pair of hexadecimal digits to a byte (or a Unicode character)
478 # arguments : string of hex digits, with an optional encoding name of the data string
479 Numeric Functions
481 ABS
482 # purpose : find the absolute value of a given numeric expression
484 # arguments : numeric expression
485 CEILING (aka CEIL) & FLOOR
487 # purpose : rounds up/down to the nearest integer
489 # arguments : numeric expression
490 EXP
492 # purpose : raise e to the power of a number
494 # arguments : numeric expression
495 LOG
497 # purpose : base B logarithm of X
499 # arguments : B, X or just one argument of X for base 10
500 LN & LOG10
502 # purpose : natural logarithm (base e) or base 10 of X
504 # arguments : numeric expression
505 MOD
507 # purpose : modulus, or remainder, left over from dividing X / Y
509 # arguments : X, Y
510 POWER aka POW
512 # purpose : X to the power of Y
514 # arguments : X, Y
515 RAND
517 # purpose : random fractional number greater than or equal to 0 and less than the value of X
519 # arguments : X (with optional seed value of Y)
520 ROUND
522 # purpose : round X with Y number of decimal digits (precision)
524 # arguments : X, optional Y defaults to 0
525 SIGN
527 # purpose : returns -1, 0, 1, NULL for negative, 0, positive, NULL values, respectively
529 # arguments : numeric expression
530 SQRT
532 # purpose : square root of X
534 # arguments : X
535 TRUNCATE aka TRUNC
537 # purpose : similar to ROUND, but removes the decimal
539 # arguments : X, optional Y defaults to 0
540 Trigonometric Functions
542 All of these functions work exactly like their counterparts in
543 Math::Trig; go there for documentation.
544 ACOS
546 ACOSEC
547 ACOSECH
548 ACOSH
549 ACOT
550 ACOTAN
551 ACOTANH
552 ACOTH
553 ACSC
554 ACSCH
555 ASEC
556 ASECH
557 ASIN
558 ASINH
559 ATAN
560 ATANH
561 COS
562 COSEC
563 COSECH
564 COSH
565 COT
566 COTAN
567 COTANH
568 COTH
569 CSC
570 CSCH
571 SEC
572 SECH
573 SIN
574 SINH
575 TAN
576 TANH
577 Takes a single parameter. All of Math::Trig's aliases are
578 included.
579 ATAN2
581 The y,x version of arc tangent.
582 DEG2DEG
584 DEG2GRAD
585 DEG2RAD
586 Converts out-of-bounds values into its correct range.
587 GRAD2DEG
589 GRAD2GRAD
590 GRAD2RAD
591 RAD2DEG
592 RAD2GRAD
593 RAD2RAD
594 Like their Math::Trig's counterparts, accepts an optional 2nd
595 boolean parameter (like TRUE) to keep prevent range wrapping.
596 DEGREES
598 RADIANS
599 DEGREES and RADIANS are included for SQL-92 compatibility, and map
600 to RAD2DEG and DEG2RAD, respectively.
601 PI PI can be used without parentheses.
603 System Functions
605 DBNAME & USERNAME (aka USER)
606 # purpose : name of the database / username
608 # arguments : none
609 Special Utility Functions
611 IMPORT
612 CREATE TABLE foo AS IMPORT(?) ,{},$external_executed_sth
614 CREATE TABLE foo AS IMPORT(?) ,{},$AoA
615 RUN
617 Takes the name of a file containing SQL statements and runs the
619 statements; see SQL::Parser for documentation.
620
622 If you make a generally useful UDF, why not submit it to me and have it
623 (and your name) included with the built-in functions? Please follow
624 the format shown in the module including a description of the arguments
625 and return values for the function as well as an example. Send them to
626 the dbi-dev@perl.org mailing list (see <http://dbi.perl.org>).
627 Thanks in advance :-).
629
631 Dean Arnold supplied DECODE, COALESCE, REPLACE, many thanks! Brendan
632 Byrd added in the Numeric/Trig/System functions and filled in the
633 SQL92/ODBC gaps for the date/string functions.
634
636 Copyright (c) 2005 by Jeff Zucker: jzuckerATcpan.org Copyright (c)
637 2009-2017 by Jens Rehsack: rehsackATcpan.org
638 All rights reserved.
640 The module may be freely distributed under the same terms as Perl
642 itself using either the "GPL License" or the "Artistic License" as
643 specified in the Perl README file.
644perl v5.28.1 2017-04-06 SQL::Statement::Functions(3)