sea_query/backend/postgres/
query.rs

1use super::*;
2use crate::extension::postgres::*;
3
4impl OperLeftAssocDecider for PostgresQueryBuilder {
5    fn well_known_left_associative(&self, op: &BinOper) -> bool {
6        let common_answer = common_well_known_left_associative(op);
7        let pg_specific_answer = matches!(op, BinOper::PgOperator(PgBinOper::Concatenate));
8        common_answer || pg_specific_answer
9    }
10}
11
12impl PrecedenceDecider for PostgresQueryBuilder {
13    fn inner_expr_well_known_greater_precedence(&self, inner: &Expr, outer_oper: &Oper) -> bool {
14        let common_answer = common_inner_expr_well_known_greater_precedence(inner, outer_oper);
15        let pg_specific_answer = match inner {
16            Expr::Binary(_, inner_bin_oper, _) => {
17                let inner_oper: Oper = (*inner_bin_oper).into();
18                if inner_oper.is_arithmetic() || inner_oper.is_shift() {
19                    is_ilike(inner_bin_oper)
20                } else if is_pg_comparison(inner_bin_oper) {
21                    outer_oper.is_logical()
22                } else {
23                    false
24                }
25            }
26            _ => false,
27        };
28        common_answer || pg_specific_answer
29    }
30}
31
32impl QueryBuilder for PostgresQueryBuilder {
33    fn placeholder(&self) -> (&'static str, bool) {
34        ("$", true)
35    }
36
37    fn prepare_simple_expr(&self, simple_expr: &Expr, sql: &mut dyn SqlWriter) {
38        match simple_expr {
39            Expr::AsEnum(type_name, expr) => {
40                sql.write_str("CAST(").unwrap();
41                self.prepare_simple_expr_common(expr, sql);
42                let q = self.quote();
43                let type_name = &type_name.0;
44                let (ty, sfx) = if let Some(base) = type_name.strip_suffix("[]") {
45                    (base, "[]")
46                } else {
47                    (type_name.as_ref(), "")
48                };
49                sql.write_str(" AS ").unwrap();
50                sql.write_char(q.left()).unwrap();
51                sql.write_str(ty).unwrap();
52                sql.write_char(q.right()).unwrap();
53                sql.write_str(sfx).unwrap();
54                sql.write_char(')').unwrap();
55            }
56            _ => QueryBuilder::prepare_simple_expr_common(self, simple_expr, sql),
57        }
58    }
59
60    fn prepare_select_distinct(&self, select_distinct: &SelectDistinct, sql: &mut dyn SqlWriter) {
61        match select_distinct {
62            SelectDistinct::All => sql.write_str("ALL").unwrap(),
63            SelectDistinct::Distinct => sql.write_str("DISTINCT").unwrap(),
64            SelectDistinct::DistinctOn(cols) => {
65                sql.write_str("DISTINCT ON (").unwrap();
66
67                let mut cols = cols.iter();
68                join_io!(
69                    cols,
70                    col,
71                    join {
72                        sql.write_str(", ").unwrap();
73                    },
74                    do {
75                        self.prepare_column_ref(col, sql);
76                    }
77                );
78
79                sql.write_str(")").unwrap();
80            }
81            _ => {}
82        };
83    }
84
85    fn prepare_bin_oper(&self, bin_oper: &BinOper, sql: &mut dyn SqlWriter) {
86        match bin_oper {
87            BinOper::PgOperator(oper) => sql
88                .write_str(match oper {
89                    PgBinOper::ILike => "ILIKE",
90                    PgBinOper::NotILike => "NOT ILIKE",
91                    PgBinOper::Matches => "@@",
92                    PgBinOper::Contains => "@>",
93                    PgBinOper::Contained => "<@",
94                    PgBinOper::Concatenate => "||",
95                    PgBinOper::Overlap => "&&",
96                    PgBinOper::Similarity => "%",
97                    PgBinOper::WordSimilarity => "<%",
98                    PgBinOper::StrictWordSimilarity => "<<%",
99                    PgBinOper::SimilarityDistance => "<->",
100                    PgBinOper::WordSimilarityDistance => "<<->",
101                    PgBinOper::StrictWordSimilarityDistance => "<<<->",
102                    PgBinOper::GetJsonField => "->",
103                    PgBinOper::CastJsonField => "->>",
104                    PgBinOper::Regex => "~",
105                    PgBinOper::RegexCaseInsensitive => "~*",
106                    #[cfg(feature = "postgres-vector")]
107                    PgBinOper::EuclideanDistance => "<->",
108                    #[cfg(feature = "postgres-vector")]
109                    PgBinOper::NegativeInnerProduct => "<#>",
110                    #[cfg(feature = "postgres-vector")]
111                    PgBinOper::CosineDistance => "<=>",
112                })
113                .unwrap(),
114            _ => self.prepare_bin_oper_common(bin_oper, sql),
115        }
116    }
117
118    fn prepare_query_statement(&self, query: &SubQueryStatement, sql: &mut dyn SqlWriter) {
119        query.prepare_statement(self, sql);
120    }
121
122    fn prepare_function_name(&self, function: &Func, sql: &mut dyn SqlWriter) {
123        match function {
124            Func::PgFunction(function) => sql
125                .write_str(match function {
126                    PgFunc::ToTsquery => "TO_TSQUERY",
127                    PgFunc::ToTsvector => "TO_TSVECTOR",
128                    PgFunc::PhrasetoTsquery => "PHRASETO_TSQUERY",
129                    PgFunc::PlaintoTsquery => "PLAINTO_TSQUERY",
130                    PgFunc::WebsearchToTsquery => "WEBSEARCH_TO_TSQUERY",
131                    PgFunc::TsRank => "TS_RANK",
132                    PgFunc::TsRankCd => "TS_RANK_CD",
133                    PgFunc::StartsWith => "STARTS_WITH",
134                    PgFunc::GenRandomUUID => "GEN_RANDOM_UUID",
135                    PgFunc::JsonBuildObject => "JSON_BUILD_OBJECT",
136                    PgFunc::JsonAgg => "JSON_AGG",
137                    PgFunc::ArrayAgg => "ARRAY_AGG",
138                    PgFunc::DateTrunc => "DATE_TRUNC",
139                    #[cfg(feature = "postgres-array")]
140                    PgFunc::Any => "ANY",
141                    #[cfg(feature = "postgres-array")]
142                    PgFunc::Some => "SOME",
143                    #[cfg(feature = "postgres-array")]
144                    PgFunc::All => "ALL",
145                })
146                .unwrap(),
147            _ => self.prepare_function_name_common(function, sql),
148        }
149    }
150
151    fn prepare_table_sample(&self, select: &SelectStatement, sql: &mut dyn SqlWriter) {
152        let Some(table_sample) = select.table_sample else {
153            return;
154        };
155
156        match table_sample.method {
157            SampleMethod::BERNOULLI => sql.write_str(" TABLESAMPLE BERNOULLI").unwrap(),
158            SampleMethod::SYSTEM => sql.write_str(" TABLESAMPLE SYSTEM").unwrap(),
159        }
160        sql.write_str(" (").unwrap();
161        write!(sql, "{}", table_sample.percentage).unwrap();
162        sql.write_char(')').unwrap();
163        if let Some(repeatable) = table_sample.repeatable {
164            sql.write_str(" REPEATABLE (").unwrap();
165            write!(sql, "{repeatable}").unwrap();
166            sql.write_char(')').unwrap();
167        }
168    }
169
170    fn prepare_order_expr(&self, order_expr: &OrderExpr, sql: &mut dyn SqlWriter) {
171        if !matches!(order_expr.order, Order::Field(_)) {
172            self.prepare_simple_expr(&order_expr.expr, sql);
173        }
174        self.prepare_order(order_expr, sql);
175        match order_expr.nulls {
176            None => (),
177            Some(NullOrdering::Last) => sql.write_str(" NULLS LAST").unwrap(),
178            Some(NullOrdering::First) => sql.write_str(" NULLS FIRST").unwrap(),
179        }
180    }
181
182    fn prepare_value(&self, value: Value, sql: &mut dyn SqlWriter) {
183        sql.push_param(value, self as _);
184    }
185
186    fn write_string_quoted(&self, string: &str, buffer: &mut dyn Write) {
187        if self.need_escape(string) {
188            buffer.write_str("E'").unwrap();
189        } else {
190            buffer.write_str("'").unwrap();
191        }
192        self.write_escaped(buffer, string);
193        buffer.write_str("'").unwrap();
194    }
195
196    fn write_bytes(&self, bytes: &[u8], buffer: &mut dyn Write) {
197        buffer.write_str("'\\x").unwrap();
198        for b in bytes {
199            write!(buffer, "{b:02X}").unwrap();
200        }
201        buffer.write_str("'").unwrap();
202    }
203
204    fn if_null_function(&self) -> &str {
205        "COALESCE"
206    }
207}
208
209fn is_pg_comparison(b: &BinOper) -> bool {
210    matches!(
211        b,
212        BinOper::PgOperator(PgBinOper::Contained)
213            | BinOper::PgOperator(PgBinOper::Contains)
214            | BinOper::PgOperator(PgBinOper::Similarity)
215            | BinOper::PgOperator(PgBinOper::WordSimilarity)
216            | BinOper::PgOperator(PgBinOper::StrictWordSimilarity)
217            | BinOper::PgOperator(PgBinOper::Matches)
218    )
219}
220
221fn is_ilike(b: &BinOper) -> bool {
222    matches!(
223        b,
224        BinOper::PgOperator(PgBinOper::ILike) | BinOper::PgOperator(PgBinOper::NotILike)
225    )
226}
sea_query/backend/postgres/query.rs

sea_query/backend/postgres/
query.rs
