Next: Lookup Tables Extension, Previous: Prefix-Style Expressions, Up: Query Construction [Contents][Index]
Some operators are handled specially:
and
or
These infix operators are very common and are only mentioned because they do not show up in the lookup tables. (Do not be alarmed!)
(case PEXP (MAYBE1 VAL1) ... [(else DEFAULT-VAL)])
This maps to the SQL CASE
statement:
CASE exp WHEN maybe1 THEN val1 ... ELSE default END
where the changes from PEXP
, MAYBE1
, VAL1
and
DEFAULT-VAL
to exp
, maybe1
, val1
and
default
, respectively, involve recursive layout transforms.
(cond (MAYBE1 VAL1) ... [(else DEFAULT-VAL)])
This maps to the SQL CASE
statement without the initial pexp:
CASE WHEN maybe1 THEN val1 ... ELSE default END
where the changes from MAYBE1
, VAL1
and
DEFAULT-VAL
to maybe1
, val1
and
default
, respectively, involve recursive layout transforms.
(if PEXP YES-VAL NO-VAL)
This maps to the SQL CASE
statement:
CASE exp WHEN 't' THEN yes WHEN 'f' THEN no END
where the changes from PEXP
, YES-VAL
and
NO-VAL
to exp
, yes
and
no
, respectively, involve recursive layout transforms.
Note that one-armed if is not permitted.
(:: TYPE PEXP)
This maps to the SQL CAST
statement:
CAST exp AS type
where the change from PEXP
to exp
involves a recursive layout
transform. Note that the result is identical to using (SQL) suffix
::TYPE
; the operator ::
was actually chosen with this
mnemonic in mind.
If TYPE
is a string, it is used directly for type
.
Otherwise, it should be a symbol, in which case type
is the result of calling type-sql-name
on TYPE
(see Consulting Existing Converters).
(in/set PEXP0 PEXP1...)
This maps to the SQL expression:
exp0 IN ( exp1, ... )
where the change from each PEXP
to exp
involves a recursive
layout transform. Parentheses and comma are added automatically.
For NOT IN
, use (not (in/set ...))
.
The name in/set
was chosen to emphasize that this particular usage
of the SQL keyword IN
is to check for set membership. (The
operator in/sel
is reserved for future implementation of IN
as sub-select.)
(between PEXP PEXP-LO PEXP-HI)
This maps to the SQL expression:
( exp BETWEEN exp-lo AND exp-hi )
where the change from each PEXP
to exp
involves a recursive
layout transform. Parentheses are added automatically.
For NOT BETWEEN
, use (not (between ...))
.
(any--op PEXP ARRAY-PEXP)
(all--op PEXP ARRAY-PEXP)
These map to the respective SQL expressions:
( exp op ANY array-exp ) ( exp op ALL array-exp )
where op is a valid comparison operator (e.g., =
),
and the change from PEXP
and ARRAY-PEXP
to exp
and
array-exp
, respectively, involves a recursive layout transform.
Parentheses are added automatically.
The --
syntax was chosen to avoid potential namespace conflicts;
according the PostgreSQL documentation (section 4.1.3, Operators):
There are a few restrictions on operator names, however:
--
and/*
cannot appear anywhere in an operator name, since they will be taken as the start of a comment.
A symbol appearing in the operands, or alone without parentheses (i.e., in a non-operator position), it is taken to be a column name or reference.
Strings are always passed through sql-quote
unless they have been
previously been passed through sql-pre
.
Numbers are passed through w/o change.
The documentation for the procedures described in this chapter often uses pexp to stand for prefix-style expression. Some procedures take a pexp (or list of them), while others take a more complex structure usually involving some extra specification composed with a single pexp.
Next: Lookup Tables Extension, Previous: Prefix-Style Expressions, Up: Query Construction [Contents][Index]