sea_query/backend/postgres/
query.rs

1use super::*;
2#[cfg(feature = "postgres-array")]
3use crate::ArrayType;
4use crate::extension::postgres::*;
5
6impl OperLeftAssocDecider for PostgresQueryBuilder {
7    fn well_known_left_associative(&self, op: &BinOper) -> bool {
8        let common_answer = common_well_known_left_associative(op);
9        let pg_specific_answer = matches!(op, BinOper::PgOperator(PgBinOper::Concatenate));
10        common_answer || pg_specific_answer
11    }
12}
13
14impl PrecedenceDecider for PostgresQueryBuilder {
15    fn inner_expr_well_known_greater_precedence(&self, inner: &Expr, outer_oper: &Oper) -> bool {
16        let common_answer = common_inner_expr_well_known_greater_precedence(inner, outer_oper);
17        let pg_specific_answer = match inner {
18            Expr::Binary(_, inner_bin_oper, _) => {
19                let inner_oper: Oper = (*inner_bin_oper).into();
20                if inner_oper.is_arithmetic() || inner_oper.is_shift() {
21                    is_ilike(inner_bin_oper)
22                } else if is_pg_comparison(inner_bin_oper) {
23                    outer_oper.is_logical()
24                } else {
25                    false
26                }
27            }
28            _ => false,
29        };
30        common_answer || pg_specific_answer
31    }
32}
33
34impl QueryBuilder for PostgresQueryBuilder {
35    fn placeholder(&self) -> (&'static str, bool) {
36        ("$", true)
37    }
38
39    fn prepare_expr(&self, simple_expr: &Expr, sql: &mut impl SqlWriter) {
40        match simple_expr {
41            Expr::AsEnum(type_name, expr) => {
42                sql.write_str("CAST(").unwrap();
43                self.prepare_expr_common(expr, sql);
44                let q = self.quote();
45                let type_name = &type_name.0;
46                let (ty, sfx) = if let Some(base) = type_name.strip_suffix("[]") {
47                    (base, "[]")
48                } else {
49                    (type_name.as_ref(), "")
50                };
51                sql.write_str(" AS ").unwrap();
52                sql.write_char(q.left()).unwrap();
53                sql.write_str(ty).unwrap();
54                sql.write_char(q.right()).unwrap();
55                sql.write_str(sfx).unwrap();
56                sql.write_char(')').unwrap();
57            }
58            _ => QueryBuilder::prepare_expr_common(self, simple_expr, sql),
59        }
60    }
61
62    fn write_value(&self, buf: &mut impl Write, value: &Value) -> std::fmt::Result {
63        match value {
64            Value::Enum(v) => match v {
65                OptionEnum::Some(v) => {
66                    self.write_string_quoted(v.value.as_ref(), buf);
67                    self.write_enum_type_hint(v.type_name.as_ref(), buf);
68                    Ok(())
69                }
70                OptionEnum::None(_) => buf.write_str("NULL"),
71            },
72            #[cfg(feature = "postgres-array")]
73            Value::Array(ArrayType::Enum(type_name), values) => {
74                match values {
75                    None => return buf.write_str("NULL"),
76                    Some(values) => {
77                        if values.is_empty() {
78                            buf.write_str("'{}'")?;
79                        } else {
80                            // TODO: refactor this with JoinWrite after #1055 merged
81                            // TODO: refactor this if deref_pattern is stabilize
82                            macro_rules! validate_value {
83                                ($name:ident) => {
84                                    {
85                                        let Value::Enum(OptionEnum::Some(value)) = $name else {
86                                            panic!(
87                                                "Value::Array(ArrayType::Enum) should contain Value::Enum"
88                                            );
89                                        };
90                                        self.write_string_quoted(value.value.as_ref(), buf);
91                                    }
92                                };
93                            }
94                            buf.write_str("ARRAY [")?;
95                            let mut iter = values.iter();
96                            if let Some(element) = iter.next() {
97                                validate_value!(element);
98                                for element in iter {
99                                    buf.write_str(",")?;
100                                    validate_value!(element)
101                                }
102                            }
103                            buf.write_str("]")?;
104                        }
105                    }
106                }
107                self.write_enum_type_hint(type_name.as_ref(), buf);
108                Ok(())
109            }
110            _ => self.write_value_common(buf, value),
111        }
112    }
113
114    fn prepare_select_distinct(&self, select_distinct: &SelectDistinct, sql: &mut impl SqlWriter) {
115        match select_distinct {
116            SelectDistinct::All => sql.write_str("ALL").unwrap(),
117            SelectDistinct::Distinct => sql.write_str("DISTINCT").unwrap(),
118            SelectDistinct::DistinctOn(cols) => {
119                sql.write_str("DISTINCT ON (").unwrap();
120
121                let mut cols = cols.iter();
122                join_io!(
123                    cols,
124                    col,
125                    join {
126                        sql.write_str(", ").unwrap();
127                    },
128                    do {
129                        self.prepare_column_ref(col, sql);
130                    }
131                );
132
133                sql.write_str(")").unwrap();
134            }
135            _ => {}
136        };
137    }
138
139    fn prepare_select_into(&self, into_table: &SelectInto, sql: &mut impl SqlWriter) {
140        sql.write_str(" INTO ").unwrap();
141
142        if let Some(modifier) = &into_table.target_table_modifier {
143            sql.write_fmt(format_args!("{modifier} ")).unwrap();
144        }
145
146        sql.write_str("TABLE ").unwrap();
147        self.prepare_iden(&into_table.target_table, sql);
148    }
149
150    fn prepare_bin_oper(&self, bin_oper: &BinOper, sql: &mut impl SqlWriter) {
151        match bin_oper {
152            BinOper::PgOperator(oper) => sql
153                .write_str(match oper {
154                    PgBinOper::ILike => "ILIKE",
155                    PgBinOper::NotILike => "NOT ILIKE",
156                    PgBinOper::Matches => "@@",
157                    PgBinOper::Contains => "@>",
158                    PgBinOper::Contained => "<@",
159                    PgBinOper::Concatenate => "||",
160                    PgBinOper::Overlap => "&&",
161                    PgBinOper::Similarity => "%",
162                    PgBinOper::WordSimilarity => "<%",
163                    PgBinOper::StrictWordSimilarity => "<<%",
164                    PgBinOper::SimilarityDistance => "<->",
165                    PgBinOper::WordSimilarityDistance => "<<->",
166                    PgBinOper::StrictWordSimilarityDistance => "<<<->",
167                    PgBinOper::GetJsonField => "->",
168                    PgBinOper::CastJsonField => "->>",
169                    PgBinOper::Regex => "~",
170                    PgBinOper::RegexCaseInsensitive => "~*",
171                    #[cfg(feature = "postgres-vector")]
172                    PgBinOper::EuclideanDistance => "<->",
173                    #[cfg(feature = "postgres-vector")]
174                    PgBinOper::NegativeInnerProduct => "<#>",
175                    #[cfg(feature = "postgres-vector")]
176                    PgBinOper::CosineDistance => "<=>",
177                })
178                .unwrap(),
179            _ => self.prepare_bin_oper_common(bin_oper, sql),
180        }
181    }
182
183    fn prepare_query_statement(&self, query: &SubQueryStatement, sql: &mut impl SqlWriter) {
184        query.prepare_statement(self, sql);
185    }
186
187    fn prepare_explain_statement(&self, explain: &ExplainStatement, sql: &mut impl SqlWriter) {
188        fn write_sep(sql: &mut impl SqlWriter, first: &mut bool) {
189            if !*first {
190                sql.write_str(", ").unwrap();
191            } else {
192                *first = false;
193            }
194        }
195
196        // https://www.postgresql.org/docs/current/sql-explain.html
197        // Specifies whether the selected option should be turned on or off. You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF, or 0 to disable it. The boolean value can also be omitted, in which case TRUE is assumed.
198        fn write_false(sql: &mut impl SqlWriter, value: bool) {
199            if !value {
200                sql.write_str(" 0").unwrap();
201            }
202        }
203
204        sql.write_str("EXPLAIN").unwrap();
205
206        let has_options = explain.analyze.is_some()
207            || explain.pg_opts.verbose.is_some()
208            || explain.pg_opts.costs.is_some()
209            || explain.pg_opts.settings.is_some()
210            || explain.pg_opts.generic_plan.is_some()
211            || explain.pg_opts.buffers.is_some()
212            || explain.pg_opts.serialize.is_some()
213            || explain.pg_opts.wal.is_some()
214            || explain.pg_opts.timing.is_some()
215            || explain.pg_opts.summary.is_some()
216            || explain.pg_opts.memory.is_some()
217            || explain.format.is_some();
218
219        if has_options {
220            sql.write_str(" (").unwrap();
221            let mut first = true;
222
223            if let Some(analyze) = explain.analyze {
224                write_sep(sql, &mut first);
225                sql.write_str("ANALYZE").unwrap();
226                write_false(sql, analyze);
227            }
228
229            if let Some(verbose) = explain.pg_opts.verbose {
230                write_sep(sql, &mut first);
231                sql.write_str("VERBOSE").unwrap();
232                write_false(sql, verbose);
233            }
234
235            if let Some(costs) = explain.pg_opts.costs {
236                write_sep(sql, &mut first);
237                sql.write_str("COSTS").unwrap();
238                write_false(sql, costs);
239            }
240
241            if let Some(settings) = explain.pg_opts.settings {
242                write_sep(sql, &mut first);
243                sql.write_str("SETTINGS").unwrap();
244                write_false(sql, settings);
245            }
246
247            if let Some(generic_plan) = explain.pg_opts.generic_plan {
248                write_sep(sql, &mut first);
249                sql.write_str("GENERIC_PLAN").unwrap();
250                write_false(sql, generic_plan);
251            }
252
253            if let Some(buffers) = explain.pg_opts.buffers {
254                write_sep(sql, &mut first);
255                sql.write_str("BUFFERS").unwrap();
256                write_false(sql, buffers);
257            }
258
259            if let Some(serialize) = explain.pg_opts.serialize {
260                write_sep(sql, &mut first);
261                sql.write_str("SERIALIZE ").unwrap();
262                sql.write_str(serialize.as_str()).unwrap();
263            }
264
265            if let Some(wal) = explain.pg_opts.wal {
266                write_sep(sql, &mut first);
267                sql.write_str("WAL").unwrap();
268                write_false(sql, wal);
269            }
270
271            if let Some(timing) = explain.pg_opts.timing {
272                write_sep(sql, &mut first);
273                sql.write_str("TIMING").unwrap();
274                write_false(sql, timing);
275            }
276
277            if let Some(summary) = explain.pg_opts.summary {
278                write_sep(sql, &mut first);
279                sql.write_str("SUMMARY").unwrap();
280                write_false(sql, summary);
281            }
282
283            if let Some(memory) = explain.pg_opts.memory {
284                write_sep(sql, &mut first);
285                sql.write_str("MEMORY").unwrap();
286                write_false(sql, memory);
287            }
288
289            if let Some(format) = explain.format {
290                write_sep(sql, &mut first);
291                sql.write_str("FORMAT ").unwrap();
292                sql.write_str(format.as_str()).unwrap();
293            }
294            sql.write_str(")").unwrap();
295        }
296
297        if let Some(statement) = &explain.statement {
298            sql.write_str(" ").unwrap();
299            statement.write_to(self, sql);
300        }
301    }
302
303    fn prepare_function_name(&self, function: &Func, sql: &mut impl SqlWriter) {
304        match function {
305            Func::PgFunction(function) => sql
306                .write_str(match function {
307                    PgFunc::ToTsquery => "TO_TSQUERY",
308                    PgFunc::ToTsvector => "TO_TSVECTOR",
309                    PgFunc::PhrasetoTsquery => "PHRASETO_TSQUERY",
310                    PgFunc::PlaintoTsquery => "PLAINTO_TSQUERY",
311                    PgFunc::WebsearchToTsquery => "WEBSEARCH_TO_TSQUERY",
312                    PgFunc::TsRank => "TS_RANK",
313                    PgFunc::TsRankCd => "TS_RANK_CD",
314                    PgFunc::StartsWith => "STARTS_WITH",
315                    PgFunc::GenRandomUUID => "GEN_RANDOM_UUID",
316                    PgFunc::JsonBuildObject => "JSON_BUILD_OBJECT",
317                    PgFunc::JsonAgg => "JSON_AGG",
318                    PgFunc::ArrayAgg => "ARRAY_AGG",
319                    PgFunc::DateTrunc => "DATE_TRUNC",
320                    PgFunc::Any => "ANY",
321                    PgFunc::Some => "SOME",
322                    PgFunc::All => "ALL",
323                    PgFunc::AdvisoryLock => "PG_ADVISORY_LOCK",
324                    PgFunc::AdvisoryLockShared => "PG_ADVISORY_LOCK_SHARED",
325                    PgFunc::TryAdvisoryLock => "PG_TRY_ADVISORY_LOCK",
326                    PgFunc::TryAdvisoryLockShared => "PG_TRY_ADVISORY_LOCK_SHARED",
327                    PgFunc::AdvisoryUnlock => "PG_ADVISORY_UNLOCK",
328                    PgFunc::AdvisoryUnlockShared => "PG_ADVISORY_UNLOCK_SHARED",
329                    PgFunc::AdvisoryUnlockAll => "PG_ADVISORY_UNLOCK_ALL",
330                    PgFunc::AdvisoryXactLock => "PG_ADVISORY_XACT_LOCK",
331                    PgFunc::AdvisoryXactLockShared => "PG_ADVISORY_XACT_LOCK_SHARED",
332                    PgFunc::TryAdvisoryXactLock => "PG_TRY_ADVISORY_XACT_LOCK",
333                    PgFunc::TryAdvisoryXactLockShared => "PG_TRY_ADVISORY_XACT_LOCK_SHARED",
334                })
335                .unwrap(),
336            _ => self.prepare_function_name_common(function, sql),
337        }
338    }
339
340    fn prepare_table_sample(&self, select: &SelectStatement, sql: &mut impl SqlWriter) {
341        let Some(table_sample) = select.table_sample else {
342            return;
343        };
344
345        match table_sample.method {
346            SampleMethod::BERNOULLI => sql.write_str(" TABLESAMPLE BERNOULLI").unwrap(),
347            SampleMethod::SYSTEM => sql.write_str(" TABLESAMPLE SYSTEM").unwrap(),
348        }
349        sql.write_str(" (").unwrap();
350        write!(sql, "{}", table_sample.percentage).unwrap();
351        sql.write_char(')').unwrap();
352        if let Some(repeatable) = table_sample.repeatable {
353            sql.write_str(" REPEATABLE (").unwrap();
354            write!(sql, "{repeatable}").unwrap();
355            sql.write_char(')').unwrap();
356        }
357    }
358
359    fn prepare_order_expr(&self, order_expr: &OrderExpr, sql: &mut impl SqlWriter) {
360        if !matches!(order_expr.order, Order::Field(_)) {
361            self.prepare_expr(&order_expr.expr, sql);
362        }
363        self.prepare_order(order_expr, sql);
364        match order_expr.nulls {
365            None => (),
366            Some(NullOrdering::Last) => sql.write_str(" NULLS LAST").unwrap(),
367            Some(NullOrdering::First) => sql.write_str(" NULLS FIRST").unwrap(),
368        }
369    }
370
371    fn prepare_value(&self, value: Value, sql: &mut impl SqlWriter) {
372        match value {
373            Value::Enum(value) => match value {
374                OptionEnum::Some(value) => {
375                    let Enum { type_name, value } = *value;
376                    sql.push_param(Value::String(Some(value.into_owned())), self as _);
377                    self.write_enum_type_hint(type_name, sql);
378                }
379                OptionEnum::None(type_name) => {
380                    sql.push_param(Value::String(None), self as _);
381                    self.write_enum_type_hint(type_name, sql);
382                }
383            },
384            #[cfg(feature = "postgres-array")]
385            Value::Array(ArrayType::Enum(type_name), values) => {
386                let values = values.map(|values| {
387                    Box::new(
388                        values
389                            .into_iter()
390                            .map(|value| match value {
391                                Value::Enum(OptionEnum::Some(value)) => {
392                                    Value::String(Some(value.value.into_owned()))
393                                }
394                                _ => {
395                                    panic!(
396                                        "Value::Array(ArrayType::Enum) should contain Value::Enum"
397                                    );
398                                }
399                            })
400                            .collect(),
401                    )
402                });
403                sql.push_param(Value::Array(ArrayType::String, values), self as _);
404                self.write_enum_type_hint(type_name.as_ref(), sql);
405                sql.write_str("[]").unwrap();
406            }
407            _ => sql.push_param(value, self as _),
408        }
409    }
410
411    fn write_string_quoted(&self, string: &str, buffer: &mut impl Write) {
412        if self.needs_escape(string) {
413            buffer.write_str("E'").unwrap();
414        } else {
415            buffer.write_str("'").unwrap();
416        }
417        self.write_escaped(buffer, string);
418        buffer.write_str("'").unwrap();
419    }
420
421    fn write_bytes(&self, bytes: &[u8], buffer: &mut impl Write) {
422        buffer.write_str("'\\x").unwrap();
423        for b in bytes {
424            write!(buffer, "{b:02X}").unwrap();
425        }
426        buffer.write_str("'").unwrap();
427    }
428
429    fn if_null_function(&self) -> &str {
430        "COALESCE"
431    }
432}
433
434impl PostgresQueryBuilder {
435    fn write_enum_type_hint(&self, type_name: impl AsRef<str>, sql: &mut impl Write) {
436        sql.write_str("::").unwrap();
437        let q = self.quote();
438        sql.write_char(q.left()).unwrap();
439        sql.write_str(type_name.as_ref()).unwrap();
440        sql.write_char(q.right()).unwrap();
441    }
442}
443
444fn is_pg_comparison(b: &BinOper) -> bool {
445    matches!(
446        b,
447        BinOper::PgOperator(PgBinOper::Contained)
448            | BinOper::PgOperator(PgBinOper::Contains)
449            | BinOper::PgOperator(PgBinOper::Similarity)
450            | BinOper::PgOperator(PgBinOper::WordSimilarity)
451            | BinOper::PgOperator(PgBinOper::StrictWordSimilarity)
452            | BinOper::PgOperator(PgBinOper::Matches)
453    )
454}
455
456fn is_ilike(b: &BinOper) -> bool {
457    matches!(
458        b,
459        BinOper::PgOperator(PgBinOper::ILike) | BinOper::PgOperator(PgBinOper::NotILike)
460    )
461}
sea_query/backend/postgres/query.rs

sea_query/backend/postgres/
query.rs
