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