6.2.3 The #:where clause
The #:where clause lets you filter the data selected by the #:from clause. The clause takes the form of a Boolean expression that specifies whether any given row should be kept in the final result.
The expression language, which is a subset of the query language, is described below. Expressions are used in various clauses besides #:where, including the #:distinct and #:having clauses and join conditions.
These forms are essentially wrappers for SQL expressions, so SQL semantics prevail over Scheme semantics. For example, = can be used to compare values of any type.
6.2.3.1 Expression types
Each expression has a type, which is one of the following:
Boolean;
numeric, with integer and real subtypes;
character, with string and symbol subtypes;
temporal, with time-utc and time-tai subtypes.
The procedures below all operate on Scheme literals and other expressions. Scheme literals are automatically quoted as expressions of the relevant type. The type of an expression is determined by the types of its arguments.
The complete #:where clause must be of type Boolean.
6.2.3.2 Boolean operators
These functions operate on Boolean arguments and produce Boolean results:
(or arg )
The Boolean OR of the arguments.
(and arg )
The Boolean AND of the arguments.
(not arg)
The Boolean negation of the argument.
6.2.3.3 Comparison functions
These functions operate on numeric, Boolean, string, symbolic or temporal arguments, as long as consistent types are used. For example, (= "str" 'str) is a valid comparison whereas (= "str" 123) is not. The result type is always Boolean.
Equality and inequality predicates perform equal? style (i.e. whole-content) comparison on their arguments:
(= arg1 arg2)
(<> arg1 arg2)
Comparison functions perform numerical, lexical or temporal comparisons as appropriate:
(< arg1 arg2)
(> arg1 arg2)
(<= arg1 arg2)
(>= arg1 arg2)
6.2.3.4 Mathematical functions
Addition and subtraction can be used on numeric or temporal arguments, producing a numeric or temporal result as appropriate:
(+ arg )
(- arg )
Other mathematical operators can be applied to numeric arguments only:
(* arg )
(/ arg1 arg2)
(abs arg)
(floor arg)
(ceiling arg)
(round arg)
6.2.3.5 Character pattern matching functions
Pattern matching in PostgreSQL
Pattern matching functions take character arguments and return Boolean results:
(like str pattern)
The SQL LIKE function with support for "%" and "_" wildcards in the pattern.
(regexp-match str pattern)
POSIX regular expression pattern matching (the "~" operator in PostgreSQL).
(regexp-match-ci str pattern)
Case insensitive POSIX regular expression matching (the "~*" operator in PostgreSQL).
6.2.3.6 Character manipulation functions
The following functions operator on character (string/symbol) arguments. If all arguments are symbols the result is a symbol, otherwise it is a string:
(string-append arg )
Concatenates the args.
(string-replace haystack needle replacement)
Searches for needle in haystack and replaces all occurrences with replacement.
(regexp-replace haystack pattern replacement)
Searches for the POSIX regular expression pattern in haystack and replaces the first occurrence with replacement.
(regexp-replace* haystack pattern replacement)
Like regexp-replace but replaces all occurrences of pattern rather than just the first.
(regexp-replace-ci haystack pattern replacement)
Case insensitive version of regexp-replace.
(regexp-replace*-ci haystack pattern replacement)
Case insensitive version of regexp-replace*.
(->string datum format-string)
Equivalent of PostgreSQL’s TO_CHAR fucntion: converts any data type to a string. format-string is a character expression that specifies the format to use in the conversion (see the PostgreSQL documentation for examples).
(->symbol datum format-string)
Like ->string but returns a symbol result.
6.2.3.7 Conditional functions
(if test then)
(if test then else)
Performs an if-then-else test on its arguments. test must be Boolean valued. If test evaluates to #t, the value of then is calculated and returned. If test evaluates to #f, the value of else is calucated and returned instead. If test evaluates to #f and else is omitted, NULL is returned.
then and else must have the same type: the return value is a function of the same type.
(cond clause )
clause
=
[test-expr value-expr]
|
[else value-expr]
cond-like syntax that expands into a chain of if expressions. All value-exprs must have the same types.
6.2.3.8 Functions related to NULL
(null? arg)
Determines whether the argument is NULL. Direct comparison with NULL using = does not work because in SQL semantics NULL is not equal to itself (see null? instead).
(coalesce arg )
Returns the value of the leftmost non-NULL argument. All arguments must be of compatible types.
6.2.3.9 The IN function
(in needle haystack)
Equivalent of the SQL IN function that searches for needle in haystack. Returns #t if it is found or #f otherwise.
haystack can be one of the following:
a list of Scheme literals that can be quoted as literal expressions;
a query with a single #:what item.
6.2.3.10 Aggregate functions
Aggregate functions in PostgreSQL
Snooze supports the four most common SQL aggregate functions applied to individual columns: COUNT, MAX, MIN and AVERAGE. There is also a variant of COUNT that can be applied to whole entities and subqueries (the equivalent of COUNT(*)).
There are many subtleties to the use of aggregate functions in SQL that Snooze does not concern itself with. For example, different DBMSs support different types of argument for each aggregate function. If your DBMS rejects a query for any reason, Snooze will raise exn:fail and provide you with an appropriate error message.
(count arg)
Returns the number of non-NULL values in the argument.
(max arg)
Returns the maximum value in the argument.
(min arg)
Returns the minimum value in the specified argument.
(average arg)
Returns the mean value in the specified argument.
(count*)
(count* arg)
]{ Returns the number of non-null rows from the specified entity or subquery (equivalent to the SQL "COUNT(foo.*)"). If arg is omitted or #f, the function returns the number of non-null rows from the current query (equivalent to the SQL "COUNT(*)").}
If you list aggregate and non-aggregate results in the #:what clause of a query, you may additionally have to specify a #:group clause to determine how the results are aggregated.