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,$rowhash,@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,$rowhash,@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 third argument, $rowhash, is a reference to a hash containing the
132 key/value pairs for the current database row the SQL is searching.
133 This isn't relevant for something like CURRENT_TIME which isn't based
134 on a SQL search, but here's an example of a (rather useless) UDF using
135 $rowhash that just joins the values for the entire row with a colon:
136 sub COLON_JOIN {
138 my($self,$sth,$rowhash,@params);
139 my $str = join ':', values %$rowhash;
140 }
141 The remaining arguments, @params, are arguments passed by users to the
143 function, either directly or with placeholders; another silly example
144 which just returns the results of multiplying the arguments passed to
145 it:
146 sub MULTIPLY {
148 my($self,$sth,$rowhash,@params);
149 return $params[0] * $params[1];
150 }
151 # first make the function available
153 #
154 $dbh->do("CREATE FUNCTION MULTIPLY");
155 # then multiply col3 in each row times seven
157 #
158 my $sth=$dbh->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,7)");
159 $sth->execute;
160 #
161 # or
162 #
163 my $sth=$dbh->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,?)");
164 $sth->execute(7);
165 Creating In-Memory Tables with functions
167 A function can return almost anything, as long is it is an appropriate
168 return for the context the function will be used in. In the special
169 case of table-returning functions, the function should return a
170 reference to an array of array references with the first row being the
171 column names and the remaining rows the data. For example:
172 1. create a function that returns an AoA,
174 sub Japh {[
176 [qw( id word )],
177 [qw( 1 Hacker )],
178 [qw( 2 Perl )],
179 [qw( 3 Another )],
180 [qw( 4 Just )],
181 ]}
182 2. make your database handle aware of the function
184 $dbh->do("CREATE FUNCTION 'Japh');
186 3. Access the data in the AoA from SQL
188 $sth = $dbh->prepare("SELECT word FROM Japh ORDER BY id DESC");
190 Or here's an example that does a join on two in-memory tables:
192 sub Prof {[ [qw(pid pname)],[qw(1 Sue )],[qw(2 Bob)],[qw(3 Tom )] ]}
194 sub Class {[ [qw(pid cname)],[qw(1 Chem)],[qw(2 Bio)],[qw(2 Math)] ]}
195 $dbh->do("CREATE FUNCTION $_) for qw(Prof Class);
196 $sth = $dbh->prepare("SELECT * FROM Prof NATURAL JOIN Class");
197 The "Prof" and "Class" functions return tables which can be used like
199 any SQL table.
200 More complex functions might do something like scrape an RSS feed, or
202 search a file system and put the results in AoA. For example, to
203 search a directory with SQL:
204 sub Dir {
206 my($self,$sth,$rowhash,$dir)=@_;
207 opendir D, $dir or die "'$dir':$!";
208 my @files = readdir D;
209 my $data = [[qw(fileName fileExt)]];
210 for (@files) {
211 my($fn,$ext) = /^(.*)(\.[^\.]+)$/;
212 push @$data, [$fn,$ext];
213 }
214 return $data;
215 }
216 $dbh->do("CREATE FUNCTION Dir");
217 printf "%s\n", join' ',@{ $dbh->selectcol_arrayref("
218 SELECT fileName FROM Dir('./') WHERE fileExt = '.pl'
219 ")};
220 Obviously, that function could be expanded with File::Find and/or stat
222 to provide more information and it could be made to accept a list of
223 directories rather than a single directory.
224 Table-Returning functions are a way to turn *anything* that can be
226 modeled as an AoA into a DBI data source.
227
229 Aggregate Functions
230 min, max, avg, sum, count
231 Aggregate functions are handled elsewhere, see SQL::Parser for
233 documentation.
234 Date and Time Functions
236 current_date, current_time, current_timestamp
237 CURRENT_DATE
239 # purpose : find current date
241 # arguments : none
242 # returns : string containing current date as yyyy-mm-dd
243 CURRENT_TIME
245 # purpose : find current time
247 # arguments : none
248 # returns : string containing current time as hh::mm::ss
249 CURRENT_TIMESTAMP
251 # purpose : find current date and time
253 # arguments : none
254 # returns : string containing current timestamp as yyyy-mm-dd hh::mm::ss
255 String Functions
257 char_length, lower, position, regex, soundex, substring, trim, upper
258 CHAR_LENGTH
260 # purpose : find length in characters of a string
262 # arguments : a string
263 # returns : a number - the length of the string in characters
264 LOWER & UPPER
266 # purpose : lower-case or upper-case a string
268 # arguments : a string
269 # returns : the sting lower or upper cased
270 POSITION
272 # purpose : find first position of a substring in a string
274 # arguments : a substring and a string possibly containing the substring
275 # returns : a number - the index of the substring in the string
276 # or 0 if the substring doesn't occur in the sring
277 REGEX
279 # purpose : test if a string matches a perl regular expression
281 # arguments : a string and a regex to match the string against
282 # returns : boolean value of the regex match
283 #
284 # example : ... WHERE REGEX(col3,'/^fun/i') ... matches rows
285 # in which col3 starts with "fun", ignoring case
286 SOUNDEX
288 # purpose : test if two strings have matching soundex codes
290 # arguments : two strings
291 # returns : true if the strings share the same soundex code
292 #
293 # example : ... WHERE SOUNDEX(col3,'fun') ... matches rows
294 # in which col3 is a soundex match for "fun"
295 CONCAT
297 # purpose : concatenate 1 or more strings into a single string;
299 # an alternative to the '||' operator
300 # arguments : 1 or more strings
301 # returns : the concatenated string
302 #
303 # example : SELECT CONCAT(first_string, 'this string', ' that string')
304 # returns "<value-of-first-string>this string that string"
305 # note : if any argument evaluates to NULL, the returned value is NULL
306 COALESCE aka NVL
308 # purpose : return the first non-NULL value from a list
310 # arguments : 1 or more expressions
311 # returns : the first expression (reading left to right)
312 # which is not NULL; returns NULL if all are NULL
313 #
314 # example : SELECT COALESCE(NULL, some_null_column, 'not null')
315 # returns 'not null'
316 DECODE
318 # purpose : compare the first argument against
320 # succeding arguments at position 1 + 2N
321 # (N = 0 to (# of arguments - 2)/2), and if equal,
322 # return the value of the argument at 1 + 2N + 1; if no
323 # arguments are equal, the last argument value is returned
324 # arguments : 4 or more expressions, must be even # of arguments
325 # returns : the value of the argument at 1 + 2N + 1 if argument 1 + 2N
326 # is equal to argument1; else the last argument value
327 #
328 # example : SELECT DECODE(some_column,
329 # 'first value', 'first value matched'
330 # '2nd value', '2nd value matched'
331 # 'no value matched'
332 # )
333 REPLACE, SUBSTITUTE
335 # purpose : perform perl subsitution on input string
337 # arguments : a string and a substitute pattern string
338 # returns : the result of the substitute operation
339 #
340 # example : ... WHERE REPLACE(col3,'s/fun(\w+)nier/$1/ig') ... replaces
341 # all instances of /fun(\w+)nier/ in col3 with the string
342 # between 'fun' and 'nier'
343 SUBSTRING
345 SUBSTRING( string FROM start_pos [FOR length] )
347 Returns the substring starting at start_pos and extending for "length"
349 character or until the end of the string, if no "length" is supplied.
350 Examples:
351 SUBSTRING( 'foobar' FROM 4 ) # returns "bar"
353 SUBSTRING( 'foobar' FROM 4 FOR 2) # returns "ba"
355 Note: The SUBSTRING function is implemented in SQL::Parser and
357 SQL::Statement and, at the current time, can not be over-ridden.
358 TRIM
360 TRIM ( [ [LEADING|TRAILING|BOTH] ['trim_char'] FROM ] string )
362 Removes all occurrences of <trim_char> from the front, back, or both
364 sides of a string.
365 BOTH is the default if neither LEADING nor TRAILING is specified.
367 Space is the default if no trim_char is specified.
369 Examples:
371 TRIM( string )
373 trims leading and trailing spaces from string
374 TRIM( LEADING FROM str )
376 trims leading spaces from string
377 TRIM( 'x' FROM str )
379 trims leading and trailing x's from string
380 Note: The TRIM function is implemented in SQL::Parser and
382 SQL::Statement and, at the current time, can not be over-ridden.
383
385 IMPORT()
386 CREATE TABLE foo AS IMPORT(?) ,{},$external_executed_sth
387 CREATE TABLE foo AS IMPORT(?) ,{},$AoA
388
390 There are a few built-in functions in the SQL::Statement::Functions.
391 If you make a generally useful UDF, why not submit it to me and have it
392 (and your name) included with the built-in functions? Please follow
393 the format shown in the module including a description of the arguments
394 and return values for the function as well as an example. Send them to
395 me at jzucker AT cpan.org with a subject line containing "built-in
396 UDF".
397 Thanks in advance :-).
399
401 Dean Arnold supplied DECODE, COALESCE, REPLACE, many thanks!
402
404 Copyright (c) 2005 by Jeff Zucker: jzuckerATcpan.org Copyright (c)
405 2009,2010 by Jens Rehsack: rehsackATcpan.org
406 All rights reserved.
408 The module may be freely distributed under the same terms as Perl
410 itself using either the "GPL License" or the "Artistic License" as
411 specified in the Perl README file.
412perl v5.12.1 2010-08-11 SQL::Statement::Functions(3)