sea_query/extension/postgres/func.rs
1//! For calling built-in Postgres SQL functions.
2
3use crate::{PgDateTruncUnit, expr::*, func::*};
4
5pub mod json_exists;
6pub mod json_fn;
7pub mod json_query;
8pub mod json_table;
9pub mod json_value;
10
11/// Known Postgres-specific functions.
12///
13/// For all supported functions (including the standard ones), see [`Function`].
14///
15/// If something is not supported, you can use [`Function::Custom`].
16#[derive(Debug, Clone, PartialEq)]
17#[non_exhaustive]
18pub enum PgFunc {
19 ToTsquery,
20 ToTsvector,
21 PhrasetoTsquery,
22 PlaintoTsquery,
23 WebsearchToTsquery,
24 TsRank,
25 TsRankCd,
26 StartsWith,
27 GenRandomUUID,
28 JsonBuildObject,
29 JsonAgg,
30 ArrayAgg,
31 DateTrunc,
32 Any,
33 Some,
34 All,
35}
36
37/// Type alias of [`PgFunc`] for compatibility.
38/// Previously, [`PgFunc`] is a namespace for building [`FunctionCall`].
39#[deprecated(since = "1.0.0", note = "use `PgFunc` instead")]
40pub type PgFunction = PgFunc;
41
42impl PgFunc {
43 /// Call `TO_TSQUERY` function. Postgres only.
44 ///
45 /// The parameter `regconfig` represents the OID of the text search configuration.
46 /// If the value is `None` the argument is omitted from the query, and hence the database default used.
47 ///
48 /// # Examples
49 ///
50 /// ```
51 /// use sea_query::{tests_cfg::*, *};
52 ///
53 /// let query = Query::select()
54 /// .expr(PgFunc::to_tsquery("a & b", None))
55 /// .to_owned();
56 ///
57 /// assert_eq!(
58 /// query.to_string(PostgresQueryBuilder),
59 /// r#"SELECT TO_TSQUERY('a & b')"#
60 /// );
61 /// ```
62 pub fn to_tsquery<T>(expr: T, regconfig: Option<u32>) -> FunctionCall
63 where
64 T: Into<Expr>,
65 {
66 match regconfig {
67 Some(config) => {
68 let config = Expr::Value(config.into());
69 FunctionCall::new(Func::PgFunction(PgFunc::ToTsquery)).args([config, expr.into()])
70 }
71 None => FunctionCall::new(Func::PgFunction(PgFunc::ToTsquery)).arg(expr),
72 }
73 }
74
75 /// Call `TO_TSVECTOR` function. Postgres only.
76 ///
77 /// The parameter `regconfig` represents the OID of the text search configuration.
78 /// If the value is `None` the argument is omitted from the query, and hence the database default used.
79 ///
80 /// # Examples
81 ///
82 /// ```
83 /// use sea_query::{tests_cfg::*, *};
84 ///
85 /// let query = Query::select()
86 /// .expr(PgFunc::to_tsvector("a b", None))
87 /// .to_owned();
88 ///
89 /// assert_eq!(
90 /// query.to_string(PostgresQueryBuilder),
91 /// r#"SELECT TO_TSVECTOR('a b')"#
92 /// );
93 /// ```
94 pub fn to_tsvector<T>(expr: T, regconfig: Option<u32>) -> FunctionCall
95 where
96 T: Into<Expr>,
97 {
98 match regconfig {
99 Some(config) => {
100 let config = Expr::Value(config.into());
101 FunctionCall::new(Func::PgFunction(PgFunc::ToTsvector)).args([config, expr.into()])
102 }
103 None => FunctionCall::new(Func::PgFunction(PgFunc::ToTsvector)).arg(expr),
104 }
105 }
106
107 /// Call `PHRASE_TO_TSQUERY` function. Postgres only.
108 ///
109 /// The parameter `regconfig` represents the OID of the text search configuration.
110 /// If the value is `None` the argument is omitted from the query, and hence the database default used.
111 ///
112 /// # Examples
113 ///
114 /// ```
115 /// use sea_query::{tests_cfg::*, *};
116 ///
117 /// let query = Query::select()
118 /// .expr(PgFunc::phraseto_tsquery("a b", None))
119 /// .to_owned();
120 ///
121 /// assert_eq!(
122 /// query.to_string(PostgresQueryBuilder),
123 /// r#"SELECT PHRASETO_TSQUERY('a b')"#
124 /// );
125 /// ```
126 pub fn phraseto_tsquery<T>(expr: T, regconfig: Option<u32>) -> FunctionCall
127 where
128 T: Into<Expr>,
129 {
130 match regconfig {
131 Some(config) => {
132 let config = Expr::Value(config.into());
133 FunctionCall::new(Func::PgFunction(PgFunc::PhrasetoTsquery))
134 .args([config, expr.into()])
135 }
136 None => FunctionCall::new(Func::PgFunction(PgFunc::PhrasetoTsquery)).arg(expr),
137 }
138 }
139
140 /// Call `PLAIN_TO_TSQUERY` function. Postgres only.
141 ///
142 /// The parameter `regconfig` represents the OID of the text search configuration.
143 /// If the value is `None` the argument is omitted from the query, and hence the database default used.
144 ///
145 /// # Examples
146 ///
147 /// ```
148 /// use sea_query::{tests_cfg::*, *};
149 ///
150 /// let query = Query::select()
151 /// .expr(PgFunc::plainto_tsquery("a b", None))
152 /// .to_owned();
153 ///
154 /// assert_eq!(
155 /// query.to_string(PostgresQueryBuilder),
156 /// r#"SELECT PLAINTO_TSQUERY('a b')"#
157 /// );
158 /// ```
159 pub fn plainto_tsquery<T>(expr: T, regconfig: Option<u32>) -> FunctionCall
160 where
161 T: Into<Expr>,
162 {
163 match regconfig {
164 Some(config) => {
165 let config = Expr::Value(config.into());
166 FunctionCall::new(Func::PgFunction(PgFunc::PlaintoTsquery))
167 .args([config, expr.into()])
168 }
169 None => FunctionCall::new(Func::PgFunction(PgFunc::PlaintoTsquery)).arg(expr),
170 }
171 }
172
173 /// Call `WEBSEARCH_TO_TSQUERY` function. Postgres only.
174 ///
175 /// The parameter `regconfig` represents the OID of the text search configuration.
176 /// If the value is `None` the argument is omitted from the query, and hence the database default used.
177 ///
178 /// # Examples
179 ///
180 /// ```
181 /// use sea_query::{tests_cfg::*, *};
182 ///
183 /// let query = Query::select()
184 /// .expr(PgFunc::websearch_to_tsquery("a b", None))
185 /// .to_owned();
186 ///
187 /// assert_eq!(
188 /// query.to_string(PostgresQueryBuilder),
189 /// r#"SELECT WEBSEARCH_TO_TSQUERY('a b')"#
190 /// );
191 /// ```
192 pub fn websearch_to_tsquery<T>(expr: T, regconfig: Option<u32>) -> FunctionCall
193 where
194 T: Into<Expr>,
195 {
196 match regconfig {
197 Some(config) => {
198 let config = Expr::Value(config.into());
199 FunctionCall::new(Func::PgFunction(PgFunc::WebsearchToTsquery))
200 .args([config, expr.into()])
201 }
202 None => FunctionCall::new(Func::PgFunction(PgFunc::WebsearchToTsquery)).arg(expr),
203 }
204 }
205
206 /// Call `TS_RANK` function. Postgres only.
207 ///
208 /// # Examples
209 ///
210 /// ```
211 /// use sea_query::{tests_cfg::*, *};
212 ///
213 /// let query = Query::select()
214 /// .expr(PgFunc::ts_rank("a b", "a&b"))
215 /// .to_owned();
216 ///
217 /// assert_eq!(
218 /// query.to_string(PostgresQueryBuilder),
219 /// r#"SELECT TS_RANK('a b', 'a&b')"#
220 /// );
221 /// ```
222 pub fn ts_rank<T>(vector: T, query: T) -> FunctionCall
223 where
224 T: Into<Expr>,
225 {
226 FunctionCall::new(Func::PgFunction(PgFunc::TsRank)).args([vector.into(), query.into()])
227 }
228
229 /// Call `TS_RANK_CD` function. Postgres only.
230 ///
231 /// # Examples
232 ///
233 /// ```
234 /// use sea_query::{tests_cfg::*, *};
235 ///
236 /// let query = Query::select()
237 /// .expr(PgFunc::ts_rank_cd("a b", "a&b"))
238 /// .to_owned();
239 ///
240 /// assert_eq!(
241 /// query.to_string(PostgresQueryBuilder),
242 /// r#"SELECT TS_RANK_CD('a b', 'a&b')"#
243 /// );
244 /// ```
245 pub fn ts_rank_cd<T>(vector: T, query: T) -> FunctionCall
246 where
247 T: Into<Expr>,
248 {
249 FunctionCall::new(Func::PgFunction(PgFunc::TsRankCd)).args([vector.into(), query.into()])
250 }
251
252 /// Call `ANY` function. Postgres only.
253 ///
254 /// # Examples
255 ///
256 /// ```
257 /// #[cfg(feature = "postgres-array")]
258 /// # {
259 /// use sea_query::{tests_cfg::*, *};
260 ///
261 /// let query = Query::select().expr(PgFunc::any(vec![0, 1])).to_owned();
262 ///
263 /// assert_eq!(
264 /// query.to_string(PostgresQueryBuilder),
265 /// r#"SELECT ANY(ARRAY [0,1])"#
266 /// );
267 /// # }
268 /// ```
269 /// ```
270 /// use sea_query::{tests_cfg::*, *};
271 ///
272 /// let query = Query::select()
273 /// .column("id")
274 /// .from("post")
275 /// .and_where(Expr::col("tag").eq(PgFunc::any(Expr::cust("string_to_array('a,b,c', ',')"))))
276 /// .to_owned();
277 ///
278 /// assert_eq!(
279 /// query.to_string(PostgresQueryBuilder),
280 /// r#"SELECT "id" FROM "post" WHERE "tag" = ANY(string_to_array('a,b,c', ','))"#
281 /// );
282 /// ```
283 pub fn any<T>(expr: T) -> FunctionCall
284 where
285 T: Into<Expr>,
286 {
287 FunctionCall::new(Func::PgFunction(PgFunc::Any)).arg(expr)
288 }
289
290 /// Call `SOME` function. Postgres only.
291 ///
292 /// # Examples
293 ///
294 /// ```
295 /// #[cfg(feature = "postgres-array")]
296 /// # {
297 /// use sea_query::{tests_cfg::*, *};
298 ///
299 /// let query = Query::select().expr(PgFunc::some(vec![0, 1])).to_owned();
300 ///
301 /// assert_eq!(
302 /// query.to_string(PostgresQueryBuilder),
303 /// r#"SELECT SOME(ARRAY [0,1])"#
304 /// );
305 /// # }
306 /// ```
307 pub fn some<T>(expr: T) -> FunctionCall
308 where
309 T: Into<Expr>,
310 {
311 FunctionCall::new(Func::PgFunction(PgFunc::Some)).arg(expr)
312 }
313
314 /// Call `ALL` function. Postgres only.
315 ///
316 /// # Examples
317 ///
318 /// ```
319 /// #[cfg(feature = "postgres-array")]
320 /// # {
321 /// use sea_query::{tests_cfg::*, *};
322 ///
323 /// let query = Query::select().expr(PgFunc::all(vec![0, 1])).to_owned();
324 ///
325 /// assert_eq!(
326 /// query.to_string(PostgresQueryBuilder),
327 /// r#"SELECT ALL(ARRAY [0,1])"#
328 /// );
329 /// # }
330 /// ```
331 pub fn all<T>(expr: T) -> FunctionCall
332 where
333 T: Into<Expr>,
334 {
335 FunctionCall::new(Func::PgFunction(PgFunc::All)).arg(expr)
336 }
337
338 /// Call `STARTS_WITH` function. Postgres only.
339 ///
340 /// # Examples
341 ///
342 /// ```
343 /// use sea_query::{tests_cfg::*, *};
344 ///
345 /// let query = Query::select()
346 /// .expr(PgFunc::starts_with("123", "1"))
347 /// .to_owned();
348 ///
349 /// assert_eq!(
350 /// query.to_string(PostgresQueryBuilder),
351 /// r#"SELECT STARTS_WITH('123', '1')"#
352 /// );
353 /// ```
354 pub fn starts_with<T, P>(text: T, prefix: P) -> FunctionCall
355 where
356 T: Into<Expr>,
357 P: Into<Expr>,
358 {
359 FunctionCall::new(Func::PgFunction(PgFunc::StartsWith)).args([text.into(), prefix.into()])
360 }
361
362 /// Call `GEN_RANDOM_UUID` function. Postgres only.
363 ///
364 /// # Examples
365 ///
366 /// ```
367 /// use sea_query::{tests_cfg::*, *};
368 ///
369 /// let query = Query::select().expr(PgFunc::gen_random_uuid()).to_owned();
370 ///
371 /// assert_eq!(
372 /// query.to_string(PostgresQueryBuilder),
373 /// r#"SELECT GEN_RANDOM_UUID()"#
374 /// );
375 /// ```
376 pub fn gen_random_uuid() -> FunctionCall {
377 FunctionCall::new(Func::PgFunction(PgFunc::GenRandomUUID))
378 }
379
380 /// Call the `JSON_BUILD_OBJECT` function. Postgres only.
381 ///
382 /// # Examples
383 ///
384 /// ```
385 /// use sea_query::{tests_cfg::*, *};
386 ///
387 /// let query = Query::select()
388 /// .expr(PgFunc::json_build_object(vec![
389 /// (Expr::val("a"), Expr::val(1)),
390 /// (Expr::val("b"), Expr::val("2")),
391 /// ]))
392 /// .to_owned();
393 ///
394 /// assert_eq!(
395 /// query.to_string(PostgresQueryBuilder),
396 /// r#"SELECT JSON_BUILD_OBJECT('a', 1, 'b', '2')"#
397 /// );
398 /// ```
399 pub fn json_build_object<T>(pairs: Vec<(T, T)>) -> FunctionCall
400 where
401 T: Into<Expr>,
402 {
403 let mut args = vec![];
404 for (key, value) in pairs {
405 args.push(key.into());
406 args.push(value.into());
407 }
408 FunctionCall::new(Func::PgFunction(PgFunc::JsonBuildObject)).args(args)
409 }
410
411 /// Call the `DATE_TRUNC` function. Postgres only.
412 ///
413 /// # Examples
414 ///
415 /// ```
416 /// use sea_query::{tests_cfg::*, *};
417 ///
418 /// let query = Query::select()
419 /// .expr(PgFunc::date_trunc(
420 /// PgDateTruncUnit::Day,
421 /// Expr::val("2020-01-01"),
422 /// ))
423 /// .to_owned();
424 ///
425 /// assert_eq!(
426 /// query.to_string(PostgresQueryBuilder),
427 /// r#"SELECT DATE_TRUNC('day', '2020-01-01')"#
428 /// );
429 ///
430 /// let query = Query::select()
431 /// .expr(PgFunc::date_trunc(
432 /// PgDateTruncUnit::Microseconds,
433 /// Expr::val("2020-01-01"),
434 /// ))
435 /// .to_owned();
436 ///
437 /// assert_eq!(
438 /// query.to_string(PostgresQueryBuilder),
439 /// r#"SELECT DATE_TRUNC('microseconds', '2020-01-01')"#
440 /// );
441 /// ```
442 pub fn date_trunc<T>(unit: PgDateTruncUnit, expr: T) -> FunctionCall
443 where
444 T: Into<Expr>,
445 {
446 FunctionCall::new(Func::PgFunction(PgFunc::DateTrunc))
447 .args([Expr::val(unit.to_string()), expr.into()])
448 }
449
450 /// Call the `JSON_AGG` function. Postgres only.
451 ///
452 /// # Examples
453 ///
454 /// ```
455 /// use sea_query::{tests_cfg::*, *};
456 ///
457 /// let query = Query::select()
458 /// .from(Char::Table)
459 /// .expr(PgFunc::json_agg(Expr::col(Char::SizeW)))
460 /// .to_owned();
461 ///
462 /// assert_eq!(
463 /// query.to_string(PostgresQueryBuilder),
464 /// r#"SELECT JSON_AGG("size_w") FROM "character""#
465 /// );
466 /// ```
467 pub fn json_agg<T>(expr: T) -> FunctionCall
468 where
469 T: Into<Expr>,
470 {
471 FunctionCall::new(Func::PgFunction(PgFunc::JsonAgg)).arg(expr)
472 }
473
474 /// Call the `ARRAY_AGG` function. Postgres only.
475 ///
476 /// # Examples
477 ///
478 /// ```
479 /// use sea_query::{tests_cfg::*, *};
480 ///
481 /// let query = Query::select()
482 /// .from(Char::Table)
483 /// .expr(PgFunc::array_agg(Expr::col(Char::Id)))
484 /// .group_by_col(Char::Character)
485 /// .to_owned();
486 ///
487 /// assert_eq!(
488 /// query.to_string(PostgresQueryBuilder),
489 /// r#"SELECT ARRAY_AGG("id") FROM "character" GROUP BY "character""#
490 /// );
491 /// ```
492 pub fn array_agg<T>(expr: T) -> FunctionCall
493 where
494 T: Into<Expr>,
495 {
496 FunctionCall::new(Func::PgFunction(PgFunc::ArrayAgg)).arg(expr)
497 }
498
499 /// Call the `ARRAY_AGG` function with the `DISTINCT` modifier. Postgres only.
500 ///
501 /// # Examples
502 ///
503 /// ```
504 /// use sea_query::{tests_cfg::*, *};
505 ///
506 /// let query = Query::select()
507 /// .from(Char::Table)
508 /// .expr(PgFunc::array_agg_distinct(Expr::col(Char::Id)))
509 /// .group_by_col(Char::Character)
510 /// .to_owned();
511 ///
512 /// assert_eq!(
513 /// query.to_string(PostgresQueryBuilder),
514 /// r#"SELECT ARRAY_AGG(DISTINCT "id") FROM "character" GROUP BY "character""#
515 /// );
516 /// ```
517 pub fn array_agg_distinct<T>(expr: T) -> FunctionCall
518 where
519 T: Into<Expr>,
520 {
521 FunctionCall::new(Func::PgFunction(PgFunc::ArrayAgg))
522 .arg_with(expr, FuncArgMod { distinct: true })
523 }
524}