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

sea_query/extension/postgres/
func.rs
