sea_query/extension/postgres/func.rs
1//! For calling built-in Postgres SQL functions.
2
3use crate::{PgDateTruncUnit, expr::*, func::*};
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)]
11#[non_exhaustive]
12pub enum PgFunc {
13 ToTsquery,
14 ToTsvector,
15 PhrasetoTsquery,
16 PlaintoTsquery,
17 WebsearchToTsquery,
18 TsRank,
19 TsRankCd,
20 StartsWith,
21 GenRandomUUID,
22 JsonBuildObject,
23 JsonAgg,
24 ArrayAgg,
25 DateTrunc,
26 #[cfg(feature = "postgres-array")]
27 Any,
28 #[cfg(feature = "postgres-array")]
29 Some,
30 #[cfg(feature = "postgres-array")]
31 All,
32}
33
34/// Type alias of [`PgFunc`] for compatibility.
35/// Previously, [`PgFunc`] is a namespace for building [`FunctionCall`].
36#[deprecated(since = "1.0.0", note = "use `PgFunc` instead")]
37pub type PgFunction = PgFunc;
38
39impl PgFunc {
40 /// Call `TO_TSQUERY` function. Postgres only.
41 ///
42 /// The parameter `regconfig` represents the OID of the text search configuration.
43 /// If the value is `None` the argument is omitted from the query, and hence the database default used.
44 ///
45 /// # Examples
46 ///
47 /// ```
48 /// use sea_query::{tests_cfg::*, *};
49 ///
50 /// let query = Query::select()
51 /// .expr(PgFunc::to_tsquery("a & b", None))
52 /// .to_owned();
53 ///
54 /// assert_eq!(
55 /// query.to_string(PostgresQueryBuilder),
56 /// r#"SELECT TO_TSQUERY('a & b')"#
57 /// );
58 /// ```
59 pub fn to_tsquery<T>(expr: T, regconfig: Option<u32>) -> FunctionCall
60 where
61 T: Into<Expr>,
62 {
63 match regconfig {
64 Some(config) => {
65 let config = Expr::Value(config.into());
66 FunctionCall::new(Func::PgFunction(PgFunc::ToTsquery)).args([config, expr.into()])
67 }
68 None => FunctionCall::new(Func::PgFunction(PgFunc::ToTsquery)).arg(expr),
69 }
70 }
71
72 /// Call `TO_TSVECTOR` function. Postgres only.
73 ///
74 /// The parameter `regconfig` represents the OID of the text search configuration.
75 /// If the value is `None` the argument is omitted from the query, and hence the database default used.
76 ///
77 /// # Examples
78 ///
79 /// ```
80 /// use sea_query::{tests_cfg::*, *};
81 ///
82 /// let query = Query::select()
83 /// .expr(PgFunc::to_tsvector("a b", None))
84 /// .to_owned();
85 ///
86 /// assert_eq!(
87 /// query.to_string(PostgresQueryBuilder),
88 /// r#"SELECT TO_TSVECTOR('a b')"#
89 /// );
90 /// ```
91 pub fn to_tsvector<T>(expr: T, regconfig: Option<u32>) -> FunctionCall
92 where
93 T: Into<Expr>,
94 {
95 match regconfig {
96 Some(config) => {
97 let config = Expr::Value(config.into());
98 FunctionCall::new(Func::PgFunction(PgFunc::ToTsvector)).args([config, expr.into()])
99 }
100 None => FunctionCall::new(Func::PgFunction(PgFunc::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<Expr>,
126 {
127 match regconfig {
128 Some(config) => {
129 let config = Expr::Value(config.into());
130 FunctionCall::new(Func::PgFunction(PgFunc::PhrasetoTsquery))
131 .args([config, expr.into()])
132 }
133 None => FunctionCall::new(Func::PgFunction(PgFunc::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<Expr>,
159 {
160 match regconfig {
161 Some(config) => {
162 let config = Expr::Value(config.into());
163 FunctionCall::new(Func::PgFunction(PgFunc::PlaintoTsquery))
164 .args([config, expr.into()])
165 }
166 None => FunctionCall::new(Func::PgFunction(PgFunc::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<Expr>,
192 {
193 match regconfig {
194 Some(config) => {
195 let config = Expr::Value(config.into());
196 FunctionCall::new(Func::PgFunction(PgFunc::WebsearchToTsquery))
197 .args([config, expr.into()])
198 }
199 None => FunctionCall::new(Func::PgFunction(PgFunc::WebsearchToTsquery)).arg(expr),
200 }
201 }
202
203 /// Call `TS_RANK` function. Postgres only.
204 ///
205 /// # Examples
206 ///
207 /// ```
208 /// use sea_query::{tests_cfg::*, *};
209 ///
210 /// let query = Query::select()
211 /// .expr(PgFunc::ts_rank("a b", "a&b"))
212 /// .to_owned();
213 ///
214 /// assert_eq!(
215 /// query.to_string(PostgresQueryBuilder),
216 /// r#"SELECT TS_RANK('a b', 'a&b')"#
217 /// );
218 /// ```
219 pub fn ts_rank<T>(vector: T, query: T) -> FunctionCall
220 where
221 T: Into<Expr>,
222 {
223 FunctionCall::new(Func::PgFunction(PgFunc::TsRank)).args([vector.into(), query.into()])
224 }
225
226 /// Call `TS_RANK_CD` function. Postgres only.
227 ///
228 /// # Examples
229 ///
230 /// ```
231 /// use sea_query::{tests_cfg::*, *};
232 ///
233 /// let query = Query::select()
234 /// .expr(PgFunc::ts_rank_cd("a b", "a&b"))
235 /// .to_owned();
236 ///
237 /// assert_eq!(
238 /// query.to_string(PostgresQueryBuilder),
239 /// r#"SELECT TS_RANK_CD('a b', 'a&b')"#
240 /// );
241 /// ```
242 pub fn ts_rank_cd<T>(vector: T, query: T) -> FunctionCall
243 where
244 T: Into<Expr>,
245 {
246 FunctionCall::new(Func::PgFunction(PgFunc::TsRankCd)).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<Expr>,
267 {
268 FunctionCall::new(Func::PgFunction(PgFunc::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<Expr>,
289 {
290 FunctionCall::new(Func::PgFunction(PgFunc::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<Expr>,
311 {
312 FunctionCall::new(Func::PgFunction(PgFunc::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<Expr>,
334 P: Into<Expr>,
335 {
336 FunctionCall::new(Func::PgFunction(PgFunc::StartsWith)).args([text.into(), prefix.into()])
337 }
338
339 /// Call `GEN_RANDOM_UUID` function. Postgres only.
340 ///
341 /// # Examples
342 ///
343 /// ```
344 /// use sea_query::{tests_cfg::*, *};
345 ///
346 /// let query = Query::select().expr(PgFunc::gen_random_uuid()).to_owned();
347 ///
348 /// assert_eq!(
349 /// query.to_string(PostgresQueryBuilder),
350 /// r#"SELECT GEN_RANDOM_UUID()"#
351 /// );
352 /// ```
353 pub fn gen_random_uuid() -> FunctionCall {
354 FunctionCall::new(Func::PgFunction(PgFunc::GenRandomUUID))
355 }
356
357 /// Call the `JSON_BUILD_OBJECT` function. Postgres only.
358 ///
359 /// # Examples
360 ///
361 /// ```
362 /// use sea_query::{tests_cfg::*, *};
363 ///
364 /// let query = Query::select()
365 /// .expr(PgFunc::json_build_object(vec![
366 /// (Expr::val("a"), Expr::val(1)),
367 /// (Expr::val("b"), Expr::val("2")),
368 /// ]))
369 /// .to_owned();
370 ///
371 /// assert_eq!(
372 /// query.to_string(PostgresQueryBuilder),
373 /// r#"SELECT JSON_BUILD_OBJECT('a', 1, 'b', '2')"#
374 /// );
375 /// ```
376 pub fn json_build_object<T>(pairs: Vec<(T, T)>) -> FunctionCall
377 where
378 T: Into<Expr>,
379 {
380 let mut args = vec![];
381 for (key, value) in pairs {
382 args.push(key.into());
383 args.push(value.into());
384 }
385 FunctionCall::new(Func::PgFunction(PgFunc::JsonBuildObject)).args(args)
386 }
387
388 /// Call the `DATE_TRUNC` function. Postgres only.
389 ///
390 /// # Examples
391 ///
392 /// ```
393 /// use sea_query::{tests_cfg::*, *};
394 ///
395 /// let query = Query::select()
396 /// .expr(PgFunc::date_trunc(
397 /// PgDateTruncUnit::Day,
398 /// Expr::val("2020-01-01"),
399 /// ))
400 /// .to_owned();
401 ///
402 /// assert_eq!(
403 /// query.to_string(PostgresQueryBuilder),
404 /// r#"SELECT DATE_TRUNC('day', '2020-01-01')"#
405 /// );
406 ///
407 /// let query = Query::select()
408 /// .expr(PgFunc::date_trunc(
409 /// PgDateTruncUnit::Microseconds,
410 /// Expr::val("2020-01-01"),
411 /// ))
412 /// .to_owned();
413 ///
414 /// assert_eq!(
415 /// query.to_string(PostgresQueryBuilder),
416 /// r#"SELECT DATE_TRUNC('microseconds', '2020-01-01')"#
417 /// );
418 /// ```
419 pub fn date_trunc<T>(unit: PgDateTruncUnit, expr: T) -> FunctionCall
420 where
421 T: Into<Expr>,
422 {
423 FunctionCall::new(Func::PgFunction(PgFunc::DateTrunc))
424 .args([Expr::val(unit.to_string()), expr.into()])
425 }
426
427 /// Call the `JSON_AGG` function. Postgres only.
428 ///
429 /// # Examples
430 ///
431 /// ```
432 /// use sea_query::{tests_cfg::*, *};
433 ///
434 /// let query = Query::select()
435 /// .from(Char::Table)
436 /// .expr(PgFunc::json_agg(Expr::col(Char::SizeW)))
437 /// .to_owned();
438 ///
439 /// assert_eq!(
440 /// query.to_string(PostgresQueryBuilder),
441 /// r#"SELECT JSON_AGG("size_w") FROM "character""#
442 /// );
443 /// ```
444 pub fn json_agg<T>(expr: T) -> FunctionCall
445 where
446 T: Into<Expr>,
447 {
448 FunctionCall::new(Func::PgFunction(PgFunc::JsonAgg)).arg(expr)
449 }
450
451 /// Call the `ARRAY_AGG` function. Postgres only.
452 ///
453 /// # Examples
454 ///
455 /// ```
456 /// use sea_query::{tests_cfg::*, *};
457 ///
458 /// let query = Query::select()
459 /// .from(Char::Table)
460 /// .expr(PgFunc::array_agg(Expr::col(Char::Id)))
461 /// .group_by_col(Char::Character)
462 /// .to_owned();
463 ///
464 /// assert_eq!(
465 /// query.to_string(PostgresQueryBuilder),
466 /// r#"SELECT ARRAY_AGG("id") FROM "character" GROUP BY "character""#
467 /// );
468 /// ```
469 pub fn array_agg<T>(expr: T) -> FunctionCall
470 where
471 T: Into<Expr>,
472 {
473 FunctionCall::new(Func::PgFunction(PgFunc::ArrayAgg)).arg(expr)
474 }
475
476 /// Call the `ARRAY_AGG` function with the `DISTINCT` modifier. Postgres only.
477 ///
478 /// # Examples
479 ///
480 /// ```
481 /// use sea_query::{tests_cfg::*, *};
482 ///
483 /// let query = Query::select()
484 /// .from(Char::Table)
485 /// .expr(PgFunc::array_agg_distinct(Expr::col(Char::Id)))
486 /// .group_by_col(Char::Character)
487 /// .to_owned();
488 ///
489 /// assert_eq!(
490 /// query.to_string(PostgresQueryBuilder),
491 /// r#"SELECT ARRAY_AGG(DISTINCT "id") FROM "character" GROUP BY "character""#
492 /// );
493 /// ```
494 pub fn array_agg_distinct<T>(expr: T) -> FunctionCall
495 where
496 T: Into<Expr>,
497 {
498 FunctionCall::new(Func::PgFunction(PgFunc::ArrayAgg))
499 .arg_with(expr, FuncArgMod { distinct: true })
500 }
501}