1SQL::Statement::FunctioUnsse(r3)Contributed Perl DocumenStQaLt:i:oSntatement::Functions(3)
2
3
4

NAME

6       SQL::Statement::Functions - built-in & user-defined SQL functions
7

SYNOPSIS

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

DESCRIPTION

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

Function syntax

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
34        # valid for both parsing and executing
35
36            SELECT MyFunc(args);
37            SELECT * FROM MyFunc(args);
38            SELECT * FROM x WHERE MyFuncs(args);
39            SELECT * FROM x WHERE y < MyFuncs(args);
40
41        # valid only for parsing (won't work from a DBD)
42
43            SELECT MyFunc(args) FROM x WHERE y;
44

User-Defined Functions

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
53        # load a single function "foo" from a subroutine
54        # named "foo" in the current package
55
56             $dbh->do(" CREATE FUNCTION foo EXTERNAL ");
57
58        # load a single function "foo" from a subroutine
59        # named "bar" in the current package
60
61             $dbh->do(" CREATE FUNCTION foo EXTERNAL NAME bar");
62
63
64        # load a single function "foo" from a subroutine named "foo"
65        # in another package
66
67             $dbh->do(' CREATE FUNCTION foo EXTERNAL NAME "Bar::Baz::foo" ');
68
69        # load all the functions in another package
70
71             $dbh->do(' LOAD "Bar::Baz" ');
72
73       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
78        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
84        # in another package
85        $dbh->do("LOAD Qux::Quimble");
86
87        # This loads FOO and BAR as SQL functions.
88
89   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
96        # 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
105       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
109        sub FOO {
110            my($self,$sth,@params);
111        }
112
113       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
117       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
122        sub FOO {
123            my($self,$sth,@params);
124            my $dbh = $sth->{Database};
125            # $dbh->do( ...), etc.
126        }
127
128       In actual practice you probably want to use $sth->{Database} directly
129       rather than making a local copy, so $sth->{Database}->do(...).
130
131       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
136        sub MULTIPLY {
137            my($self,$sth,@params);
138            return $params[0] * $params[1];
139        }
140
141        # first make the function available
142        #
143        $dbh->do("CREATE FUNCTION MULTIPLY");
144
145        # 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
155   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
162       1. create a function that returns an AoA,
163
164         sub Japh {[
165             [qw( id word   )],
166             [qw( 1 Hacker  )],
167             [qw( 2 Perl    )],
168             [qw( 3 Another )],
169             [qw( 4 Just    )],
170         ]}
171
172       2. make your database handle aware of the function
173
174         $dbh->do("CREATE FUNCTION 'Japh');
175
176       3. Access the data in the AoA from SQL
177
178         $sth = $dbh->prepare("SELECT word FROM Japh ORDER BY id DESC");
179
180       Or here's an example that does a join on two in-memory tables:
181
182         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
187       The "Prof" and "Class" functions return tables which can be used like
188       any SQL table.
189
190       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
194        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
210       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
214       Table-Returning functions are a way to turn *anything* that can be
215       modeled as an AoA into a DBI data source.
216

Built-in Functions

218   SQL-92/ODBC Compatibility
219       All ODBC 3.0 functions are available except for the following:
220
221        ### SQL-92 / ODBC Functions
222
223        # 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
227        ### ODBC 3.0 Time/Date Functions only
228
229        # 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
233       ODBC 3.0 functions that are implemented with differences include:
234
235        # 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
244   Aggregate Functions
245       MIN, MAX, AVG, SUM, COUNT
246
247       Aggregate functions are handled elsewhere, see SQL::Parser for
248       documentation.
249
250   Date and Time Functions
251       These functions can be used without parentheses.
252
253       CURRENT_DATE aka CURDATE
254
255        # purpose   : find current date
256        # arguments : none
257        # returns   : string containing current date as yyyy-mm-dd
258
259       CURRENT_TIME aka CURTIME
260
261        # purpose   : find current time
262        # arguments : optional seconds precision
263        # returns   : string containing current time as hh:mm:ss (or ss.sss...)
264
265       CURRENT_TIMESTAMP aka NOW
266
267        # 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
271       UNIX_TIMESTAMP
272
273        # 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
277   String Functions
278       ASCII & CHAR
279
280        # 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
283       BIT_LENGTH
284
285        # purpose   : length of the string in bits
286        # arguments : string
287
288       CHARACTER_LENGTH aka CHAR_LENGTH
289
290        # purpose   : find length in characters of a string
291        # arguments : a string
292        # returns   : a number - the length of the string in characters
293
294       COALESCE aka NVL aka IFNULL
295
296        # 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
302       CONCAT
303
304        # 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
313       CONV
314
315        # 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
330       DECODE
331
332        # 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
347       INSERT
348
349        # 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
353       HEX & OCT & BIN
354
355        # purpose   : convert number X from decimal to hex/octal/binary; equiv. to CONV(X, 10, 16/8/2)
356        # arguments : X
357
358       LEFT & RIGHT
359
360        # purpose   : leftmost or rightmost L characters in STR, or NULL for any NULL args
361        # arguments : STR1, L
362
363       LOCATE aka POSITION
364
365        # 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
369       LOWER & UPPER aka LCASE & UCASE
370
371        # purpose   : lower-case or upper-case a string
372        # arguments : a string
373        # returns   : the sting lower or upper cased
374
375       LTRIM & RTRIM
376
377        # purpose   : left/right counterparts for TRIM
378        # arguments : string
379
380       OCTET_LENGTH
381
382        # purpose   : length of the string in bytes (not characters)
383        # arguments : string
384
385       REGEX
386
387        # 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
394       REPEAT
395
396        # purpose   : string composed of STR1 repeated C times, or NULL for any NULL args
397        # arguments : STR1, C
398
399       REPLACE aka SUBSTITUTE
400
401        # 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
409       SOUNDEX
410
411        # 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
418       SPACE
419
420        # purpose   : a string of spaces
421        # arguments : number of spaces
422
423       SUBSTRING
424
425         SUBSTRING( string FROM start_pos [FOR length] )
426
427       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
431         SUBSTRING( 'foobar' FROM 4 )       # returns "bar"
432
433         SUBSTRING( 'foobar' FROM 4 FOR 2)  # returns "ba"
434
435       Note: The SUBSTRING function is implemented in SQL::Parser and
436       SQL::Statement and, at the current time, can not be over-ridden.
437
438       SUBSTR
439
440        # 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
444       TRANSLATE
445
446        # 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
450       TRIM
451
452         TRIM ( [ [LEADING|TRAILING|BOTH] ['trim_char'] FROM ] string )
453
454       Removes all occurrences of <trim_char> from the front, back, or both
455       sides of a string.
456
457        BOTH is the default if neither LEADING nor TRAILING is specified.
458
459        Space is the default if no trim_char is specified.
460
461        Examples:
462
463        TRIM( string )
464          trims leading and trailing spaces from string
465
466        TRIM( LEADING FROM str )
467          trims leading spaces from string
468
469        TRIM( 'x' FROM str )
470          trims leading and trailing x's from string
471
472       Note: The TRIM function is implemented in SQL::Parser and
473       SQL::Statement and, at the current time, can not be over-ridden.
474
475       UNHEX
476
477        # 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
480   Numeric Functions
481       ABS
482
483        # purpose   : find the absolute value of a given numeric expression
484        # arguments : numeric expression
485
486       CEILING (aka CEIL) & FLOOR
487
488        # purpose   : rounds up/down to the nearest integer
489        # arguments : numeric expression
490
491       EXP
492
493        # purpose   : raise e to the power of a number
494        # arguments : numeric expression
495
496       LOG
497
498        # purpose   : base B logarithm of X
499        # arguments : B, X or just one argument of X for base 10
500
501       LN & LOG10
502
503        # purpose   : natural logarithm (base e) or base 10 of X
504        # arguments : numeric expression
505
506       MOD
507
508        # purpose   : modulus, or remainder, left over from dividing X / Y
509        # arguments : X, Y
510
511       POWER aka POW
512
513        # purpose   : X to the power of Y
514        # arguments : X, Y
515
516       RAND
517
518        # 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
521       ROUND
522
523        # purpose   : round X with Y number of decimal digits (precision)
524        # arguments : X, optional Y defaults to 0
525
526       SIGN
527
528        # purpose   : returns -1, 0, 1, NULL for negative, 0, positive, NULL values, respectively
529        # arguments : numeric expression
530
531       SQRT
532
533        # purpose   : square root of X
534        # arguments : X
535
536       TRUNCATE aka TRUNC
537
538        # purpose   : similar to ROUND, but removes the decimal
539        # arguments : X, optional Y defaults to 0
540
541   Trigonometric Functions
542       All of these functions work exactly like their counterparts in
543       Math::Trig; go there for documentation.
544
545       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
580       ATAN2
581           The y,x version of arc tangent.
582
583       DEG2DEG
584       DEG2GRAD
585       DEG2RAD
586           Converts out-of-bounds values into its correct range.
587
588       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
597       DEGREES
598       RADIANS
599           DEGREES and RADIANS are included for SQL-92 compatibility, and map
600           to RAD2DEG and DEG2RAD, respectively.
601
602       PI  PI can be used without parentheses.
603
604   System Functions
605       DBNAME & USERNAME (aka USER)
606
607        # purpose   : name of the database / username
608        # arguments : none
609
610   Special Utility Functions
611       IMPORT
612
613        CREATE TABLE foo AS IMPORT(?)    ,{},$external_executed_sth
614        CREATE TABLE foo AS IMPORT(?)    ,{},$AoA
615
616       RUN
617
618       Takes the name of a file containing SQL statements and runs the
619       statements; see SQL::Parser for documentation.
620

Submitting built-in functions

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
628       Thanks in advance :-).
629

ACKNOWLEDGEMENTS

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-2020 by Jens Rehsack: rehsackATcpan.org
638
639       All rights reserved.
640
641       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.
644
645
646
647perl v5.38.0                      2023-07-21      SQL::Statement::Functions(3)
Impressum