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

sea_query/backend/postgres/
query.rs
