keelson_psql/
ops.rs

1use std::borrow::Cow;
2
3use keelson_core::expr::{Chain, Expr, IntoExpr, IntoExprList};
4
5/// The operators PostgreSQL has and the other two dialects do not.
6///
7/// An extension trait over [`Chain`] with a blanket impl, which is the shape
8/// `keelson_core::expr::chain` documents for exactly this: nothing in core changes,
9/// and the operators are reachable only where this trait is imported.
10///
11/// ```
12/// use keelson_psql::{PsqlOps, arg, quote};
13///
14/// let e = quote("title").ilike(arg("%rust%"));
15/// ```
16///
17/// Every method finishes through [`Chain::step`] or [`Chain::op`], so the
18/// parenthesisation rule is applied for it and a result never accumulates
19/// redundant parentheses.
20// The `is_*` predicates take `self` by value like every other operator: they are
21// SQL keywords being spelled out, not Rust predicates returning `bool`.
22#[allow(clippy::wrong_self_convention)]
23pub trait PsqlOps: Chain {
24    // -- pattern matching ----------------------------------------------------
25
26    /// `self ILIKE rhs` — `LIKE`, case-insensitively.
27    #[must_use]
28    fn ilike(self, rhs: impl IntoExpr) -> Self {
29        self.op("ILIKE", rhs)
30    }
31
32    /// `self NOT ILIKE rhs`.
33    #[must_use]
34    fn not_ilike(self, rhs: impl IntoExpr) -> Self {
35        self.op("NOT ILIKE", rhs)
36    }
37
38    /// `self NOT LIKE rhs`.
39    #[must_use]
40    fn not_like(self, rhs: impl IntoExpr) -> Self {
41        self.op("NOT LIKE", rhs)
42    }
43
44    /// `self SIMILAR TO rhs` — the SQL-standard regular-expression operator.
45    #[must_use]
46    fn similar_to(self, rhs: impl IntoExpr) -> Self {
47        self.op("SIMILAR TO", rhs)
48    }
49
50    /// `self NOT SIMILAR TO rhs`.
51    #[must_use]
52    fn not_similar_to(self, rhs: impl IntoExpr) -> Self {
53        self.op("NOT SIMILAR TO", rhs)
54    }
55
56    /// `self ~ rhs` — POSIX regular-expression match.
57    #[must_use]
58    fn matches(self, rhs: impl IntoExpr) -> Self {
59        self.op("~", rhs)
60    }
61
62    /// `self ~* rhs` — POSIX match, case-insensitively.
63    #[must_use]
64    fn imatches(self, rhs: impl IntoExpr) -> Self {
65        self.op("~*", rhs)
66    }
67
68    /// `self !~ rhs`.
69    #[must_use]
70    fn not_matches(self, rhs: impl IntoExpr) -> Self {
71        self.op("!~", rhs)
72    }
73
74    /// `self !~* rhs`.
75    #[must_use]
76    fn not_imatches(self, rhs: impl IntoExpr) -> Self {
77        self.op("!~*", rhs)
78    }
79
80    // -- ranges --------------------------------------------------------------
81
82    /// `self BETWEEN SYMMETRIC a AND b` — the bounds may be given either way
83    /// round.
84    #[must_use]
85    fn between_symmetric(self, a: impl IntoExpr, b: impl IntoExpr) -> Self {
86        self.step(move |lhs| {
87            Expr::join((lhs, Expr::raw("BETWEEN SYMMETRIC"), a, Expr::raw("AND"), b))
88        })
89    }
90
91    /// `self NOT BETWEEN SYMMETRIC a AND b`.
92    #[must_use]
93    fn not_between_symmetric(self, a: impl IntoExpr, b: impl IntoExpr) -> Self {
94        self.step(move |lhs| {
95            Expr::join((
96                lhs,
97                Expr::raw("NOT BETWEEN SYMMETRIC"),
98                a,
99                Expr::raw("AND"),
100                b,
101            ))
102        })
103    }
104
105    // -- containment, shared by arrays, ranges and jsonb ---------------------
106
107    /// `self @> rhs` — contains.
108    #[must_use]
109    fn contains(self, rhs: impl IntoExpr) -> Self {
110        self.op("@>", rhs)
111    }
112
113    /// `self <@ rhs` — is contained by.
114    #[must_use]
115    fn contained_by(self, rhs: impl IntoExpr) -> Self {
116        self.op("<@", rhs)
117    }
118
119    /// `self && rhs` — overlaps.
120    #[must_use]
121    fn overlaps(self, rhs: impl IntoExpr) -> Self {
122        self.op("&&", rhs)
123    }
124
125    /// `self @@ rhs` — full-text search match.
126    #[must_use]
127    fn text_search(self, rhs: impl IntoExpr) -> Self {
128        self.op("@@", rhs)
129    }
130
131    // -- json / jsonb --------------------------------------------------------
132
133    /// `self -> rhs` — the field or element, as `json`/`jsonb`.
134    #[must_use]
135    fn json_get(self, rhs: impl IntoExpr) -> Self {
136        self.op("->", rhs)
137    }
138
139    /// `self ->> rhs` — the field or element, as `text`.
140    #[must_use]
141    fn json_get_text(self, rhs: impl IntoExpr) -> Self {
142        self.op("->>", rhs)
143    }
144
145    /// `self #> rhs` — the value at a path, as `json`/`jsonb`.
146    #[must_use]
147    fn json_get_path(self, rhs: impl IntoExpr) -> Self {
148        self.op("#>", rhs)
149    }
150
151    /// `self #>> rhs` — the value at a path, as `text`.
152    #[must_use]
153    fn json_get_path_text(self, rhs: impl IntoExpr) -> Self {
154        self.op("#>>", rhs)
155    }
156
157    /// `self ? rhs` — does the top level contain this key.
158    ///
159    /// The `?` is written verbatim as an operator; it is never treated as a
160    /// placeholder, because only [`template`](keelson_core::expr::template)
161    /// rewrites those.
162    #[must_use]
163    fn json_has_key(self, rhs: impl IntoExpr) -> Self {
164        self.op("?", rhs)
165    }
166
167    /// `self ?| rhs` — any of these keys.
168    #[must_use]
169    fn json_has_any_key(self, rhs: impl IntoExpr) -> Self {
170        self.op("?|", rhs)
171    }
172
173    /// `self ?& rhs` — all of these keys.
174    #[must_use]
175    fn json_has_all_keys(self, rhs: impl IntoExpr) -> Self {
176        self.op("?&", rhs)
177    }
178
179    // -- quantified comparison ----------------------------------------------
180
181    /// `self = ANY (vals)` — true for at least one element.
182    ///
183    /// One operand is the usual case: an array-valued argument or a sub-query.
184    #[must_use]
185    fn eq_any(self, vals: impl IntoExprList) -> Self {
186        self.step(move |lhs| Expr::join((lhs, Expr::raw("= ANY"), Expr::group(vals))))
187    }
188
189    /// `self <> ALL (vals)` — true for every element.
190    #[must_use]
191    fn ne_all(self, vals: impl IntoExprList) -> Self {
192        self.step(move |lhs| Expr::join((lhs, Expr::raw("<> ALL"), Expr::group(vals))))
193    }
194
195    /// `self <op> ANY (vals)`, for an operator this trait does not name.
196    #[must_use]
197    fn any(self, op: &'static str, vals: impl IntoExprList) -> Self {
198        self.step(move |lhs| Expr::join((lhs, Expr::raw(op), Expr::raw("ANY"), Expr::group(vals))))
199    }
200
201    /// `self <op> ALL (vals)`, for an operator this trait does not name.
202    #[must_use]
203    fn all(self, op: &'static str, vals: impl IntoExprList) -> Self {
204        self.step(move |lhs| Expr::join((lhs, Expr::raw(op), Expr::raw("ALL"), Expr::group(vals))))
205    }
206
207    // -- three-valued boolean tests ------------------------------------------
208
209    /// `self IS TRUE`.
210    #[must_use]
211    fn is_true(self) -> Self {
212        self.step(|lhs| Expr::postfix(lhs, "IS TRUE"))
213    }
214
215    /// `self IS NOT TRUE`.
216    #[must_use]
217    fn is_not_true(self) -> Self {
218        self.step(|lhs| Expr::postfix(lhs, "IS NOT TRUE"))
219    }
220
221    /// `self IS FALSE`.
222    #[must_use]
223    fn is_false(self) -> Self {
224        self.step(|lhs| Expr::postfix(lhs, "IS FALSE"))
225    }
226
227    /// `self IS NOT FALSE`.
228    #[must_use]
229    fn is_not_false(self) -> Self {
230        self.step(|lhs| Expr::postfix(lhs, "IS NOT FALSE"))
231    }
232
233    /// `self IS UNKNOWN`.
234    #[must_use]
235    fn is_unknown(self) -> Self {
236        self.step(|lhs| Expr::postfix(lhs, "IS UNKNOWN"))
237    }
238
239    /// `self IS NOT UNKNOWN`.
240    #[must_use]
241    fn is_not_unknown(self) -> Self {
242        self.step(|lhs| Expr::postfix(lhs, "IS NOT UNKNOWN"))
243    }
244
245    // -- misc ----------------------------------------------------------------
246
247    /// `self::type_name` — PostgreSQL's cast shorthand.
248    ///
249    /// The type name is written verbatim, so `int`, `numeric(10, 2)` and
250    /// `text[]` all work. [`cast`](crate::cast) is the portable spelling.
251    #[must_use]
252    fn cast_to(self, type_name: impl Into<Cow<'static, str>>) -> Self {
253        let type_name = type_name.into();
254        self.step(move |lhs| Expr::join_with("", (lhs, Expr::raw("::"), Expr::raw(type_name))))
255    }
256
257    /// `self COLLATE "name"`.
258    #[must_use]
259    fn collate(self, name: impl Into<Cow<'static, str>>) -> Self {
260        let name = name.into();
261        self.step(move |lhs| Expr::join((lhs, Expr::raw("COLLATE"), Expr::ident(name))))
262    }
263
264    /// `self AT TIME ZONE zone`.
265    #[must_use]
266    fn at_time_zone(self, zone: impl IntoExpr) -> Self {
267        self.step(move |lhs| Expr::join((lhs, Expr::raw("AT TIME ZONE"), zone)))
268    }
269}
270
271impl<T: Chain> PsqlOps for T {}
272
273#[cfg(test)]
274mod tests {
275    use super::*;
276    use crate::{Psql, arg, quote};
277    use keelson_core::build;
278
279    fn sql(e: Expr) -> String {
280        build(&Psql, &e).expect("render").0
281    }
282
283    #[test]
284    fn every_operator_renders_with_one_set_of_parentheses() {
285        // Spellings taken from PostgreSQL 17 chapter 9 (Functions and Operators).
286        let cases = [
287            (quote("a").ilike(arg("x")), r#"("a" ILIKE $1)"#),
288            (quote("a").not_ilike(arg("x")), r#"("a" NOT ILIKE $1)"#),
289            (quote("a").not_like(arg("x")), r#"("a" NOT LIKE $1)"#),
290            (quote("a").similar_to(arg("x")), r#"("a" SIMILAR TO $1)"#),
291            (
292                quote("a").not_similar_to(arg("x")),
293                r#"("a" NOT SIMILAR TO $1)"#,
294            ),
295            (quote("a").matches(arg("x")), r#"("a" ~ $1)"#),
296            (quote("a").imatches(arg("x")), r#"("a" ~* $1)"#),
297            (quote("a").not_matches(arg("x")), r#"("a" !~ $1)"#),
298            (quote("a").not_imatches(arg("x")), r#"("a" !~* $1)"#),
299            (quote("a").contains(arg("x")), r#"("a" @> $1)"#),
300            (quote("a").contained_by(arg("x")), r#"("a" <@ $1)"#),
301            (quote("a").overlaps(arg("x")), r#"("a" && $1)"#),
302            (quote("a").text_search(arg("x")), r#"("a" @@ $1)"#),
303            (quote("a").json_get(arg("x")), r#"("a" -> $1)"#),
304            (quote("a").json_get_text(arg("x")), r#"("a" ->> $1)"#),
305            (quote("a").json_get_path(arg("x")), r#"("a" #> $1)"#),
306            (quote("a").json_get_path_text(arg("x")), r#"("a" #>> $1)"#),
307            (quote("a").json_has_key(arg("x")), r#"("a" ? $1)"#),
308            (quote("a").json_has_any_key(arg("x")), r#"("a" ?| $1)"#),
309            (quote("a").json_has_all_keys(arg("x")), r#"("a" ?& $1)"#),
310            (quote("a").is_true(), r#"("a" IS TRUE)"#),
311            (quote("a").is_not_true(), r#"("a" IS NOT TRUE)"#),
312            (quote("a").is_false(), r#"("a" IS FALSE)"#),
313            (quote("a").is_not_false(), r#"("a" IS NOT FALSE)"#),
314            (quote("a").is_unknown(), r#"("a" IS UNKNOWN)"#),
315            (quote("a").is_not_unknown(), r#"("a" IS NOT UNKNOWN)"#),
316        ];
317        for (e, expected) in cases {
318            assert_eq!(sql(e), expected);
319        }
320    }
321
322    #[test]
323    fn the_multi_token_operators_keep_their_shape() {
324        assert_eq!(
325            sql(quote("a").between_symmetric(arg(1i32), arg(2i32))),
326            r#"("a" BETWEEN SYMMETRIC $1 AND $2)"#
327        );
328        assert_eq!(
329            sql(quote("a").not_between_symmetric(arg(1i32), arg(2i32))),
330            r#"("a" NOT BETWEEN SYMMETRIC $1 AND $2)"#
331        );
332        assert_eq!(sql(quote("a").eq_any(arg(1i32))), r#"("a" = ANY ($1))"#);
333        assert_eq!(sql(quote("a").ne_all(arg(1i32))), r#"("a" <> ALL ($1))"#);
334        assert_eq!(sql(quote("a").any(">", arg(1i32))), r#"("a" > ANY ($1))"#);
335        assert_eq!(sql(quote("a").all("<", arg(1i32))), r#"("a" < ALL ($1))"#);
336    }
337
338    #[test]
339    fn cast_shorthand_has_no_spaces_and_collate_quotes_its_name() {
340        assert_eq!(sql(quote("a").cast_to("int")), r#"("a"::int)"#);
341        assert_eq!(sql(quote("a").collate("C")), r#"("a" COLLATE "C")"#);
342        assert_eq!(
343            sql(quote("a").at_time_zone(arg("UTC"))),
344            r#"("a" AT TIME ZONE $1)"#
345        );
346    }
347}
keelson_psql/ops.rs

keelson_psql/
ops.rs
