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