---

9.2 Special Operators

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.