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
49 your own user-defined functions (UDFs). In order to use a UDF in a
50 script, you first have to create a perl subroutine (see below), then
51 you need to make the function available to your database handle with
52 the CREATE FUNCTION or LOAD commands:
53 # load a single function "foo"from a subroutine
55 # named "foo" in the current package
56 $dbh->do(" CREATE FUNCTION foo EXTERNAL ");
58 # load a single function "foo" from a subroutine
60 # named "bar" in the current package
61 $dbh->do(" CREATE FUNCTION foo EXTERNAL NAME bar");
63 # 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. Sub‐
74 routines loaded with CREATE FUNCTION can have any valied perl subrour‐
75 inte name. Subroutines loaded with LOAD must start with SQL_FUNCTION_
76 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
92 appropriate to the context of the function in a SQL statement. For
93 example the built-in CURRENT_TIME returns a string value and therefore
94 may beused anywhere in a SQL statement that a string value can. Here'
95 the entire perl code for the function:
96 # CURRENT_TIME
98 #
99 # arguments : none
100 # returns : string containing current time as hh::mm::ss
101 #
102 sub SQL_FUNCTION_CURRENT_TIME {
103 sprintf "%02s::%02s::%02s",(localtime)[2,1,0]
104 }
105 More complex functions can make use of a number of arguments always
107 passed to functions automatically. Functions always recieve these val‐
108 ues in @_:
109 sub FOO {
111 my($self,$sth,$rowhash,@params);
112 }
113 The first argument, $self, is whatever class the function is defined
115 in, not generally useful unless you have an entire module to support
116 the function.
117 The second argument, $sth is the active statement handle of the current
119 statement. Like all active statement handles it contains the current
120 database handle in the {Database} attribute so you can have access to
121 the database handle in any function:
122 sub FOO {
124 my($self,$sth,$rowhash,@params);
125 my $dbh = $sth->{Database};
126 # $dbh->do( ...), etc.
127 }
128 In actual practice you probably want to use $sth-{Database} directly
130 rather than making a local copy, so $sth->{Database}->do(...).
131 The third argument, $rowhash, is a reference to a hash containing the
133 key/value pairs for the current database row the SQL is searching.
134 This isn't relevant for something like CURRENT_TIME which isn't based
135 on a SQL search, but here's an example of a (rather useless) UDF using
136 $rowhash that just joins the values for the entire row with a colon:
137 sub COLON_JOIN {
139 my($self,$sth,$rowhash,@params);
140 my $str = join ':', values %$rowhash;
141 }
142 The remaining arguments, @params, are aguements passed by users to the
144 function, either directly or with placeholders; another silly example
145 which just returns the results of multiplying the arguments passed to
146 it:
147 sub MULTIPLY {
149 my($self,$sth,$rowhash,@params);
150 return $params[0] * $params[1];
151 }
152 # first make the function available
154 #
155 $dbh->do("CREATE FUNCTION MULTIPLY");
156 # then multiply col3 in each row times seven
158 #
159 my $sth=$dbh->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,7)");
160 $sth->execute;
161 #
162 # or
163 #
164 my $sth=$dbh->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,?)");
165 $sth->execute(7);
166 Creating In-Memory Tables with functions
168 A function can return almost anything, as long is it is an appropriate
170 return for the context the function will be used in. In the special
171 case of table-returning functions, the function should return a refer‐
172 ence to an array of array references with the first row being the col‐
173 umn names and the remaining rows the data. For example:
174 1. create a function that returns an AoA,
176 sub Japh {[
178 [qw( id word )],
179 [qw( 1 Hacker )],
180 [qw( 2 Perl )],
181 [qw( 3 Another )],
182 [qw( 4 Just )],
183 ]}
184 2. make your database handle aware of the function
186 $dbh->do("CREATE FUNCTION 'Japh');
188 3. Access the data in the AoA from SQL
190 $sth = $dbh->prepare("SELECT word FROM Japh ORDER BY id DESC");
192 Or here's an example that does a join on two in-memory tables:
194 sub Prof {[ [qw(pid pname)],[qw(1 Sue )],[qw(2 Bob)],[qw(3 Tom )] ]}
196 sub Class {[ [qw(pid cname)],[qw(1 Chem)],[qw(2 Bio)],[qw(2 Math)] ]}
197 $dbh->do("CREATE FUNCTION $_) for qw(Prof Class);
198 $sth = $dbh->prepare("SELECT * FROM Prof NATURAL JOIN Class");
199 The "Prof" and "Class" functions return tables which can be used like
201 any SQL table.
202 More complex functions might do something like scrape an RSS feed, or
204 search a file system and put the results in AoA. For example, to
205 search a directory with SQL:
206 sub Dir {
208 my($self,$sth,$rowhash,$dir)=@_;
209 opendir D, $dir or die "'$dir':$!";
210 my @files = readdir D;
211 my $data = [[qw(fileName fileExt)]];
212 for (@files) {
213 my($fn,$ext) = /^(.*)(\.[^\.]+)$/;
214 push @$data, [$fn,$ext];
215 }
216 return $data;
217 }
218 $dbh->do("CREATE FUNCTION Dir");
219 printf "%s\n", join' ',@{ $dbh->selectcol_arrayref("
220 SELECT fileName FROM Dir('./') WHERE fileExt = '.pl'
221 ")};
222 Obviously, that function could be expanded with File::Find and/or stat
224 to provide more information and it could be made to accept a list of
225 directories rather than a single directory.
226 Table-Returning functions are a way to turn *anything* that can be mod‐
228 eled as an AoA into a DBI data source.
229
231 Aggregate Functions
232 min, max, avg, sum, count
234 Aggregate functions are handled elsewhere, see SQL::Parser for documen‐
236 tation.
237 Date and Time Functions
239 current_date, current_time, current_timestamp
241 CURRENT_DATE
243 # purpose : find current date
245 # arguments : none
246 # returns : string containing current date as yyyy-mm-dd
247 CURRENT_TIME
249 # purpose : find current time
251 # arguments : none
252 # returns : string containing current time as hh::mm::ss
253 CURRENT_TIMESTAMP
255 # purpose : find current date and time
257 # arguments : none
258 # returns : string containing current timestamp as yyyy-mm-dd hh::mm::ss
259 String Functions
261 char_length, lower, position, regex, soundex, substring, trim, upper
263 CHAR_LENGTH
265 # purpose : find length in characters of a string
267 # arguments : a string
268 # returns : a number - the length of the string in characters
269 LOWER & UPPER
271 # purpose : lower-case or upper-case a string
273 # arguments : a string
274 # returns : the sting lower or upper cased
275 POSITION
277 # purpose : find first position of a substring in a string
279 # arguments : a substring and a string possibly containing the substring
280 # returns : a number - the index of the substring in the string
281 # or 0 if the substring doesn't occur in the sring
282 REGEX
284 # purpose : test if a string matches a perl regular expression
286 # arguments : a string and a regex to match the string against
287 # returns : boolean value of the regex match
288 #
289 # example : ... WHERE REGEX(col3,'/^fun/i') ... matches rows
290 # in which col3 starts with "fun", ignoring case
291 SOUNDEX
293 # purpose : test if two strings have matching soundex codes
295 # arguments : two strings
296 # returns : true if the strings share the same soundex code
297 #
298 # example : ... WHERE SOUNDEX(col3,'fun') ... matches rows
299 # in which col3 is a soundex match for "fun"
300 CONCAT
302 # purpose : concatenate 1 or more strings into a single string;
304 # an alternative to the '⎪⎪' operator
305 # arguments : 1 or more strings
306 # returns : the concatenated string
307 #
308 # example : SELECT CONCAT(first_string, 'this string', ' that string')
309 # returns "<value-of-first-string>this string that string"
310 # note : if any argument evaluates to NULL, the returned value is NULL
311 COALESCE aka NVL
313 # purpose : return the first non-NULL value from a list
315 # arguments : 1 or more expressions
316 # returns : the first expression (reading left to right)
317 # which is not NULL; returns NULL if all are NULL
318 #
319 # example : SELECT COALESCE(NULL, some_null_column, 'not null')
320 # returns 'not null'
321 DECODE
323 # purpose : compare the first argument against
325 # succeding arguments at position 1 + 2N
326 # (N = 0 to (# of arguments - 2)/2), and if equal,
327 # return the value of the argument at 1 + 2N + 1; if no
328 # arguments are equal, the last argument value is returned
329 # arguments : 4 or more expressions, must be even # of arguments
330 # returns : the value of the argument at 1 + 2N + 1 if argument 1 + 2N
331 # is equal to argument1; else the last argument value
332 #
333 # example : SELECT DECODE(some_column,
334 # 'first value', 'first value matched'
335 # '2nd value', '2nd value matched'
336 # 'no value matched'
337 # )
338 REPLACE, SUBSTITUTE
340 # purpose : perform perl subsitution on input string
342 # arguments : a string and a substitute pattern string
343 # returns : the result of the substitute operation
344 #
345 # example : ... WHERE REPLACE(col3,'s/fun(\w+)nier/$1/ig') ... replaces
346 # all instances of /fun(\w+)nier/ in col3 with the string
347 # between 'fun' and 'nier'
348 SUBSTRING
350 SUBSTRING( string FROM start_pos [FOR length] )
352 Returns the substring starting at start_pos and extending for "length"
354 character or until the end of the string, if no "length" is supplied.
355 Examples:
356 SUBSTRING( 'foobar' FROM 4 ) # returns "bar"
358 SUBSTRING( 'foobar' FROM 4 FOR 2) # returns "ba"
360 Note: The SUBSTRING function is implemented in SQL::Parser and
362 SQL::Statement and, at the current time, can not be over-ridden.
363 TRIM
365 TRIM ( [ [LEADING⎪TRAILING⎪BOTH] ['trim_char'] FROM ] string )
367 Removes all occurrences of <trim_char> from the front, back, or both
369 sides of a string.
370 BOTH is the default if neither LEADING nor TRAILING is specified.
372 Space is the default if no trim_char is specified.
374 Examples:
376 TRIM( string )
378 trims leading and trailing spaces from string
379 TRIM( LEADING FROM str )
381 trims leading spaces from string
382 TRIM( 'x' FROM str )
384 trims leading and trailing x's from string
385 Note: The TRIM function is implemented in SQL::Parser and SQL::State‐
387 ment and, at the current time, can not be over-ridden.
388
390 IMPORT()
391 CREATE TABLE foo AS IMPORT(?) ,{},$external_executed_sth
393 CREATE TABLE foo AS IMPORT(?) ,{},$AoA
394
396 There are a few built-in functions in the SQL::Statement::Functions.
397 If you make a generally useful UDF, why not submit it to me and have it
398 (and your name) included with the built-in functions? Please follow
399 the format shown in the module including a description of the arguments
400 and return values for the function as well as an example. Send them to
401 me at jzucker AT cpan.org with a subject line containing "built-in
402 UDF".
403 Thanks in advance :-).
405
407 Dean Arnold supplied DECODE, COALESCE, REPLACE, many thanks!
408
410 This module is copyright (c) 2005 by Jeff Zucker. All rights reserved.
411 The module may be freely distributed under the same terms as Perl
413 itself using either the "GPL License" or the "Artistic License" as
414 specified in the Perl README file.
415 Jeff can be reached at: jzucker AT cpan.org
417perl v5.8.8 2005-04-18 SQL::Statement::Functions(3)