mz_sql/
func.rs

1// Copyright Materialize, Inc. and contributors. All rights reserved.
2//
3// Use of this software is governed by the Business Source License
4// included in the LICENSE file.
5//
6// As of the Change Date specified in that file, in accordance with
7// the Business Source License, use of this software will be governed
8// by the Apache License, Version 2.0.
9
10//! TBD: Currently, `sql::func` handles matching arguments to their respective
11//! built-in functions (for most built-in functions, at least).
12
13use std::cell::RefCell;
14use std::collections::BTreeMap;
15use std::fmt;
16use std::sync::LazyLock;
17
18use itertools::Itertools;
19use mz_expr::func;
20use mz_ore::collections::CollectionExt;
21use mz_ore::str::StrExt;
22use mz_pgrepr::oid;
23use mz_repr::role_id::RoleId;
24use mz_repr::{ColumnName, Datum, ScalarBaseType, SqlRelationType, SqlScalarType};
25
26use crate::ast::{SelectStatement, Statement};
27use crate::catalog::{CatalogType, TypeCategory, TypeReference};
28use crate::names::{self, ResolvedItemName};
29use crate::plan::error::PlanError;
30use crate::plan::hir::{
31    AggregateFunc, BinaryFunc, CoercibleScalarExpr, CoercibleScalarType, ColumnOrder,
32    HirRelationExpr, HirScalarExpr, ScalarWindowFunc, TableFunc, UnaryFunc, UnmaterializableFunc,
33    ValueWindowFunc, VariadicFunc,
34};
35use crate::plan::query::{self, ExprContext, QueryContext};
36use crate::plan::scope::Scope;
37use crate::plan::side_effecting_func::PG_CATALOG_SEF_BUILTINS;
38use crate::plan::transform_ast;
39use crate::plan::typeconv::{self, CastContext};
40use crate::session::vars::{self, ENABLE_TIME_AT_TIME_ZONE};
41
42/// A specifier for a function or an operator.
43#[derive(Clone, Copy, Debug)]
44pub enum FuncSpec<'a> {
45    /// A function name.
46    Func(&'a ResolvedItemName),
47    /// An operator name.
48    Op(&'a str),
49}
50
51impl<'a> fmt::Display for FuncSpec<'a> {
52    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
53        match self {
54            FuncSpec::Func(n) => n.fmt(f),
55            FuncSpec::Op(o) => o.fmt(f),
56        }
57    }
58}
59
60impl TypeCategory {
61    /// Extracted from PostgreSQL 9.6.
62    /// ```sql,ignore
63    /// SELECT array_agg(typname), typcategory
64    /// FROM pg_catalog.pg_type
65    /// WHERE typname IN (
66    ///  'bool', 'bytea', 'date', 'float4', 'float8', 'int4', 'int8', 'interval', 'jsonb',
67    ///  'numeric', 'text', 'time', 'timestamp', 'timestamptz'
68    /// )
69    /// GROUP BY typcategory
70    /// ORDER BY typcategory;
71    /// ```
72    pub fn from_type(typ: &SqlScalarType) -> Self {
73        // Keep this in sync with `from_catalog_type`.
74        match typ {
75            SqlScalarType::Array(..) | SqlScalarType::Int2Vector => Self::Array,
76            SqlScalarType::Bool => Self::Boolean,
77            SqlScalarType::AclItem
78            | SqlScalarType::Bytes
79            | SqlScalarType::Jsonb
80            | SqlScalarType::Uuid
81            | SqlScalarType::MzAclItem => Self::UserDefined,
82            SqlScalarType::Date
83            | SqlScalarType::Time
84            | SqlScalarType::Timestamp { .. }
85            | SqlScalarType::TimestampTz { .. } => Self::DateTime,
86            SqlScalarType::Float32
87            | SqlScalarType::Float64
88            | SqlScalarType::Int16
89            | SqlScalarType::Int32
90            | SqlScalarType::Int64
91            | SqlScalarType::UInt16
92            | SqlScalarType::UInt32
93            | SqlScalarType::UInt64
94            | SqlScalarType::Oid
95            | SqlScalarType::RegClass
96            | SqlScalarType::RegProc
97            | SqlScalarType::RegType
98            | SqlScalarType::Numeric { .. } => Self::Numeric,
99            SqlScalarType::Interval => Self::Timespan,
100            SqlScalarType::List { .. } => Self::List,
101            SqlScalarType::PgLegacyChar
102            | SqlScalarType::PgLegacyName
103            | SqlScalarType::String
104            | SqlScalarType::Char { .. }
105            | SqlScalarType::VarChar { .. } => Self::String,
106            SqlScalarType::Record { custom_id, .. } => {
107                if custom_id.is_some() {
108                    Self::Composite
109                } else {
110                    Self::Pseudo
111                }
112            }
113            SqlScalarType::Map { .. } => Self::Pseudo,
114            SqlScalarType::MzTimestamp => Self::Numeric,
115            SqlScalarType::Range { .. } => Self::Range,
116        }
117    }
118
119    pub fn from_param(param: &ParamType) -> Self {
120        match param {
121            ParamType::Any
122            | ParamType::AnyElement
123            | ParamType::ArrayAny
124            | ParamType::ArrayAnyCompatible
125            | ParamType::AnyCompatible
126            | ParamType::ListAny
127            | ParamType::ListAnyCompatible
128            | ParamType::ListElementAnyCompatible
129            | ParamType::Internal
130            | ParamType::NonVecAny
131            | ParamType::NonVecAnyCompatible
132            | ParamType::MapAny
133            | ParamType::MapAnyCompatible
134            | ParamType::RecordAny => Self::Pseudo,
135            ParamType::RangeAnyCompatible | ParamType::RangeAny => Self::Range,
136            ParamType::Plain(t) => Self::from_type(t),
137        }
138    }
139
140    /// Like [`TypeCategory::from_type`], but for catalog types.
141    // TODO(benesch): would be nice to figure out how to share code with
142    // `from_type`, but the refactor to enable that would be substantial.
143    pub fn from_catalog_type<T>(catalog_type: &CatalogType<T>) -> Self
144    where
145        T: TypeReference,
146    {
147        // Keep this in sync with `from_type`.
148        match catalog_type {
149            CatalogType::Array { .. } | CatalogType::Int2Vector => Self::Array,
150            CatalogType::Bool => Self::Boolean,
151            CatalogType::AclItem
152            | CatalogType::Bytes
153            | CatalogType::Jsonb
154            | CatalogType::Uuid
155            | CatalogType::MzAclItem => Self::UserDefined,
156            CatalogType::Date
157            | CatalogType::Time
158            | CatalogType::Timestamp
159            | CatalogType::TimestampTz => Self::DateTime,
160            CatalogType::Float32
161            | CatalogType::Float64
162            | CatalogType::Int16
163            | CatalogType::Int32
164            | CatalogType::Int64
165            | CatalogType::UInt16
166            | CatalogType::UInt32
167            | CatalogType::UInt64
168            | CatalogType::Oid
169            | CatalogType::RegClass
170            | CatalogType::RegProc
171            | CatalogType::RegType
172            | CatalogType::Numeric { .. } => Self::Numeric,
173            CatalogType::Interval => Self::Timespan,
174            CatalogType::List { .. } => Self::List,
175            CatalogType::PgLegacyChar
176            | CatalogType::PgLegacyName
177            | CatalogType::String
178            | CatalogType::Char { .. }
179            | CatalogType::VarChar { .. } => Self::String,
180            CatalogType::Record { .. } => TypeCategory::Composite,
181            CatalogType::Map { .. } | CatalogType::Pseudo => Self::Pseudo,
182            CatalogType::MzTimestamp => Self::String,
183            CatalogType::Range { .. } => Self::Range,
184        }
185    }
186
187    /// Extracted from PostgreSQL 9.6.
188    /// ```ignore
189    /// SELECT typcategory, typname, typispreferred
190    /// FROM pg_catalog.pg_type
191    /// WHERE typispreferred = true
192    /// ORDER BY typcategory;
193    /// ```
194    pub fn preferred_type(&self) -> Option<SqlScalarType> {
195        match self {
196            Self::Array
197            | Self::BitString
198            | Self::Composite
199            | Self::Enum
200            | Self::Geometric
201            | Self::List
202            | Self::NetworkAddress
203            | Self::Pseudo
204            | Self::Range
205            | Self::Unknown
206            | Self::UserDefined => None,
207            Self::Boolean => Some(SqlScalarType::Bool),
208            Self::DateTime => Some(SqlScalarType::TimestampTz { precision: None }),
209            Self::Numeric => Some(SqlScalarType::Float64),
210            Self::String => Some(SqlScalarType::String),
211            Self::Timespan => Some(SqlScalarType::Interval),
212        }
213    }
214}
215
216/// Builds an expression that evaluates a scalar function on the provided
217/// input expressions.
218pub struct Operation<R>(
219    pub  Box<
220        dyn Fn(
221                &ExprContext,
222                Vec<CoercibleScalarExpr>,
223                &ParamList,
224                Vec<ColumnOrder>,
225            ) -> Result<R, PlanError>
226            + Send
227            + Sync,
228    >,
229);
230
231impl<R> fmt::Debug for Operation<R> {
232    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
233        f.debug_struct("Operation").finish()
234    }
235}
236
237impl Operation<HirScalarExpr> {
238    /// Builds a unary operation that simply returns its input.
239    fn identity() -> Operation<HirScalarExpr> {
240        Operation::unary(|_ecx, e| Ok(e))
241    }
242}
243
244impl<R> Operation<R> {
245    fn new<F>(f: F) -> Operation<R>
246    where
247        F: Fn(
248                &ExprContext,
249                Vec<CoercibleScalarExpr>,
250                &ParamList,
251                Vec<ColumnOrder>,
252            ) -> Result<R, PlanError>
253            + Send
254            + Sync
255            + 'static,
256    {
257        Operation(Box::new(f))
258    }
259
260    /// Builds an operation that takes no arguments.
261    fn nullary<F>(f: F) -> Operation<R>
262    where
263        F: Fn(&ExprContext) -> Result<R, PlanError> + Send + Sync + 'static,
264    {
265        Self::variadic(move |ecx, exprs| {
266            assert!(exprs.is_empty());
267            f(ecx)
268        })
269    }
270
271    /// Builds an operation that takes one argument.
272    fn unary<F>(f: F) -> Operation<R>
273    where
274        F: Fn(&ExprContext, HirScalarExpr) -> Result<R, PlanError> + Send + Sync + 'static,
275    {
276        Self::variadic(move |ecx, exprs| f(ecx, exprs.into_element()))
277    }
278
279    /// Builds an operation that takes one argument and an order_by.
280    fn unary_ordered<F>(f: F) -> Operation<R>
281    where
282        F: Fn(&ExprContext, HirScalarExpr, Vec<ColumnOrder>) -> Result<R, PlanError>
283            + Send
284            + Sync
285            + 'static,
286    {
287        Self::new(move |ecx, cexprs, params, order_by| {
288            let exprs = coerce_args_to_types(ecx, cexprs, params)?;
289            f(ecx, exprs.into_element(), order_by)
290        })
291    }
292
293    /// Builds an operation that takes two arguments.
294    fn binary<F>(f: F) -> Operation<R>
295    where
296        F: Fn(&ExprContext, HirScalarExpr, HirScalarExpr) -> Result<R, PlanError>
297            + Send
298            + Sync
299            + 'static,
300    {
301        Self::variadic(move |ecx, exprs| {
302            assert_eq!(exprs.len(), 2);
303            let mut exprs = exprs.into_iter();
304            let left = exprs.next().unwrap();
305            let right = exprs.next().unwrap();
306            f(ecx, left, right)
307        })
308    }
309
310    /// Builds an operation that takes two arguments and an order_by.
311    ///
312    /// If returning an aggregate function, it should return `true` for
313    /// [`AggregateFunc::is_order_sensitive`].
314    fn binary_ordered<F>(f: F) -> Operation<R>
315    where
316        F: Fn(&ExprContext, HirScalarExpr, HirScalarExpr, Vec<ColumnOrder>) -> Result<R, PlanError>
317            + Send
318            + Sync
319            + 'static,
320    {
321        Self::new(move |ecx, cexprs, params, order_by| {
322            let exprs = coerce_args_to_types(ecx, cexprs, params)?;
323            assert_eq!(exprs.len(), 2);
324            let mut exprs = exprs.into_iter();
325            let left = exprs.next().unwrap();
326            let right = exprs.next().unwrap();
327            f(ecx, left, right, order_by)
328        })
329    }
330
331    /// Builds an operation that takes any number of arguments.
332    fn variadic<F>(f: F) -> Operation<R>
333    where
334        F: Fn(&ExprContext, Vec<HirScalarExpr>) -> Result<R, PlanError> + Send + Sync + 'static,
335    {
336        Self::new(move |ecx, cexprs, params, _order_by| {
337            let exprs = coerce_args_to_types(ecx, cexprs, params)?;
338            f(ecx, exprs)
339        })
340    }
341}
342
343/// Backing implementation for sql_impl_func and sql_impl_cast. See those
344/// functions for details.
345pub fn sql_impl(
346    expr: &str,
347) -> impl Fn(&QueryContext, Vec<SqlScalarType>) -> Result<HirScalarExpr, PlanError> + use<> {
348    let expr = mz_sql_parser::parser::parse_expr(expr).unwrap_or_else(|e| {
349        panic!(
350            "static function definition failed to parse {}: {}",
351            expr.quoted(),
352            e,
353        )
354    });
355    move |qcx, types| {
356        // Reconstruct an expression context where the parameter types are
357        // bound to the types of the expressions in `args`.
358        let mut scx = qcx.scx.clone();
359        scx.param_types = RefCell::new(
360            types
361                .into_iter()
362                .enumerate()
363                .map(|(i, ty)| (i + 1, ty))
364                .collect(),
365        );
366        let qcx = QueryContext::root(&scx, qcx.lifetime);
367
368        let (mut expr, _) = names::resolve(qcx.scx.catalog, expr.clone())?;
369        // Desugar the expression
370        transform_ast::transform(&scx, &mut expr)?;
371
372        let ecx = ExprContext {
373            qcx: &qcx,
374            name: "static function definition",
375            scope: &Scope::empty(),
376            relation_type: &SqlRelationType::empty(),
377            allow_aggregates: false,
378            allow_subqueries: true,
379            allow_parameters: true,
380            allow_windows: false,
381        };
382
383        // Plan the expression.
384        query::plan_expr(&ecx, &expr)?.type_as_any(&ecx)
385    }
386}
387
388// Constructs a definition for a built-in function out of a static SQL
389// expression.
390//
391// The SQL expression should use the standard parameter syntax (`$1`, `$2`, ...)
392// to refer to the inputs to the function. For example, a built-in function that
393// takes two arguments and concatenates them with an arrow in between could be
394// defined like so:
395//
396//     sql_impl_func("$1 || '<->' || $2")
397//
398// The number of parameters in the SQL expression must exactly match the number
399// of parameters in the built-in's declaration. There is no support for variadic
400// functions.
401fn sql_impl_func(expr: &str) -> Operation<HirScalarExpr> {
402    let invoke = sql_impl(expr);
403    Operation::variadic(move |ecx, args| {
404        let types = args.iter().map(|arg| ecx.scalar_type(arg)).collect();
405        let mut out = invoke(ecx.qcx, types)?;
406        out.splice_parameters(&args, 0);
407        Ok(out)
408    })
409}
410
411// Defines a built-in table function from a static SQL SELECT statement.
412//
413// The SQL statement should use the standard parameter syntax (`$1`, `$2`, ...)
414// to refer to the inputs to the function; see sql_impl_func for an example.
415//
416// The number of parameters in the SQL expression must exactly match the number
417// of parameters in the built-in's declaration. There is no support for variadic
418// functions.
419//
420// As this is a full SQL statement, it returns a set of rows, similar to a
421// table function. The SELECT's projection's names are used and should be
422// aliased if needed.
423fn sql_impl_table_func_inner(
424    sql: &'static str,
425    feature_flag: Option<&'static vars::FeatureFlag>,
426) -> Operation<TableFuncPlan> {
427    let query = match mz_sql_parser::parser::parse_statements(sql)
428        .expect("static function definition failed to parse")
429        .expect_element(|| "static function definition must have exactly one statement")
430        .ast
431    {
432        Statement::Select(SelectStatement { query, as_of: None }) => query,
433        _ => panic!("static function definition expected SELECT statement"),
434    };
435    let invoke = move |qcx: &QueryContext, types: Vec<SqlScalarType>| {
436        // Reconstruct an expression context where the parameter types are
437        // bound to the types of the expressions in `args`.
438        let mut scx = qcx.scx.clone();
439        scx.param_types = RefCell::new(
440            types
441                .into_iter()
442                .enumerate()
443                .map(|(i, ty)| (i + 1, ty))
444                .collect(),
445        );
446        let mut qcx = QueryContext::root(&scx, qcx.lifetime);
447
448        let query = query.clone();
449        let (mut query, _) = names::resolve(qcx.scx.catalog, query)?;
450        transform_ast::transform(&scx, &mut query)?;
451
452        query::plan_nested_query(&mut qcx, &query)
453    };
454
455    Operation::variadic(move |ecx, args| {
456        if let Some(feature_flag) = feature_flag {
457            ecx.require_feature_flag(feature_flag)?;
458        }
459        let types = args.iter().map(|arg| ecx.scalar_type(arg)).collect();
460        let (mut expr, scope) = invoke(ecx.qcx, types)?;
461        expr.splice_parameters(&args, 0);
462        Ok(TableFuncPlan {
463            imp: TableFuncImpl::Expr(expr),
464            column_names: scope.column_names().cloned().collect(),
465        })
466    })
467}
468
469/// Implements a table function using SQL.
470///
471/// Warning: These implementations are currently defective for WITH ORDINALITY / FROM ROWS, see
472/// comment in `plan_table_function_internal`.
473fn sql_impl_table_func(sql: &'static str) -> Operation<TableFuncPlan> {
474    sql_impl_table_func_inner(sql, None)
475}
476
477fn experimental_sql_impl_table_func(
478    feature: &'static vars::FeatureFlag,
479    sql: &'static str,
480) -> Operation<TableFuncPlan> {
481    sql_impl_table_func_inner(sql, Some(feature))
482}
483
484/// Describes a single function's implementation.
485pub struct FuncImpl<R> {
486    pub oid: u32,
487    pub params: ParamList,
488    pub return_type: ReturnType,
489    pub op: Operation<R>,
490}
491
492/// Describes how each implementation should be represented in the catalog.
493#[derive(Debug)]
494pub struct FuncImplCatalogDetails {
495    pub oid: u32,
496    pub arg_typs: Vec<&'static str>,
497    pub variadic_typ: Option<&'static str>,
498    pub return_typ: Option<&'static str>,
499    pub return_is_set: bool,
500}
501
502impl<R> FuncImpl<R> {
503    pub fn details(&self) -> FuncImplCatalogDetails {
504        FuncImplCatalogDetails {
505            oid: self.oid,
506            arg_typs: self.params.arg_names(),
507            variadic_typ: self.params.variadic_name(),
508            return_typ: self.return_type.typ.as_ref().map(|t| t.name()),
509            return_is_set: self.return_type.is_set_of,
510        }
511    }
512}
513
514impl<R> fmt::Debug for FuncImpl<R> {
515    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
516        f.debug_struct("FuncImpl")
517            .field("oid", &self.oid)
518            .field("params", &self.params)
519            .field("ret", &self.return_type)
520            .field("op", &"<omitted>")
521            .finish()
522    }
523}
524
525impl From<UnmaterializableFunc> for Operation<HirScalarExpr> {
526    fn from(n: UnmaterializableFunc) -> Operation<HirScalarExpr> {
527        Operation::nullary(move |_ecx| Ok(HirScalarExpr::call_unmaterializable(n.clone())))
528    }
529}
530
531impl From<UnaryFunc> for Operation<HirScalarExpr> {
532    fn from(u: UnaryFunc) -> Operation<HirScalarExpr> {
533        Operation::unary(move |_ecx, e| Ok(e.call_unary(u.clone())))
534    }
535}
536
537impl From<BinaryFunc> for Operation<HirScalarExpr> {
538    fn from(b: BinaryFunc) -> Operation<HirScalarExpr> {
539        Operation::binary(move |_ecx, left, right| Ok(left.call_binary(right, b.clone())))
540    }
541}
542
543impl From<VariadicFunc> for Operation<HirScalarExpr> {
544    fn from(v: VariadicFunc) -> Operation<HirScalarExpr> {
545        Operation::variadic(move |_ecx, exprs| Ok(HirScalarExpr::call_variadic(v.clone(), exprs)))
546    }
547}
548
549impl From<AggregateFunc> for Operation<(HirScalarExpr, AggregateFunc)> {
550    fn from(a: AggregateFunc) -> Operation<(HirScalarExpr, AggregateFunc)> {
551        Operation::unary(move |_ecx, e| Ok((e, a.clone())))
552    }
553}
554
555impl From<ScalarWindowFunc> for Operation<ScalarWindowFunc> {
556    fn from(a: ScalarWindowFunc) -> Operation<ScalarWindowFunc> {
557        Operation::nullary(move |_ecx| Ok(a.clone()))
558    }
559}
560
561impl From<ValueWindowFunc> for Operation<(HirScalarExpr, ValueWindowFunc)> {
562    fn from(a: ValueWindowFunc) -> Operation<(HirScalarExpr, ValueWindowFunc)> {
563        Operation::unary(move |_ecx, e| Ok((e, a.clone())))
564    }
565}
566
567#[derive(Debug, Clone, Eq, PartialEq, Hash)]
568/// Describes possible types of function parameters.
569///
570/// Note that this is not exhaustive and will likely require additions.
571pub enum ParamList {
572    Exact(Vec<ParamType>),
573    Variadic {
574        leading: Vec<ParamType>,
575        trailing: ParamType,
576    },
577}
578
579impl ParamList {
580    /// Determines whether `typs` are compatible with `self`.
581    fn matches_argtypes(&self, ecx: &ExprContext, typs: &[CoercibleScalarType]) -> bool {
582        if !self.validate_arg_len(typs.len()) {
583            return false;
584        }
585
586        for (i, typ) in typs.iter().enumerate() {
587            let param = &self[i];
588            if let CoercibleScalarType::Coerced(typ) = typ {
589                // Ensures either `typ` can at least be implicitly cast to a
590                // type `param` accepts. Implicit in this check is that unknown
591                // type arguments can be cast to any type.
592                //
593                // N.B. this will require more fallthrough checks once we
594                // support RECORD types in functions.
595                if !param.accepts_type(ecx, typ) {
596                    return false;
597                }
598            }
599        }
600
601        // Ensure a polymorphic solution exists (non-polymorphic functions have
602        // trivial polymorphic solutions that evaluate to `None`).
603        PolymorphicSolution::new(ecx, typs, self).is_some()
604    }
605
606    /// Validates that the number of input elements are viable for `self`.
607    fn validate_arg_len(&self, input_len: usize) -> bool {
608        match self {
609            Self::Exact(p) => p.len() == input_len,
610            Self::Variadic { leading, .. } => input_len > leading.len(),
611        }
612    }
613
614    /// Matches a `&[SqlScalarType]` derived from the user's function argument
615    /// against this `ParamList`'s permitted arguments.
616    fn exact_match(&self, types: &[&SqlScalarType]) -> bool {
617        types.iter().enumerate().all(|(i, t)| self[i] == **t)
618    }
619
620    /// Generates values underlying data for for `mz_catalog.mz_functions.arg_ids`.
621    fn arg_names(&self) -> Vec<&'static str> {
622        match self {
623            ParamList::Exact(p) => p.iter().map(|p| p.name()).collect::<Vec<_>>(),
624            ParamList::Variadic { leading, trailing } => leading
625                .iter()
626                .chain([trailing])
627                .map(|p| p.name())
628                .collect::<Vec<_>>(),
629        }
630    }
631
632    /// Generates values for `mz_catalog.mz_functions.variadic_id`.
633    fn variadic_name(&self) -> Option<&'static str> {
634        match self {
635            ParamList::Exact(_) => None,
636            ParamList::Variadic { trailing, .. } => Some(trailing.name()),
637        }
638    }
639}
640
641impl std::ops::Index<usize> for ParamList {
642    type Output = ParamType;
643
644    fn index(&self, i: usize) -> &Self::Output {
645        match self {
646            Self::Exact(p) => &p[i],
647            Self::Variadic { leading, trailing } => leading.get(i).unwrap_or(trailing),
648        }
649    }
650}
651
652/// Provides a shorthand function for writing `ParamList::Exact`.
653impl From<Vec<ParamType>> for ParamList {
654    fn from(p: Vec<ParamType>) -> ParamList {
655        ParamList::Exact(p)
656    }
657}
658
659#[derive(Debug, Clone, Eq, PartialEq, Hash)]
660/// Describes parameter types.
661///
662/// Parameters with "Compatible" in their name are used in conjunction with
663/// other "Compatible"-type parameters to determine the best common type to cast
664/// arguments to.
665///
666/// "Compatible" parameters contrast with parameters that contain "Any" in their
667/// name, but not "Compatible." These parameters require all other "Any"-type
668/// parameters be of the same type from the perspective of
669/// [`SqlScalarType::base_eq`].
670///
671/// For more details on polymorphic parameter resolution, see `PolymorphicSolution`.
672pub enum ParamType {
673    /// A pseudotype permitting any type. Note that this parameter does not
674    /// enforce the same "Any" constraint as the other "Any"-type parameters.
675    Any,
676    /// A pseudotype permitting any type, permitting other "Compatibility"-type
677    /// parameters to find the best common type.
678    AnyCompatible,
679    /// An pseudotype permitting any type, requiring other "Any"-type parameters
680    /// to be of the same type.
681    AnyElement,
682    /// An pseudotype permitting any array type, requiring other "Any"-type
683    /// parameters to be of the same type.
684    ArrayAny,
685    /// A pseudotype permitting any array type, permitting other "Compatibility"-type
686    /// parameters to find the best common type.
687    ArrayAnyCompatible,
688    /// An pseudotype permitting any list type, requiring other "Any"-type
689    /// parameters to be of the same type.
690    ListAny,
691    /// A pseudotype permitting any list type, permitting other
692    /// "Compatibility"-type parameters to find the best common type.
693    ListAnyCompatible,
694    /// A pseudotype permitting any type, permitting other "Compatibility"-type
695    /// parameters to find the best common type. Additionally, enforces a
696    /// constraint that when used with `ListAnyCompatible`, resolves to that
697    /// argument's element type.
698    ListElementAnyCompatible,
699    /// An pseudotype permitting any map type, requiring other "Any"-type
700    /// parameters to be of the same type.
701    MapAny,
702    /// A pseudotype permitting any map type, permitting other "Compatibility"-type
703    /// parameters to find the best common type.
704    MapAnyCompatible,
705    /// A pseudotype permitting any type except `SqlScalarType::List` and
706    /// `SqlScalarType::Array`, requiring other "Any"-type
707    /// parameters to be of the same type.
708    NonVecAny,
709    /// A pseudotype permitting any type except `SqlScalarType::List` and
710    /// `SqlScalarType::Array`, requiring other "Compatibility"-type
711    /// parameters to be of the same type.
712    NonVecAnyCompatible,
713    /// A standard parameter that accepts arguments that match its embedded
714    /// `SqlScalarType`.
715    Plain(SqlScalarType),
716    /// A polymorphic pseudotype permitting a `SqlScalarType::Record` of any type,
717    /// but all records must be structurally equal.
718    RecordAny,
719    /// An pseudotype permitting any range type, requiring other "Any"-type
720    /// parameters to be of the same type.
721    RangeAny,
722    /// A pseudotype permitting any range type, permitting other
723    /// "Compatibility"-type parameters to find the best common type.
724    ///
725    /// Prefer using [`ParamType::RangeAny`] over this type; it is easy to fool
726    /// this type into generating non-existent range types (e.g. ranges of
727    /// floats) that will panic.
728    RangeAnyCompatible,
729    /// A psuedotype indicating that the function is only meant to be called
730    /// internally by the database system.
731    Internal,
732}
733
734impl ParamType {
735    /// Does `self` accept arguments of type `t`?
736    fn accepts_type(&self, ecx: &ExprContext, t: &SqlScalarType) -> bool {
737        use ParamType::*;
738        use SqlScalarType::*;
739
740        match self {
741            Any | AnyElement | AnyCompatible | ListElementAnyCompatible => true,
742            ArrayAny | ArrayAnyCompatible => matches!(t, Array(..) | Int2Vector),
743            ListAny | ListAnyCompatible => matches!(t, List { .. }),
744            MapAny | MapAnyCompatible => matches!(t, Map { .. }),
745            RangeAny | RangeAnyCompatible => matches!(t, Range { .. }),
746            NonVecAny | NonVecAnyCompatible => !t.is_vec(),
747            Internal => false,
748            Plain(to) => typeconv::can_cast(ecx, CastContext::Implicit, t, to),
749            RecordAny => matches!(t, Record { .. }),
750        }
751    }
752
753    /// Does `t`'s [`TypeCategory`] prefer `self`? This question can make
754    /// more sense with the understanding that pseudotypes are never preferred.
755    fn is_preferred_by(&self, t: &SqlScalarType) -> bool {
756        if let Some(pt) = TypeCategory::from_type(t).preferred_type() {
757            *self == pt
758        } else {
759            false
760        }
761    }
762
763    /// Is `self` the [`ParamType`] corresponding to `t`'s [near match] value?
764    ///
765    /// [near match]: SqlScalarType::near_match
766    fn is_near_match(&self, t: &SqlScalarType) -> bool {
767        match (self, t.near_match()) {
768            (ParamType::Plain(t), Some(near_match)) => t.structural_eq(near_match),
769            _ => false,
770        }
771    }
772
773    /// Is `self` the preferred parameter type for its `TypeCategory`?
774    fn prefers_self(&self) -> bool {
775        if let Some(pt) = TypeCategory::from_param(self).preferred_type() {
776            *self == pt
777        } else {
778            false
779        }
780    }
781
782    fn is_polymorphic(&self) -> bool {
783        use ParamType::*;
784        match self {
785            AnyElement
786            | ArrayAny
787            | ArrayAnyCompatible
788            | AnyCompatible
789            | ListAny
790            | ListAnyCompatible
791            | ListElementAnyCompatible
792            | MapAny
793            | MapAnyCompatible
794            | NonVecAny
795            | NonVecAnyCompatible
796            // In PG, RecordAny isn't polymorphic even though it offers
797            // polymorphic behavior. For more detail, see
798            // `PolymorphicCompatClass::StructuralEq`.
799            | RecordAny
800            | RangeAny
801            | RangeAnyCompatible => true,
802            Any | Internal | Plain(_)  => false,
803        }
804    }
805
806    fn name(&self) -> &'static str {
807        match self {
808            ParamType::Plain(t) => {
809                assert!(
810                    !t.is_custom_type(),
811                    "custom types cannot currently be used as parameters; use a polymorphic parameter that accepts the custom type instead"
812                );
813                let t: mz_pgrepr::Type = t.into();
814                t.catalog_name()
815            }
816            ParamType::Any => "any",
817            ParamType::AnyCompatible => "anycompatible",
818            ParamType::AnyElement => "anyelement",
819            ParamType::ArrayAny => "anyarray",
820            ParamType::ArrayAnyCompatible => "anycompatiblearray",
821            ParamType::Internal => "internal",
822            ParamType::ListAny => "list",
823            ParamType::ListAnyCompatible => "anycompatiblelist",
824            // ListElementAnyCompatible is not identical to AnyCompatible, but reusing its ID appears harmless
825            ParamType::ListElementAnyCompatible => "anycompatible",
826            ParamType::MapAny => "map",
827            ParamType::MapAnyCompatible => "anycompatiblemap",
828            ParamType::NonVecAny => "anynonarray",
829            ParamType::NonVecAnyCompatible => "anycompatiblenonarray",
830            ParamType::RecordAny => "record",
831            ParamType::RangeAny => "anyrange",
832            ParamType::RangeAnyCompatible => "anycompatiblerange",
833        }
834    }
835}
836
837impl PartialEq<SqlScalarType> for ParamType {
838    fn eq(&self, other: &SqlScalarType) -> bool {
839        match self {
840            ParamType::Plain(s) => s.base_eq(other),
841            // Pseudotypes never equal concrete types
842            _ => false,
843        }
844    }
845}
846
847impl PartialEq<ParamType> for SqlScalarType {
848    fn eq(&self, other: &ParamType) -> bool {
849        other == self
850    }
851}
852
853impl From<SqlScalarType> for ParamType {
854    fn from(s: SqlScalarType) -> ParamType {
855        ParamType::Plain(s)
856    }
857}
858
859impl From<ScalarBaseType> for ParamType {
860    fn from(s: ScalarBaseType) -> ParamType {
861        use ScalarBaseType::*;
862        let s = match s {
863            Array | List | Map | Record | Range => {
864                panic!("use polymorphic parameters rather than {:?}", s);
865            }
866            AclItem => SqlScalarType::AclItem,
867            Bool => SqlScalarType::Bool,
868            Int16 => SqlScalarType::Int16,
869            Int32 => SqlScalarType::Int32,
870            Int64 => SqlScalarType::Int64,
871            UInt16 => SqlScalarType::UInt16,
872            UInt32 => SqlScalarType::UInt32,
873            UInt64 => SqlScalarType::UInt64,
874            Float32 => SqlScalarType::Float32,
875            Float64 => SqlScalarType::Float64,
876            Numeric => SqlScalarType::Numeric { max_scale: None },
877            Date => SqlScalarType::Date,
878            Time => SqlScalarType::Time,
879            Timestamp => SqlScalarType::Timestamp { precision: None },
880            TimestampTz => SqlScalarType::TimestampTz { precision: None },
881            Interval => SqlScalarType::Interval,
882            Bytes => SqlScalarType::Bytes,
883            String => SqlScalarType::String,
884            Char => SqlScalarType::Char { length: None },
885            VarChar => SqlScalarType::VarChar { max_length: None },
886            PgLegacyChar => SqlScalarType::PgLegacyChar,
887            PgLegacyName => SqlScalarType::PgLegacyName,
888            Jsonb => SqlScalarType::Jsonb,
889            Uuid => SqlScalarType::Uuid,
890            Oid => SqlScalarType::Oid,
891            RegClass => SqlScalarType::RegClass,
892            RegProc => SqlScalarType::RegProc,
893            RegType => SqlScalarType::RegType,
894            Int2Vector => SqlScalarType::Int2Vector,
895            MzTimestamp => SqlScalarType::MzTimestamp,
896            MzAclItem => SqlScalarType::MzAclItem,
897        };
898        ParamType::Plain(s)
899    }
900}
901
902#[derive(Debug, Clone, Eq, PartialEq, Hash)]
903pub struct ReturnType {
904    pub typ: Option<ParamType>,
905    pub is_set_of: bool,
906}
907
908impl ReturnType {
909    /// Expresses that a function's return type is a scalar value.
910    fn scalar(typ: ParamType) -> ReturnType {
911        ReturnType {
912            typ: Some(typ),
913            is_set_of: false,
914        }
915    }
916
917    /// Expresses that a function's return type is a set of values, e.g. a table
918    /// function.
919    fn set_of(typ: ParamType) -> ReturnType {
920        ReturnType {
921            typ: Some(typ),
922            is_set_of: true,
923        }
924    }
925
926    /// Expresses that a function's return type is None.
927    fn none(is_set_of: bool) -> ReturnType {
928        ReturnType {
929            typ: None,
930            is_set_of,
931        }
932    }
933}
934
935impl From<ParamType> for ReturnType {
936    fn from(typ: ParamType) -> ReturnType {
937        ReturnType::scalar(typ)
938    }
939}
940
941impl From<ScalarBaseType> for ReturnType {
942    fn from(s: ScalarBaseType) -> ReturnType {
943        ParamType::from(s).into()
944    }
945}
946
947impl From<SqlScalarType> for ReturnType {
948    fn from(s: SqlScalarType) -> ReturnType {
949        ParamType::Plain(s).into()
950    }
951}
952
953#[derive(Clone, Debug)]
954/// Tracks candidate implementations.
955pub struct Candidate<'a, R> {
956    /// The implementation under consideration.
957    fimpl: &'a FuncImpl<R>,
958    exact_matches: usize,
959    preferred_types: usize,
960    near_matches: usize,
961}
962
963/// Selects the best implementation given the provided `args` using a
964/// process similar to [PostgreSQL's parser][pgparser], and returns the
965/// `ScalarExpr` to invoke that function.
966///
967/// Inline comments prefixed with number are taken from the "Function Type
968/// Resolution" section of the aforelinked page.
969///
970/// # Errors
971/// - When the provided arguments are not valid for any implementation, e.g.
972///   cannot be converted to the appropriate types.
973/// - When all implementations are equally valid.
974///
975/// [pgparser]: https://www.postgresql.org/docs/current/typeconv-oper.html
976pub fn select_impl<R>(
977    ecx: &ExprContext,
978    spec: FuncSpec,
979    impls: &[FuncImpl<R>],
980    args: Vec<CoercibleScalarExpr>,
981    order_by: Vec<ColumnOrder>,
982) -> Result<R, PlanError>
983where
984    R: fmt::Debug,
985{
986    let name = spec.to_string();
987    let ecx = &ecx.with_name(&name);
988    let mut types: Vec<_> = args.iter().map(|e| ecx.scalar_type(e)).collect();
989
990    // PostgreSQL force coerces all record types before function selection. We
991    // may want to do something smarter in the future (e.g., a function that
992    // accepts multiple `RecordAny` parameters should perhaps coerce to the
993    // result of calling `guess_best_common_type` on all those parameters), but
994    // for now we just directly match PostgreSQL's behavior.
995    for ty in &mut types {
996        ty.force_coerced_if_record();
997    }
998
999    // 4.a. Discard candidate functions for which the input types do not
1000    // match and cannot be converted (using an implicit conversion) to
1001    // match. unknown literals are assumed to be convertible to anything for
1002    // this purpose.
1003    let impls: Vec<_> = impls
1004        .iter()
1005        .filter(|i| i.params.matches_argtypes(ecx, &types))
1006        .collect();
1007
1008    let f = find_match(ecx, &types, impls).map_err(|candidates| {
1009        let arg_types: Vec<_> = types
1010            .into_iter()
1011            .map(|ty| match ty {
1012                // This will be used in error msgs, therefore we call with `postgres_compat` false.
1013                CoercibleScalarType::Coerced(ty) => ecx.humanize_scalar_type(&ty, false),
1014                CoercibleScalarType::Record(_) => "record".to_string(),
1015                CoercibleScalarType::Uncoerced => "unknown".to_string(),
1016            })
1017            .collect();
1018
1019        if candidates == 0 {
1020            match spec {
1021                FuncSpec::Func(name) => PlanError::UnknownFunction {
1022                    name: ecx
1023                        .qcx
1024                        .scx
1025                        .humanize_resolved_name(name)
1026                        .expect("resolved to object")
1027                        .to_string(),
1028                    arg_types,
1029                },
1030                FuncSpec::Op(name) => PlanError::UnknownOperator {
1031                    name: name.to_string(),
1032                    arg_types,
1033                },
1034            }
1035        } else {
1036            match spec {
1037                FuncSpec::Func(name) => PlanError::IndistinctFunction {
1038                    name: ecx
1039                        .qcx
1040                        .scx
1041                        .humanize_resolved_name(name)
1042                        .expect("resolved to object")
1043                        .to_string(),
1044                    arg_types,
1045                },
1046                FuncSpec::Op(name) => PlanError::IndistinctOperator {
1047                    name: name.to_string(),
1048                    arg_types,
1049                },
1050            }
1051        }
1052    })?;
1053
1054    (f.op.0)(ecx, args, &f.params, order_by)
1055}
1056
1057/// Finds an exact match based on the arguments, or, if no exact match, finds
1058/// the best match available. Patterned after [PostgreSQL's type conversion
1059/// matching algorithm][pgparser].
1060///
1061/// [pgparser]: https://www.postgresql.org/docs/current/typeconv-func.html
1062fn find_match<'a, R: std::fmt::Debug>(
1063    ecx: &ExprContext,
1064    types: &[CoercibleScalarType],
1065    impls: Vec<&'a FuncImpl<R>>,
1066) -> Result<&'a FuncImpl<R>, usize> {
1067    let all_types_known = types.iter().all(|t| t.is_coerced());
1068
1069    // Check for exact match.
1070    if all_types_known {
1071        let known_types: Vec<_> = types.iter().filter_map(|t| t.as_coerced()).collect();
1072        let matching_impls: Vec<&FuncImpl<_>> = impls
1073            .iter()
1074            .filter(|i| i.params.exact_match(&known_types))
1075            .cloned()
1076            .collect();
1077
1078        if matching_impls.len() == 1 {
1079            return Ok(matching_impls[0]);
1080        }
1081    }
1082
1083    // No exact match. Apply PostgreSQL's best match algorithm. Generate
1084    // candidates by assessing their compatibility with each implementation's
1085    // parameters.
1086    let mut candidates: Vec<Candidate<_>> = Vec::new();
1087    macro_rules! maybe_get_last_candidate {
1088        () => {
1089            if candidates.len() == 1 {
1090                return Ok(&candidates[0].fimpl);
1091            }
1092        };
1093    }
1094    let mut max_exact_matches = 0;
1095
1096    for fimpl in impls {
1097        let mut exact_matches = 0;
1098        let mut preferred_types = 0;
1099        let mut near_matches = 0;
1100
1101        for (i, arg_type) in types.iter().enumerate() {
1102            let param_type = &fimpl.params[i];
1103
1104            match arg_type {
1105                CoercibleScalarType::Coerced(arg_type) => {
1106                    if param_type == arg_type {
1107                        exact_matches += 1;
1108                    }
1109                    if param_type.is_preferred_by(arg_type) {
1110                        preferred_types += 1;
1111                    }
1112                    if param_type.is_near_match(arg_type) {
1113                        near_matches += 1;
1114                    }
1115                }
1116                CoercibleScalarType::Record(_) | CoercibleScalarType::Uncoerced => {
1117                    if param_type.prefers_self() {
1118                        preferred_types += 1;
1119                    }
1120                }
1121            }
1122        }
1123
1124        // 4.a. Discard candidate functions for which the input types do not
1125        // match and cannot be converted (using an implicit conversion) to
1126        // match. unknown literals are assumed to be convertible to anything for
1127        // this purpose.
1128        max_exact_matches = std::cmp::max(max_exact_matches, exact_matches);
1129        candidates.push(Candidate {
1130            fimpl,
1131            exact_matches,
1132            preferred_types,
1133            near_matches,
1134        });
1135    }
1136
1137    if candidates.is_empty() {
1138        return Err(0);
1139    }
1140
1141    maybe_get_last_candidate!();
1142
1143    // 4.c. Run through all candidates and keep those with the most exact
1144    // matches on input types. Keep all candidates if none have exact matches.
1145    candidates.retain(|c| c.exact_matches >= max_exact_matches);
1146
1147    maybe_get_last_candidate!();
1148
1149    // 4.c.i. (MZ extension) Run through all candidates and keep those with the
1150    // most 'near' matches on input types. Keep all candidates if none have near
1151    // matches. If only one candidate remains, use it; else continue to the next
1152    // step.
1153    let mut max_near_matches = 0;
1154    for c in &candidates {
1155        max_near_matches = std::cmp::max(max_near_matches, c.near_matches);
1156    }
1157    candidates.retain(|c| c.near_matches >= max_near_matches);
1158
1159    // 4.d. Run through all candidates and keep those that accept preferred
1160    // types (of the input data type's type category) at the most positions
1161    // where type conversion will be required.
1162    let mut max_preferred_types = 0;
1163    for c in &candidates {
1164        max_preferred_types = std::cmp::max(max_preferred_types, c.preferred_types);
1165    }
1166    candidates.retain(|c| c.preferred_types >= max_preferred_types);
1167
1168    maybe_get_last_candidate!();
1169
1170    if all_types_known {
1171        return Err(candidates.len());
1172    }
1173
1174    let mut found_known = false;
1175    let mut types_match = true;
1176    let mut common_type: Option<SqlScalarType> = None;
1177
1178    for (i, arg_type) in types.iter().enumerate() {
1179        let mut selected_category: Option<TypeCategory> = None;
1180        let mut categories_match = true;
1181
1182        match arg_type {
1183            // 4.e. If any input arguments are unknown, check the type
1184            // categories accepted at those argument positions by the remaining
1185            // candidates.
1186            CoercibleScalarType::Uncoerced | CoercibleScalarType::Record(_) => {
1187                for c in candidates.iter() {
1188                    let this_category = TypeCategory::from_param(&c.fimpl.params[i]);
1189                    // 4.e. cont: Select the string category if any candidate
1190                    // accepts that category. (This bias towards string is
1191                    // appropriate since an unknown-type literal looks like a
1192                    // string.)
1193                    if this_category == TypeCategory::String {
1194                        selected_category = Some(TypeCategory::String);
1195                        break;
1196                    }
1197                    match selected_category {
1198                        Some(ref mut selected_category) => {
1199                            // 4.e. cont: [...otherwise,] if all the remaining candidates
1200                            // accept the same type category, select that category.
1201                            categories_match =
1202                                selected_category == &this_category && categories_match;
1203                        }
1204                        None => selected_category = Some(this_category.clone()),
1205                    }
1206                }
1207
1208                // 4.e. cont: Otherwise fail because the correct choice cannot
1209                // be deduced without more clues.
1210                // (ed: this doesn't mean fail entirely, simply moving onto 4.f)
1211                if selected_category != Some(TypeCategory::String) && !categories_match {
1212                    break;
1213                }
1214
1215                // 4.e. cont: Now discard candidates that do not accept the
1216                // selected type category. Furthermore, if any candidate accepts
1217                // a preferred type in that category, discard candidates that
1218                // accept non-preferred types for that argument.
1219                let selected_category = selected_category.unwrap();
1220
1221                let preferred_type = selected_category.preferred_type();
1222                let mut found_preferred_type_candidate = false;
1223                candidates.retain(|c| {
1224                    if let Some(typ) = &preferred_type {
1225                        found_preferred_type_candidate = c.fimpl.params[i].accepts_type(ecx, typ)
1226                            || found_preferred_type_candidate;
1227                    }
1228                    selected_category == TypeCategory::from_param(&c.fimpl.params[i])
1229                });
1230
1231                if found_preferred_type_candidate {
1232                    let preferred_type = preferred_type.unwrap();
1233                    candidates.retain(|c| c.fimpl.params[i].accepts_type(ecx, &preferred_type));
1234                }
1235            }
1236            CoercibleScalarType::Coerced(typ) => {
1237                found_known = true;
1238                // Track if all known types are of the same type; use this info
1239                // in 4.f.
1240                match common_type {
1241                    Some(ref common_type) => types_match = common_type == typ && types_match,
1242                    None => common_type = Some(typ.clone()),
1243                }
1244            }
1245        }
1246    }
1247
1248    maybe_get_last_candidate!();
1249
1250    // 4.f. If there are both unknown and known-type arguments, and all the
1251    // known-type arguments have the same type, assume that the unknown
1252    // arguments are also of that type, and check which candidates can accept
1253    // that type at the unknown-argument positions.
1254    // (ed: We know unknown argument exists if we're in this part of the code.)
1255    if found_known && types_match {
1256        let common_type = common_type.unwrap();
1257        let common_typed: Vec<_> = types
1258            .iter()
1259            .map(|t| match t {
1260                CoercibleScalarType::Coerced(t) => CoercibleScalarType::Coerced(t.clone()),
1261                CoercibleScalarType::Uncoerced | CoercibleScalarType::Record(_) => {
1262                    CoercibleScalarType::Coerced(common_type.clone())
1263                }
1264            })
1265            .collect();
1266
1267        candidates.retain(|c| c.fimpl.params.matches_argtypes(ecx, &common_typed));
1268
1269        maybe_get_last_candidate!();
1270    }
1271
1272    Err(candidates.len())
1273}
1274
1275#[derive(Debug, PartialEq, Eq, PartialOrd, Ord)]
1276enum PolymorphicCompatClass {
1277    /// Represents the older "Any"-style matching of PG polymorphic types, which
1278    /// constrains all types to be of the same type, i.e. does not attempt to
1279    /// promote parameters to a best common type.
1280    Any,
1281    /// Represent's Postgres' "anycompatible"-type polymorphic resolution.
1282    ///
1283    /// > Selection of the common type considers the actual types of
1284    /// > anycompatible and anycompatiblenonarray inputs, the array element
1285    /// > types of anycompatiblearray inputs, the range subtypes of
1286    /// > anycompatiblerange inputs, and the multirange subtypes of
1287    /// > anycompatiblemultirange inputs. If anycompatiblenonarray is present
1288    /// > then the common type is required to be a non-array type. Once a common
1289    /// > type is identified, arguments in anycompatible and
1290    /// > anycompatiblenonarray positions are automatically cast to that type,
1291    /// > and arguments in anycompatiblearray positions are automatically cast
1292    /// > to the array type for that type.
1293    ///
1294    /// For details, see
1295    /// <https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC>
1296    BestCommonAny,
1297    /// Represents polymorphic compatibility operations for Materialize LIST
1298    /// types. This differs from PG's "anycompatible" type resolution, which
1299    /// focuses on determining a common type, and e.g. using that as
1300    /// `AnyCompatibleArray`'s element type. Instead, our list compatibility
1301    /// focuses on finding a common list type, and then casting
1302    /// `ListElementAnyCompatible` parameters to that list type's elements. This
1303    /// approach is necessary to let us polymorphically resolve custom list
1304    /// types without losing their OIDs.
1305    BestCommonList,
1306    /// Represents an operation similar to LIST compatibility, but for MAP. This
1307    /// is distinct from `BestCommonList` in as much as the parameter types that
1308    /// work with `BestCommonList` are incommensurate with the parameter types
1309    /// used with `BestCommonMap`.
1310    BestCommonMap,
1311    /// Represents type resolution for `SqlScalarType::Record` types, which e.g.
1312    /// ignores custom types and type modifications.
1313    ///
1314    /// In [PG], this is handled by invocation of the function calls that take
1315    /// `RecordAny` params, which we want to avoid if at all possible.
1316    ///
1317    /// [PG]:
1318    ///     https://github.com/postgres/postgres/blob/33a377608fc29cdd1f6b63be561eab0aee5c81f0/src/backend/utils/adt/rowtypes.c#L1041
1319    StructuralEq,
1320}
1321
1322impl TryFrom<&ParamType> for PolymorphicCompatClass {
1323    type Error = ();
1324    fn try_from(param: &ParamType) -> Result<PolymorphicCompatClass, Self::Error> {
1325        use ParamType::*;
1326
1327        Ok(match param {
1328            AnyElement | ArrayAny | ListAny | MapAny | NonVecAny | RangeAny => {
1329                PolymorphicCompatClass::Any
1330            }
1331            ArrayAnyCompatible | AnyCompatible | RangeAnyCompatible | NonVecAnyCompatible => {
1332                PolymorphicCompatClass::BestCommonAny
1333            }
1334            ListAnyCompatible | ListElementAnyCompatible => PolymorphicCompatClass::BestCommonList,
1335            MapAnyCompatible => PolymorphicCompatClass::BestCommonMap,
1336            RecordAny => PolymorphicCompatClass::StructuralEq,
1337            _ => return Err(()),
1338        })
1339    }
1340}
1341
1342impl PolymorphicCompatClass {
1343    fn compatible(&self, ecx: &ExprContext, from: &SqlScalarType, to: &SqlScalarType) -> bool {
1344        use PolymorphicCompatClass::*;
1345        match self {
1346            StructuralEq => from.structural_eq(to),
1347            Any => from.base_eq(to),
1348            _ => typeconv::can_cast(ecx, CastContext::Implicit, from, to),
1349        }
1350    }
1351}
1352
1353/// Represents a solution to a set of polymorphic constraints, expressed as the
1354/// `params` of a function and the user-supplied `args`.
1355#[derive(Debug)]
1356pub(crate) struct PolymorphicSolution {
1357    /// Constrains this solution to a particular form of polymorphic
1358    /// compatibility.
1359    compat: Option<PolymorphicCompatClass>,
1360    seen: Vec<CoercibleScalarType>,
1361    /// An internal representation of the discovered polymorphic type.
1362    key: Option<SqlScalarType>,
1363}
1364
1365impl PolymorphicSolution {
1366    /// Provides a solution to the polymorphic type constraints expressed in
1367    /// `params` based on the users' input in `args`. Returns `None` if a
1368    /// solution cannot be found.
1369    ///
1370    /// After constructing the `PolymorphicSolution`, access its solution using
1371    /// [`PolymorphicSolution::target_for_param_type`].
1372    fn new(
1373        ecx: &ExprContext,
1374        args: &[CoercibleScalarType],
1375        params: &ParamList,
1376    ) -> Option<PolymorphicSolution> {
1377        let mut r = PolymorphicSolution {
1378            compat: None,
1379            seen: vec![],
1380            key: None,
1381        };
1382
1383        for (i, scalar_type) in args.iter().cloned().enumerate() {
1384            r.track_seen(&params[i], scalar_type);
1385        }
1386
1387        if !r.determine_key(ecx) { None } else { Some(r) }
1388    }
1389
1390    /// Determines the desired type of polymorphic compatibility, as well as the
1391    /// values to determine a polymorphic solution.
1392    fn track_seen(&mut self, param: &ParamType, seen: CoercibleScalarType) {
1393        use ParamType::*;
1394
1395        self.seen.push(match param {
1396            // These represent the keys of their respective compatibility classes.
1397            AnyElement | AnyCompatible | ListAnyCompatible |  MapAnyCompatible | NonVecAny | RecordAny => seen,
1398            MapAny => seen.map_coerced(|array| array.unwrap_map_value_type().clone()),
1399            ListAny => seen.map_coerced(|array| array.unwrap_list_element_type().clone()),
1400            ArrayAny | ArrayAnyCompatible => seen.map_coerced(|array| array.unwrap_array_element_type().clone()),
1401            RangeAny | RangeAnyCompatible => seen.map_coerced(|range| range.unwrap_range_element_type().clone()),
1402            ListElementAnyCompatible => seen.map_coerced(|el| SqlScalarType::List {
1403                custom_id: None,
1404                element_type: Box::new(el),
1405            }),
1406            o => {
1407                assert!(
1408                    !o.is_polymorphic(),
1409                    "polymorphic parameters must track types they encounter to determine polymorphic solution"
1410                );
1411                return;
1412            }
1413        });
1414
1415        let compat_class = param
1416            .try_into()
1417            .expect("already returned for non-polymorphic params");
1418
1419        match &self.compat {
1420            None => self.compat = Some(compat_class),
1421            Some(c) => {
1422                assert_eq!(
1423                    c, &compat_class,
1424                    "do not know how to correlate polymorphic classes {:?} and {:?}",
1425                    c, &compat_class,
1426                )
1427            }
1428        };
1429    }
1430
1431    /// Attempt to resolve all polymorphic types to a single "key" type. For
1432    /// `target_for_param_type` to be useful, this must have already been
1433    /// called.
1434    fn determine_key(&mut self, ecx: &ExprContext) -> bool {
1435        self.key = if !self.seen.iter().any(|v| v.is_coerced()) {
1436            match &self.compat {
1437                // No encountered param was polymorphic
1438                None => None,
1439                // Params were polymorphic, but we never received a known type.
1440                // This cannot be delegated to `guess_best_common_type`, which
1441                // will incorrectly guess string, which is incompatible with
1442                // `BestCommonList`, `BestCommonMap`.
1443                Some(t) => match t {
1444                    PolymorphicCompatClass::BestCommonAny => Some(SqlScalarType::String),
1445                    PolymorphicCompatClass::BestCommonList => Some(SqlScalarType::List {
1446                        custom_id: None,
1447                        element_type: Box::new(SqlScalarType::String),
1448                    }),
1449                    PolymorphicCompatClass::BestCommonMap => Some(SqlScalarType::Map {
1450                        value_type: Box::new(SqlScalarType::String),
1451                        custom_id: None,
1452                    }),
1453                    // Do not infer type.
1454                    PolymorphicCompatClass::StructuralEq | PolymorphicCompatClass::Any => None,
1455                },
1456            }
1457        } else {
1458            // If we saw any polymorphic parameters, we must have determined the
1459            // compatibility type.
1460            let compat = self.compat.as_ref().unwrap();
1461
1462            let r = match compat {
1463                PolymorphicCompatClass::Any => {
1464                    let mut s = self
1465                        .seen
1466                        .iter()
1467                        .filter_map(|f| f.as_coerced().cloned())
1468                        .collect::<Vec<_>>();
1469                    let (candiate, remaining) =
1470                        s.split_first().expect("have at least one non-None element");
1471                    if remaining.iter().all(|r| r.base_eq(candiate)) {
1472                        s.remove(0)
1473                    } else {
1474                        return false;
1475                    }
1476                }
1477                _ => match typeconv::guess_best_common_type(ecx, &self.seen) {
1478                    Ok(r) => r,
1479                    Err(_) => return false,
1480                },
1481            };
1482
1483            // Ensure the best common type is compatible.
1484            for t in self.seen.iter() {
1485                if let CoercibleScalarType::Coerced(t) = t {
1486                    if !compat.compatible(ecx, t, &r) {
1487                        return false;
1488                    }
1489                }
1490            }
1491            Some(r)
1492        };
1493
1494        true
1495    }
1496
1497    // Determines the appropriate `SqlScalarType` for the given `ParamType` based
1498    // on the polymorphic solution.
1499    fn target_for_param_type(&self, param: &ParamType) -> Option<SqlScalarType> {
1500        use ParamType::*;
1501        assert_eq!(
1502            self.compat,
1503            Some(
1504                param
1505                    .try_into()
1506                    .expect("target_for_param_type only supports polymorphic parameters")
1507            ),
1508            "cannot use polymorphic solution for different compatibility classes"
1509        );
1510
1511        assert!(
1512            !matches!(param, RecordAny),
1513            "RecordAny should not be cast to a target type"
1514        );
1515
1516        match param {
1517            AnyElement | AnyCompatible | ListAnyCompatible | MapAnyCompatible | NonVecAny => {
1518                self.key.clone()
1519            }
1520            ArrayAny | ArrayAnyCompatible => self
1521                .key
1522                .as_ref()
1523                .map(|key| SqlScalarType::Array(Box::new(key.clone()))),
1524            ListAny => self.key.as_ref().map(|key| SqlScalarType::List {
1525                element_type: Box::new(key.clone()),
1526                custom_id: None,
1527            }),
1528            MapAny => self.key.as_ref().map(|key| SqlScalarType::Map {
1529                value_type: Box::new(key.clone()),
1530                custom_id: None,
1531            }),
1532            RangeAny | RangeAnyCompatible => self.key.as_ref().map(|key| SqlScalarType::Range {
1533                element_type: Box::new(key.clone()),
1534            }),
1535            ListElementAnyCompatible => self
1536                .key
1537                .as_ref()
1538                .map(|key| key.unwrap_list_element_type().clone()),
1539            _ => unreachable!(
1540                "cannot use polymorphic solution to resolve target type for param {:?}",
1541                param,
1542            ),
1543        }
1544    }
1545}
1546
1547fn coerce_args_to_types(
1548    ecx: &ExprContext,
1549    args: Vec<CoercibleScalarExpr>,
1550    params: &ParamList,
1551) -> Result<Vec<HirScalarExpr>, PlanError> {
1552    use ParamType::*;
1553
1554    let mut scalar_types: Vec<_> = args.iter().map(|e| ecx.scalar_type(e)).collect();
1555
1556    // See comment in `select_impl`.
1557    for ty in &mut scalar_types {
1558        ty.force_coerced_if_record();
1559    }
1560
1561    let polymorphic_solution = PolymorphicSolution::new(ecx, &scalar_types, params)
1562        .expect("polymorphic solution previously determined to be valid");
1563
1564    let do_convert =
1565        |arg: CoercibleScalarExpr, ty: &SqlScalarType| arg.cast_to(ecx, CastContext::Implicit, ty);
1566
1567    let mut res_exprs = Vec::with_capacity(args.len());
1568    for (i, cexpr) in args.into_iter().enumerate() {
1569        let expr = match &params[i] {
1570            Any => match cexpr {
1571                CoercibleScalarExpr::Parameter(n) => {
1572                    sql_bail!("could not determine data type of parameter ${}", n)
1573                }
1574                _ => cexpr.type_as_any(ecx)?,
1575            },
1576            RecordAny => match cexpr {
1577                CoercibleScalarExpr::LiteralString(_) => {
1578                    sql_bail!("input of anonymous composite types is not implemented");
1579                }
1580                // By passing the creation of the polymorphic solution, we've
1581                // already ensured that all of the record types are
1582                // intrinsically well-typed enough to move onto the next step.
1583                _ => cexpr.type_as_any(ecx)?,
1584            },
1585            Plain(ty) => do_convert(cexpr, ty)?,
1586            Internal => return Err(PlanError::InternalFunctionCall),
1587            p => {
1588                let target = polymorphic_solution
1589                    .target_for_param_type(p)
1590                    .ok_or_else(|| {
1591                        // n.b. This errors here, rather than during building
1592                        // the polymorphic solution, to make the error clearer.
1593                        // If we errored while constructing the polymorphic
1594                        // solution, an implementation would get discarded even
1595                        // if it were the only one, and it would appear as if a
1596                        // compatible solution did not exist. Instead, the
1597                        // problem is simply that we couldn't resolve the
1598                        // polymorphic type.
1599                        PlanError::UnsolvablePolymorphicFunctionInput
1600                    })?;
1601                do_convert(cexpr, &target)?
1602            }
1603        };
1604        res_exprs.push(expr);
1605    }
1606
1607    Ok(res_exprs)
1608}
1609
1610/// Provides shorthand for converting `Vec<SqlScalarType>` into `Vec<ParamType>`.
1611macro_rules! params {
1612    ([$($p:expr),*], $v:ident...) => { ParamList::Variadic { leading: vec![$($p.into(),)*], trailing: $v.into() } };
1613    ($v:ident...) => { ParamList::Variadic { leading: vec![], trailing: $v.into() } };
1614    ($($p:expr),*) => { ParamList::Exact(vec![$($p.into(),)*]) };
1615}
1616
1617macro_rules! impl_def {
1618    // Return type explicitly specified. This must be the case in situations
1619    // such as:
1620    // - Polymorphic functions: We have no way of understanding if the input
1621    //   type affects the return type, so you must tell us what the return type
1622    //   is.
1623    // - Explicitly defined Operations whose returned expression does not
1624    //   appropriately correlate to the function itself, e.g. returning a
1625    //   UnaryFunc from a FuncImpl that takes two parameters.
1626    // - Unimplemented/catalog-only functions
1627    ($params:expr, $op:expr, $return_type:expr, $oid:expr) => {{
1628        FuncImpl {
1629            oid: $oid,
1630            params: $params.into(),
1631            op: $op.into(),
1632            return_type: $return_type.into(),
1633        }
1634    }};
1635}
1636
1637/// Constructs builtin function map.
1638macro_rules! builtins {
1639    {
1640        $(
1641            $name:expr => $ty:ident {
1642                $($params:expr => $op:expr => $return_type:expr, $oid:expr;)+
1643            }
1644        ),+
1645    } => {{
1646
1647        let mut builtins = BTreeMap::new();
1648        $(
1649            let impls = vec![$(impl_def!($params, $op, $return_type, $oid)),+];
1650            let func = Func::$ty(impls);
1651            let expect_set_return = matches!(&func, Func::Table(_));
1652            for imp in func.func_impls() {
1653                assert_eq!(imp.return_is_set, expect_set_return, "wrong set return value for func with oid {}", imp.oid);
1654            }
1655            let old = builtins.insert($name, func);
1656            mz_ore::assert_none!(old, "duplicate entry in builtins list");
1657        )+
1658        builtins
1659    }};
1660}
1661
1662#[derive(Debug)]
1663pub struct TableFuncPlan {
1664    pub imp: TableFuncImpl,
1665    pub column_names: Vec<ColumnName>,
1666}
1667
1668/// The implementation of a table function is either
1669/// 1. just a `CallTable` HIR node (in which case we can put `WITH ORDINALITY` into it when we
1670///    create the actual HIR node in `plan_table_function_internal`),
1671/// 2. or a general HIR expression. This happens when it's implemented as SQL, i.e., by a call to
1672///    `sql_impl_table_func_inner`.
1673///
1674/// TODO(ggevay, database-issues#9598): when a table function in 2. is used with WITH ORDINALITY or
1675/// ROWS FROM, we can't use the new implementation of WITH ORDINALITY. Depending on
1676/// `enable_with_ordinality_legacy_fallback`, we either fall back to the legacy implementation or
1677/// error out the query planning. The legacy WITH ORDINALITY implementation relies on the
1678/// row_number window function, and is mostly broken. It can give an incorrect ordering, and also
1679/// has an extreme performance problem in some cases. Discussed in
1680/// <https://github.com/MaterializeInc/database-issues/issues/4764#issuecomment-2854572614>
1681///
1682/// These table functions are somewhat exotic, and WITH ORDINALITY / ROWS FROM are also somewhat
1683/// exotic, so let's hope that the combination of these is so exotic that nobody will need it for
1684/// quite a while. Note that the SQL standard only allows WITH ORDINALITY on `unnest_...` functions,
1685/// of which none fall into the 2. category, so we are fine with these; it's only a Postgres
1686/// extension to support WITH ORDINALITY on arbitrary table functions. When this combination arises,
1687/// we emit a Sentry error, so that we'll know about it.
1688///
1689/// When we eventually need to fix this, a possible approach would be to write two SQL
1690/// implementations: one would be their current implementation, and the other would be
1691/// WITH ORDINALITY.
1692/// - This will be trivial for some table functions, e.g., those that end with an UNNEST just need a
1693///   WITH ORDINALITY on this UNNEST (e.g., `regexp_split_to_table`).
1694/// - `_pg_expandarray` and `date_bin_hopping` also look easy.
1695/// - `mz_name_rank` and `mz_resolve_object_name` look more complicated but hopefully solvable.
1696///
1697/// Another approach to fixing this would be to add an ORDER BY to the SQL definitions and then
1698/// move this ORDER BY into a row_number window function call. This would at least solve the
1699/// correctness problem, but not the performance problem.
1700#[derive(Debug)]
1701pub enum TableFuncImpl {
1702    CallTable {
1703        func: TableFunc,
1704        exprs: Vec<HirScalarExpr>,
1705    },
1706    Expr(HirRelationExpr),
1707}
1708
1709#[derive(Debug)]
1710pub enum Func {
1711    Scalar(Vec<FuncImpl<HirScalarExpr>>),
1712    Aggregate(Vec<FuncImpl<(HirScalarExpr, AggregateFunc)>>),
1713    Table(Vec<FuncImpl<TableFuncPlan>>),
1714    ScalarWindow(Vec<FuncImpl<ScalarWindowFunc>>),
1715    ValueWindow(Vec<FuncImpl<(HirScalarExpr, ValueWindowFunc)>>),
1716}
1717
1718impl Func {
1719    pub fn func_impls(&self) -> Vec<FuncImplCatalogDetails> {
1720        match self {
1721            Func::Scalar(impls) => impls.iter().map(|f| f.details()).collect::<Vec<_>>(),
1722            Func::Aggregate(impls) => impls.iter().map(|f| f.details()).collect::<Vec<_>>(),
1723            Func::Table(impls) => impls.iter().map(|f| f.details()).collect::<Vec<_>>(),
1724            Func::ScalarWindow(impls) => impls.iter().map(|f| f.details()).collect::<Vec<_>>(),
1725            Func::ValueWindow(impls) => impls.iter().map(|f| f.details()).collect::<Vec<_>>(),
1726        }
1727    }
1728
1729    pub fn class(&self) -> &str {
1730        match self {
1731            Func::Scalar(..) => "scalar",
1732            Func::Aggregate(..) => "aggregate",
1733            Func::Table(..) => "table",
1734            Func::ScalarWindow(..) => "window",
1735            Func::ValueWindow(..) => "window",
1736        }
1737    }
1738}
1739
1740/// Functions using this macro should be transformed/planned away before
1741/// reaching function selection code, but still need to be present in the
1742/// catalog during planning.
1743macro_rules! catalog_name_only {
1744    ($name:expr) => {
1745        panic!(
1746            "{} should be planned away before reaching function selection",
1747            $name
1748        )
1749    };
1750}
1751
1752/// Generates an (OID, OID, TEXT) SQL implementation for has_X_privilege style functions.
1753macro_rules! privilege_fn {
1754    ( $fn_name:expr, $catalog_tbl:expr ) => {
1755        {
1756            let fn_name = $fn_name;
1757            let catalog_tbl = $catalog_tbl;
1758            let public_role = RoleId::Public;
1759            format!(
1760                "
1761                    CASE
1762                    -- We need to validate the privileges to return a proper error before anything
1763                    -- else.
1764                    WHEN NOT mz_internal.mz_validate_privileges($3)
1765                    OR $1 IS NULL
1766                    OR $2 IS NULL
1767                    OR $3 IS NULL
1768                    OR $1 NOT IN (SELECT oid FROM mz_catalog.mz_roles)
1769                    OR $2 NOT IN (SELECT oid FROM {catalog_tbl})
1770                    THEN NULL
1771                    ELSE COALESCE(
1772                        (
1773                            SELECT
1774                                bool_or(
1775                                    mz_internal.mz_acl_item_contains_privilege(privilege, $3)
1776                                )
1777                                    AS {fn_name}
1778                            FROM
1779                                (
1780                                    SELECT
1781                                        unnest(privileges)
1782                                    FROM
1783                                        {catalog_tbl}
1784                                    WHERE
1785                                        {catalog_tbl}.oid = $2
1786                                )
1787                                    AS user_privs (privilege)
1788                                LEFT JOIN mz_catalog.mz_roles ON
1789                                        mz_internal.mz_aclitem_grantee(privilege) = mz_roles.id
1790                            WHERE
1791                                mz_internal.mz_aclitem_grantee(privilege) = '{public_role}' OR pg_has_role($1, mz_roles.oid, 'USAGE')
1792                        ),
1793                        false
1794                    )
1795                    END
1796                ",
1797            )
1798        }
1799    };
1800}
1801
1802/// Correlates a built-in function name to its implementations.
1803pub static PG_CATALOG_BUILTINS: LazyLock<BTreeMap<&'static str, Func>> = LazyLock::new(|| {
1804    use ParamType::*;
1805    use ScalarBaseType::*;
1806    let mut builtins = builtins! {
1807        // Literal OIDs collected from PG 13 using a version of this query
1808        // ```sql
1809        // SELECT oid, proname, proargtypes::regtype[]
1810        // FROM pg_proc
1811        // WHERE proname IN (
1812        //      'ascii', 'array_upper', 'jsonb_build_object'
1813        // );
1814        // ```
1815        // Values are also available through
1816        // https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_proc.dat
1817
1818        // Scalars.
1819        "abs" => Scalar {
1820            params!(Int16) => UnaryFunc::AbsInt16(func::AbsInt16) => Int16, 1398;
1821            params!(Int32) => UnaryFunc::AbsInt32(func::AbsInt32) => Int32, 1397;
1822            params!(Int64) => UnaryFunc::AbsInt64(func::AbsInt64) => Int64, 1396;
1823            params!(Numeric) => UnaryFunc::AbsNumeric(func::AbsNumeric) => Numeric, 1705;
1824            params!(Float32) => UnaryFunc::AbsFloat32(func::AbsFloat32) => Float32, 1394;
1825            params!(Float64) => UnaryFunc::AbsFloat64(func::AbsFloat64) => Float64, 1395;
1826        },
1827        "aclexplode" => Table {
1828            params!(SqlScalarType::Array(Box::new(SqlScalarType::AclItem))) =>  Operation::unary(move |_ecx, aclitems| {
1829                Ok(TableFuncPlan {
1830                    imp: TableFuncImpl::CallTable {
1831                        func: TableFunc::AclExplode,
1832                        exprs: vec![aclitems],
1833                    },
1834                    column_names: vec!["grantor".into(), "grantee".into(), "privilege_type".into(), "is_grantable".into()],
1835                })
1836            }) => ReturnType::set_of(RecordAny), 1689;
1837        },
1838        "array_cat" => Scalar {
1839            params!(ArrayAnyCompatible, ArrayAnyCompatible) => Operation::binary(|_ecx, lhs, rhs| {
1840                Ok(lhs.call_binary(rhs, func::ArrayArrayConcat))
1841            }) => ArrayAnyCompatible, 383;
1842        },
1843        "array_fill" => Scalar {
1844            params!(AnyElement, SqlScalarType::Array(Box::new(SqlScalarType::Int32))) => Operation::binary(|ecx, elem, dims| {
1845                let elem_type = ecx.scalar_type(&elem);
1846
1847                let elem_type = match elem_type.array_of_self_elem_type() {
1848                    Ok(elem_type) => elem_type,
1849                    Err(elem_type) => bail_unsupported!(
1850                        // This will be used in error msgs, therefore we call with `postgres_compat` false.
1851                        format!("array_fill on {}", ecx.humanize_scalar_type(&elem_type, false))
1852                    ),
1853                };
1854
1855                Ok(HirScalarExpr::call_variadic(VariadicFunc::ArrayFill { elem_type }, vec![elem, dims]))
1856            }) => ArrayAny, 1193;
1857            params!(
1858                AnyElement,
1859                SqlScalarType::Array(Box::new(SqlScalarType::Int32)),
1860                SqlScalarType::Array(Box::new(SqlScalarType::Int32))
1861            ) => Operation::variadic(|ecx, exprs| {
1862                let elem_type = ecx.scalar_type(&exprs[0]);
1863
1864                let elem_type = match elem_type.array_of_self_elem_type() {
1865                    Ok(elem_type) => elem_type,
1866                    Err(elem_type) => bail_unsupported!(
1867                        format!("array_fill on {}", ecx.humanize_scalar_type(&elem_type, false))
1868                    ),
1869                };
1870
1871                Ok(HirScalarExpr::call_variadic(VariadicFunc::ArrayFill { elem_type }, exprs))
1872            }) => ArrayAny, 1286;
1873        },
1874        "array_length" => Scalar {
1875            params![ArrayAny, Int64] => BinaryFunc::from(func::ArrayLength) => Int32, 2176;
1876        },
1877        "array_lower" => Scalar {
1878            params!(ArrayAny, Int64) => BinaryFunc::from(func::ArrayLower) => Int32, 2091;
1879        },
1880        "array_position" => Scalar {
1881            params!(ArrayAnyCompatible, AnyCompatible) => VariadicFunc::ArrayPosition => Int32, 3277;
1882            params!(ArrayAnyCompatible, AnyCompatible, Int32) => VariadicFunc::ArrayPosition => Int32, 3278;
1883        },
1884        "array_remove" => Scalar {
1885            params!(ArrayAnyCompatible, AnyCompatible) => BinaryFunc::from(func::ArrayRemove) => ArrayAnyCompatible, 3167;
1886        },
1887        "array_to_string" => Scalar {
1888            params!(ArrayAny, String) => Operation::variadic(array_to_string) => String, 395;
1889            params!(ArrayAny, String, String) => Operation::variadic(array_to_string) => String, 384;
1890        },
1891        "array_upper" => Scalar {
1892            params!(ArrayAny, Int64) => BinaryFunc::from(func::ArrayUpper) => Int32, 2092;
1893        },
1894        "ascii" => Scalar {
1895            params!(String) => UnaryFunc::Ascii(func::Ascii) => Int32, 1620;
1896        },
1897        "avg" => Scalar {
1898            params!(Int64) => Operation::nullary(|_ecx| catalog_name_only!("avg")) => Numeric, 2100;
1899            params!(Int32) => Operation::nullary(|_ecx| catalog_name_only!("avg")) => Numeric, 2101;
1900            params!(Int16) => Operation::nullary(|_ecx| catalog_name_only!("avg")) => Numeric, 2102;
1901            params!(UInt64) => Operation::nullary(|_ecx| catalog_name_only!("avg")) => Numeric, oid::FUNC_AVG_UINT64_OID;
1902            params!(UInt32) => Operation::nullary(|_ecx| catalog_name_only!("avg")) => Numeric, oid::FUNC_AVG_UINT32_OID;
1903            params!(UInt16) => Operation::nullary(|_ecx| catalog_name_only!("avg")) => Numeric, oid::FUNC_AVG_UINT16_OID;
1904            params!(Float32) => Operation::nullary(|_ecx| catalog_name_only!("avg")) => Float64, 2104;
1905            params!(Float64) => Operation::nullary(|_ecx| catalog_name_only!("avg")) => Float64, 2105;
1906            params!(Interval) => Operation::nullary(|_ecx| catalog_name_only!("avg")) => Interval, 2106;
1907        },
1908        "bit_count" => Scalar {
1909            params!(Bytes) => UnaryFunc::BitCountBytes(func::BitCountBytes) => Int64, 6163;
1910        },
1911        "bit_length" => Scalar {
1912            params!(Bytes) => UnaryFunc::BitLengthBytes(func::BitLengthBytes) => Int32, 1810;
1913            params!(String) => UnaryFunc::BitLengthString(func::BitLengthString) => Int32, 1811;
1914        },
1915        "btrim" => Scalar {
1916            params!(String) => UnaryFunc::TrimWhitespace(func::TrimWhitespace) => String, 885;
1917            params!(String, String) => BinaryFunc::from(func::Trim) => String, 884;
1918        },
1919        "cbrt" => Scalar {
1920            params!(Float64) => UnaryFunc::CbrtFloat64(func::CbrtFloat64) => Float64, 1345;
1921        },
1922        "ceil" => Scalar {
1923            params!(Float32) => UnaryFunc::CeilFloat32(func::CeilFloat32) => Float32, oid::FUNC_CEIL_F32_OID;
1924            params!(Float64) => UnaryFunc::CeilFloat64(func::CeilFloat64) => Float64, 2308;
1925            params!(Numeric) => UnaryFunc::CeilNumeric(func::CeilNumeric) => Numeric, 1711;
1926        },
1927        "ceiling" => Scalar {
1928            params!(Float32) => UnaryFunc::CeilFloat32(func::CeilFloat32) => Float32, oid::FUNC_CEILING_F32_OID;
1929            params!(Float64) => UnaryFunc::CeilFloat64(func::CeilFloat64) => Float64, 2320;
1930            params!(Numeric) => UnaryFunc::CeilNumeric(func::CeilNumeric) => Numeric, 2167;
1931        },
1932        "char_length" => Scalar {
1933            params!(String) => UnaryFunc::CharLength(func::CharLength) => Int32, 1381;
1934        },
1935        // SQL exactly matches PostgreSQL's implementation.
1936        "col_description" => Scalar {
1937            params!(Oid, Int32) => sql_impl_func(
1938                "(SELECT description
1939                    FROM pg_description
1940                    WHERE objoid = $1 AND classoid = 'pg_class'::regclass AND objsubid = $2)"
1941                ) => String, 1216;
1942        },
1943        "concat" => Scalar {
1944            params!(Any...) => Operation::variadic(|ecx, cexprs| {
1945                if cexprs.is_empty() {
1946                    sql_bail!("No function matches the given name and argument types. \
1947                    You might need to add explicit type casts.")
1948                }
1949                let mut exprs = vec![];
1950                for expr in cexprs {
1951                    exprs.push(match ecx.scalar_type(&expr) {
1952                        // concat uses nonstandard bool -> string casts
1953                        // to match historical baggage in PostgreSQL.
1954                        SqlScalarType::Bool => expr.call_unary(UnaryFunc::CastBoolToStringNonstandard(func::CastBoolToStringNonstandard)),
1955                        // TODO(see <materialize#7572>): remove call to PadChar
1956                        SqlScalarType::Char { length } => expr.call_unary(UnaryFunc::PadChar(func::PadChar { length })),
1957                        _ => typeconv::to_string(ecx, expr)
1958                    });
1959                }
1960                Ok(HirScalarExpr::call_variadic(VariadicFunc::Concat, exprs))
1961            }) => String, 3058;
1962        },
1963        "concat_ws" => Scalar {
1964            params!([String], Any...) => Operation::variadic(|ecx, cexprs| {
1965                if cexprs.len() < 2 {
1966                    sql_bail!("No function matches the given name and argument types. \
1967                    You might need to add explicit type casts.")
1968                }
1969                let mut exprs = vec![];
1970                for expr in cexprs {
1971                    exprs.push(match ecx.scalar_type(&expr) {
1972                        // concat uses nonstandard bool -> string casts
1973                        // to match historical baggage in PostgreSQL.
1974                        SqlScalarType::Bool => expr.call_unary(UnaryFunc::CastBoolToStringNonstandard(func::CastBoolToStringNonstandard)),
1975                        // TODO(see <materialize#7572>): remove call to PadChar
1976                        SqlScalarType::Char { length } => expr.call_unary(UnaryFunc::PadChar(func::PadChar { length })),
1977                        _ => typeconv::to_string(ecx, expr)
1978                    });
1979                }
1980                Ok(HirScalarExpr::call_variadic(VariadicFunc::ConcatWs, exprs))
1981            }) => String, 3059;
1982        },
1983        "convert_from" => Scalar {
1984            params!(Bytes, String) => BinaryFunc::from(func::ConvertFrom) => String, 1714;
1985        },
1986        "cos" => Scalar {
1987            params!(Float64) => UnaryFunc::Cos(func::Cos) => Float64, 1605;
1988        },
1989        "acos" => Scalar {
1990            params!(Float64) => UnaryFunc::Acos(func::Acos) => Float64, 1601;
1991        },
1992        "cosh" => Scalar {
1993            params!(Float64) => UnaryFunc::Cosh(func::Cosh) => Float64, 2463;
1994        },
1995        "acosh" => Scalar {
1996            params!(Float64) => UnaryFunc::Acosh(func::Acosh) => Float64, 2466;
1997        },
1998        "cot" => Scalar {
1999            params!(Float64) => UnaryFunc::Cot(func::Cot) => Float64, 1607;
2000        },
2001        "current_schema" => Scalar {
2002            // TODO: this should be `name`. This is tricky in Materialize
2003            // because `name` truncates to 63 characters but Materialize does
2004            // not have a limit on identifier length.
2005            params!() => UnmaterializableFunc::CurrentSchema => String, 1402;
2006        },
2007        "current_schemas" => Scalar {
2008            params!(Bool) => Operation::unary(|_ecx, e| {
2009                Ok(HirScalarExpr::if_then_else(
2010                     e,
2011                     HirScalarExpr::call_unmaterializable(UnmaterializableFunc::CurrentSchemasWithSystem),
2012                     HirScalarExpr::call_unmaterializable(UnmaterializableFunc::CurrentSchemasWithoutSystem),
2013                ))
2014                // TODO: this should be `name[]`. This is tricky in Materialize
2015                // because `name` truncates to 63 characters but Materialize
2016                // does not have a limit on identifier length.
2017            }) => SqlScalarType::Array(Box::new(SqlScalarType::String)), 1403;
2018        },
2019        "current_database" => Scalar {
2020            params!() => UnmaterializableFunc::CurrentDatabase => String, 861;
2021        },
2022        "current_catalog" => Scalar {
2023            params!() => UnmaterializableFunc::CurrentDatabase => String, oid::FUNC_CURRENT_CATALOG;
2024        },
2025        "current_setting" => Scalar {
2026            params!(String) => Operation::unary(|_ecx, name| {
2027                current_settings(name, HirScalarExpr::literal_false())
2028            }) => SqlScalarType::String, 2077;
2029            params!(String, Bool) => Operation::binary(|_ecx, name, missing_ok| {
2030                current_settings(name, missing_ok)
2031            }) => SqlScalarType::String, 3294;
2032        },
2033        "current_timestamp" => Scalar {
2034            params!() => UnmaterializableFunc::CurrentTimestamp => TimestampTz, oid::FUNC_CURRENT_TIMESTAMP_OID;
2035        },
2036        "current_user" => Scalar {
2037            params!() => UnmaterializableFunc::CurrentUser => String, 745;
2038        },
2039        "current_role" => Scalar {
2040            params!() => UnmaterializableFunc::CurrentUser => String, oid::FUNC_CURRENT_ROLE;
2041        },
2042        "user" => Scalar {
2043            params!() => UnmaterializableFunc::CurrentUser => String, oid::FUNC_USER;
2044        },
2045        "session_user" => Scalar {
2046            params!() => UnmaterializableFunc::SessionUser => String, 746;
2047        },
2048        "chr" => Scalar {
2049            params!(Int32) => UnaryFunc::Chr(func::Chr) => String, 1621;
2050        },
2051        "date" => Scalar {
2052            params!(String) => UnaryFunc::CastStringToDate(func::CastStringToDate) => Date, oid::FUNC_DATE_FROM_TEXT;
2053            params!(Timestamp) => UnaryFunc::CastTimestampToDate(func::CastTimestampToDate) => Date, 2029;
2054            params!(TimestampTz) => UnaryFunc::CastTimestampTzToDate(func::CastTimestampTzToDate) => Date, 1178;
2055        },
2056        "date_bin" => Scalar {
2057            params!(Interval, Timestamp) => Operation::binary(|ecx, stride, source| {
2058                ecx.require_feature_flag(&vars::ENABLE_BINARY_DATE_BIN)?;
2059                Ok(stride.call_binary(source, func::DateBinTimestamp))
2060            }) => Timestamp, oid::FUNC_MZ_DATE_BIN_UNIX_EPOCH_TS_OID;
2061            params!(Interval, TimestampTz) => Operation::binary(|ecx, stride, source| {
2062                ecx.require_feature_flag(&vars::ENABLE_BINARY_DATE_BIN)?;
2063                Ok(stride.call_binary(source, func::DateBinTimestampTz))
2064            }) => TimestampTz, oid::FUNC_MZ_DATE_BIN_UNIX_EPOCH_TSTZ_OID;
2065            params!(Interval, Timestamp, Timestamp) => VariadicFunc::DateBinTimestamp => Timestamp, 6177;
2066            params!(Interval, TimestampTz, TimestampTz) => VariadicFunc::DateBinTimestampTz => TimestampTz, 6178;
2067        },
2068        "extract" => Scalar {
2069            params!(String, Interval) => BinaryFunc::from(func::DatePartIntervalNumeric) => Numeric, 6204;
2070            params!(String, Time) => BinaryFunc::from(func::DatePartTimeNumeric) => Numeric, 6200;
2071            params!(String, Timestamp) => BinaryFunc::from(func::DatePartTimestampTimestampNumeric) => Numeric, 6202;
2072            params!(String, TimestampTz) => BinaryFunc::from(func::DatePartTimestampTimestampTzNumeric) => Numeric, 6203;
2073            params!(String, Date) => BinaryFunc::from(func::ExtractDateUnits) => Numeric, 6199;
2074        },
2075        "date_part" => Scalar {
2076            params!(String, Interval) => BinaryFunc::from(func::DatePartIntervalF64) => Float64, 1172;
2077            params!(String, Time) => BinaryFunc::from(func::DatePartTimeF64) => Float64, 1385;
2078            params!(String, Timestamp) => BinaryFunc::from(func::DatePartTimestampTimestampF64) => Float64, 2021;
2079            params!(String, TimestampTz) => BinaryFunc::from(func::DatePartTimestampTimestampTzF64) => Float64, 1171;
2080        },
2081        "date_trunc" => Scalar {
2082            params!(String, Timestamp) => BinaryFunc::from(func::DateTruncUnitsTimestamp) => Timestamp, 2020;
2083            params!(String, TimestampTz) => BinaryFunc::from(func::DateTruncUnitsTimestampTz) => TimestampTz, 1217;
2084            params!(String, Interval) => BinaryFunc::from(func::DateTruncInterval) => Interval, 1218;
2085        },
2086        "daterange" => Scalar {
2087            params!(Date, Date) => Operation::variadic(|_ecx, mut exprs| {
2088                exprs.push(HirScalarExpr::literal(Datum::String("[)"), SqlScalarType::String));
2089                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Date },
2090                    exprs))
2091            }) => SqlScalarType::Range { element_type: Box::new(SqlScalarType::Date)}, 3941;
2092            params!(Date, Date, String) => Operation::variadic(|_ecx, exprs| {
2093                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Date },
2094                    exprs))
2095            }) => SqlScalarType::Range { element_type: Box::new(SqlScalarType::Date)}, 3942;
2096        },
2097        "degrees" => Scalar {
2098            params!(Float64) => UnaryFunc::Degrees(func::Degrees) => Float64, 1608;
2099        },
2100        "digest" => Scalar {
2101            params!(String, String) => BinaryFunc::from(func::DigestString) => Bytes, oid::FUNC_PG_DIGEST_STRING;
2102            params!(Bytes, String) => BinaryFunc::from(func::DigestBytes) => Bytes, oid::FUNC_PG_DIGEST_BYTES;
2103        },
2104        "exp" => Scalar {
2105            params!(Float64) => UnaryFunc::Exp(func::Exp) => Float64, 1347;
2106            params!(Numeric) => UnaryFunc::ExpNumeric(func::ExpNumeric) => Numeric, 1732;
2107        },
2108        "floor" => Scalar {
2109            params!(Float32) => UnaryFunc::FloorFloat32(func::FloorFloat32) => Float32, oid::FUNC_FLOOR_F32_OID;
2110            params!(Float64) => UnaryFunc::FloorFloat64(func::FloorFloat64) => Float64, 2309;
2111            params!(Numeric) => UnaryFunc::FloorNumeric(func::FloorNumeric) => Numeric, 1712;
2112        },
2113        "format_type" => Scalar {
2114            params!(Oid, Int32) => sql_impl_func(
2115                "CASE
2116                        WHEN $1 IS NULL THEN NULL
2117                        -- timestamp and timestamptz have the typmod in
2118                        -- a nonstandard location that requires special
2119                        -- handling.
2120                        WHEN $1 = 1114 AND $2 >= 0 THEN 'timestamp(' || $2 || ') without time zone'
2121                        WHEN $1 = 1184 AND $2 >= 0 THEN 'timestamp(' || $2 || ') with time zone'
2122                        ELSE coalesce((SELECT pg_catalog.concat(coalesce(mz_internal.mz_type_name($1), name), mz_internal.mz_render_typmod($1, $2)) FROM mz_catalog.mz_types WHERE oid = $1), '???')
2123                    END"
2124            ) => String, 1081;
2125        },
2126        "get_bit" => Scalar {
2127            params!(Bytes, Int32) => BinaryFunc::from(func::GetBit) => Int32, 723;
2128        },
2129        "get_byte" => Scalar {
2130            params!(Bytes, Int32) => BinaryFunc::from(func::GetByte) => Int32, 721;
2131        },
2132        "pg_get_ruledef" => Scalar {
2133            params!(Oid) => sql_impl_func("NULL::pg_catalog.text") => String, 1573;
2134            params!(Oid, Bool) => sql_impl_func("NULL::pg_catalog.text") => String, 2504;
2135        },
2136        "has_schema_privilege" => Scalar {
2137            params!(String, String, String) => sql_impl_func("has_schema_privilege(mz_internal.mz_role_oid($1), mz_internal.mz_schema_oid($2), $3)") => Bool, 2268;
2138            params!(String, Oid, String) => sql_impl_func("has_schema_privilege(mz_internal.mz_role_oid($1), $2, $3)") => Bool, 2269;
2139            params!(Oid, String, String) => sql_impl_func("has_schema_privilege($1, mz_internal.mz_schema_oid($2), $3)") => Bool, 2270;
2140            params!(Oid, Oid, String) => sql_impl_func(&privilege_fn!("has_schema_privilege", "mz_schemas")) => Bool, 2271;
2141            params!(String, String) => sql_impl_func("has_schema_privilege(current_user, $1, $2)") => Bool, 2272;
2142            params!(Oid, String) => sql_impl_func("has_schema_privilege(current_user, $1, $2)") => Bool, 2273;
2143        },
2144        "has_database_privilege" => Scalar {
2145            params!(String, String, String) => sql_impl_func("has_database_privilege(mz_internal.mz_role_oid($1), mz_internal.mz_database_oid($2), $3)") => Bool, 2250;
2146            params!(String, Oid, String) => sql_impl_func("has_database_privilege(mz_internal.mz_role_oid($1), $2, $3)") => Bool, 2251;
2147            params!(Oid, String, String) => sql_impl_func("has_database_privilege($1, mz_internal.mz_database_oid($2), $3)") => Bool, 2252;
2148            params!(Oid, Oid, String) => sql_impl_func(&privilege_fn!("has_database_privilege", "mz_databases")) => Bool, 2253;
2149            params!(String, String) => sql_impl_func("has_database_privilege(current_user, $1, $2)") => Bool, 2254;
2150            params!(Oid, String) => sql_impl_func("has_database_privilege(current_user, $1, $2)") => Bool, 2255;
2151        },
2152        "has_table_privilege" => Scalar {
2153            params!(String, String, String) => sql_impl_func("has_table_privilege(mz_internal.mz_role_oid($1), $2::regclass::oid, $3)") => Bool, 1922;
2154            params!(String, Oid, String) => sql_impl_func("has_table_privilege(mz_internal.mz_role_oid($1), $2, $3)") => Bool, 1923;
2155            params!(Oid, String, String) => sql_impl_func("has_table_privilege($1, $2::regclass::oid, $3)") => Bool, 1924;
2156            params!(Oid, Oid, String) => sql_impl_func(&privilege_fn!("has_table_privilege", "mz_relations")) => Bool, 1925;
2157            params!(String, String) => sql_impl_func("has_table_privilege(current_user, $1, $2)") => Bool, 1926;
2158            params!(Oid, String) => sql_impl_func("has_table_privilege(current_user, $1, $2)") => Bool, 1927;
2159        },
2160        "hmac" => Scalar {
2161            params!(String, String, String) => VariadicFunc::HmacString => Bytes, oid::FUNC_PG_HMAC_STRING;
2162            params!(Bytes, Bytes, String) => VariadicFunc::HmacBytes => Bytes, oid::FUNC_PG_HMAC_BYTES;
2163        },
2164        "initcap" => Scalar {
2165            params!(String) => UnaryFunc::Initcap(func::Initcap) => String, 872;
2166        },
2167        "int4range" => Scalar {
2168            params!(Int32, Int32) => Operation::variadic(|_ecx, mut exprs| {
2169                exprs.push(HirScalarExpr::literal(Datum::String("[)"), SqlScalarType::String));
2170                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Int32 },
2171                    exprs))
2172            }) => SqlScalarType::Range { element_type: Box::new(SqlScalarType::Int32)}, 3840;
2173            params!(Int32, Int32, String) => Operation::variadic(|_ecx, exprs| {
2174                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Int32 },
2175                    exprs))
2176            }) => SqlScalarType::Range { element_type: Box::new(SqlScalarType::Int32)}, 3841;
2177        },
2178        "int8range" => Scalar {
2179            params!(Int64, Int64) => Operation::variadic(|_ecx, mut exprs| {
2180                exprs.push(HirScalarExpr::literal(Datum::String("[)"), SqlScalarType::String));
2181                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Int64 },
2182                    exprs))
2183            }) => SqlScalarType::Range { element_type: Box::new(SqlScalarType::Int64)}, 3945;
2184            params!(Int64, Int64, String) => Operation::variadic(|_ecx, exprs| {
2185                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Int64 },
2186                    exprs))
2187                }) => SqlScalarType::Range { element_type: Box::new(SqlScalarType::Int64)}, 3946;
2188        },
2189        "isempty" => Scalar {
2190            params!(RangeAny) => UnaryFunc::RangeEmpty(func::RangeEmpty) => Bool, 3850;
2191        },
2192        "jsonb_array_length" => Scalar {
2193            params!(Jsonb) => UnaryFunc::JsonbArrayLength(func::JsonbArrayLength) => Int32, 3207;
2194        },
2195        "jsonb_build_array" => Scalar {
2196            params!() => VariadicFunc::JsonbBuildArray => Jsonb, 3272;
2197            params!(Any...) => Operation::variadic(|ecx, exprs| Ok(HirScalarExpr::call_variadic(VariadicFunc::JsonbBuildArray,
2198                exprs.into_iter().map(|e| typeconv::to_jsonb(ecx, e)).collect()))) => Jsonb, 3271;
2199        },
2200        "jsonb_build_object" => Scalar {
2201            params!() => VariadicFunc::JsonbBuildObject => Jsonb, 3274;
2202            params!(Any...) => Operation::variadic(|ecx, exprs| {
2203                if exprs.len() % 2 != 0 {
2204                    sql_bail!("argument list must have even number of elements")
2205                }
2206                Ok(HirScalarExpr::call_variadic(
2207                    VariadicFunc::JsonbBuildObject,
2208                    exprs.into_iter().tuples().map(|(key, val)| {
2209                        let key = typeconv::to_string(ecx, key);
2210                        let val = typeconv::to_jsonb(ecx, val);
2211                        vec![key, val]
2212                    }).flatten().collect()))
2213            }) => Jsonb, 3273;
2214        },
2215        "jsonb_pretty" => Scalar {
2216            params!(Jsonb) => UnaryFunc::JsonbPretty(func::JsonbPretty) => String, 3306;
2217        },
2218        "jsonb_strip_nulls" => Scalar {
2219            params!(Jsonb) => UnaryFunc::JsonbStripNulls(func::JsonbStripNulls) => Jsonb, 3262;
2220        },
2221        "jsonb_typeof" => Scalar {
2222            params!(Jsonb) => UnaryFunc::JsonbTypeof(func::JsonbTypeof) => String, 3210;
2223        },
2224        "justify_days" => Scalar {
2225            params!(Interval) => UnaryFunc::JustifyDays(func::JustifyDays) => Interval, 1295;
2226        },
2227        "justify_hours" => Scalar {
2228            params!(Interval) => UnaryFunc::JustifyHours(func::JustifyHours) => Interval, 1175;
2229        },
2230        "justify_interval" => Scalar {
2231            params!(Interval) => UnaryFunc::JustifyInterval(func::JustifyInterval) => Interval, 2711;
2232        },
2233        "left" => Scalar {
2234            params!(String, Int32) => BinaryFunc::from(func::Left) => String, 3060;
2235        },
2236        "length" => Scalar {
2237            params!(Bytes) => UnaryFunc::ByteLengthBytes(func::ByteLengthBytes) => Int32, 2010;
2238            // bpcharlen is redundant with automatic coercion to string, 1318.
2239            params!(String) => UnaryFunc::CharLength(func::CharLength) => Int32, 1317;
2240            params!(Bytes, String) => BinaryFunc::from(func::EncodedBytesCharLength) => Int32, 1713;
2241        },
2242        "like_escape" => Scalar {
2243            params!(String, String) => BinaryFunc::from(func::LikeEscape) => String, 1637;
2244        },
2245        "ln" => Scalar {
2246            params!(Float64) => UnaryFunc::Ln(func::Ln) => Float64, 1341;
2247            params!(Numeric) => UnaryFunc::LnNumeric(func::LnNumeric) => Numeric, 1734;
2248        },
2249        "log10" => Scalar {
2250            params!(Float64) => UnaryFunc::Log10(func::Log10) => Float64, 1194;
2251            params!(Numeric) => UnaryFunc::Log10Numeric(func::Log10Numeric) => Numeric, 1481;
2252        },
2253        "log" => Scalar {
2254            params!(Float64) => UnaryFunc::Log10(func::Log10) => Float64, 1340;
2255            params!(Numeric) => UnaryFunc::Log10Numeric(func::Log10Numeric) => Numeric, 1741;
2256            params!(Numeric, Numeric) => BinaryFunc::from(func::LogBaseNumeric) => Numeric, 1736;
2257        },
2258        "lower" => Scalar {
2259            params!(String) => UnaryFunc::Lower(func::Lower) => String, 870;
2260            params!(RangeAny) => UnaryFunc::RangeLower(func::RangeLower) => AnyElement, 3848;
2261        },
2262        "lower_inc" => Scalar {
2263            params!(RangeAny) => UnaryFunc::RangeLowerInc(func::RangeLowerInc) => Bool, 3851;
2264        },
2265        "lower_inf" => Scalar {
2266            params!(RangeAny) => UnaryFunc::RangeLowerInf(func::RangeLowerInf) => Bool, 3853;
2267        },
2268        "lpad" => Scalar {
2269            params!(String, Int32) => VariadicFunc::PadLeading => String, 879;
2270            params!(String, Int32, String) => VariadicFunc::PadLeading => String, 873;
2271        },
2272        "ltrim" => Scalar {
2273            params!(String) => UnaryFunc::TrimLeadingWhitespace(func::TrimLeadingWhitespace) => String, 881;
2274            params!(String, String) => BinaryFunc::from(func::TrimLeading) => String, 875;
2275        },
2276        "makeaclitem" => Scalar {
2277            params!(Oid, Oid, String, Bool) => VariadicFunc::MakeAclItem => AclItem, 1365;
2278        },
2279        "make_timestamp" => Scalar {
2280            params!(Int64, Int64, Int64, Int64, Int64, Float64) => VariadicFunc::MakeTimestamp => Timestamp, 3461;
2281        },
2282        "md5" => Scalar {
2283            params!(String) => Operation::unary(move |_ecx, input| {
2284                let algorithm = HirScalarExpr::literal(Datum::String("md5"), SqlScalarType::String);
2285                let encoding = HirScalarExpr::literal(Datum::String("hex"), SqlScalarType::String);
2286                Ok(input.call_binary(algorithm, func::DigestString).call_binary(encoding, func::Encode))
2287            }) => String, 2311;
2288            params!(Bytes) => Operation::unary(move |_ecx, input| {
2289                let algorithm = HirScalarExpr::literal(Datum::String("md5"), SqlScalarType::String);
2290                let encoding = HirScalarExpr::literal(Datum::String("hex"), SqlScalarType::String);
2291                Ok(input.call_binary(algorithm, func::DigestBytes).call_binary(encoding, func::Encode))
2292            }) => String, 2321;
2293        },
2294        "mod" => Scalar {
2295            params!(Numeric, Numeric) => Operation::nullary(|_ecx| catalog_name_only!("mod")) => Numeric, 1728;
2296            params!(Int16, Int16) => Operation::nullary(|_ecx| catalog_name_only!("mod")) => Int16, 940;
2297            params!(Int32, Int32) => Operation::nullary(|_ecx| catalog_name_only!("mod")) => Int32, 941;
2298            params!(Int64, Int64) => Operation::nullary(|_ecx| catalog_name_only!("mod")) => Int64, 947;
2299            params!(UInt16, UInt16) => Operation::nullary(|_ecx| catalog_name_only!("mod")) => UInt16, oid::FUNC_MOD_UINT16_OID;
2300            params!(UInt32, UInt32) => Operation::nullary(|_ecx| catalog_name_only!("mod")) => UInt32, oid::FUNC_MOD_UINT32_OID;
2301            params!(UInt64, UInt64) => Operation::nullary(|_ecx| catalog_name_only!("mod")) => UInt64, oid::FUNC_MOD_UINT64_OID;
2302        },
2303        "normalize" => Scalar {
2304            // Parser always provides two arguments (defaults second to "NFC" when omitted)
2305            params!(String, String) => BinaryFunc::Normalize => String, oid::FUNC_NORMALIZE_OID;
2306        },
2307        "now" => Scalar {
2308            params!() => UnmaterializableFunc::CurrentTimestamp => TimestampTz, 1299;
2309        },
2310        "numrange" => Scalar {
2311            params!(Numeric, Numeric) => Operation::variadic(|_ecx, mut exprs| {
2312                exprs.push(HirScalarExpr::literal(Datum::String("[)"), SqlScalarType::String));
2313                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Numeric { max_scale: None } },
2314                    exprs))
2315            }) =>  SqlScalarType::Range { element_type: Box::new(SqlScalarType::Numeric { max_scale: None })}, 3844;
2316            params!(Numeric, Numeric, String) => Operation::variadic(|_ecx, exprs| {
2317                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Numeric { max_scale: None } },
2318                    exprs))
2319            }) => SqlScalarType::Range { element_type: Box::new(SqlScalarType::Numeric { max_scale: None })}, 3845;
2320        },
2321        "octet_length" => Scalar {
2322            params!(Bytes) => UnaryFunc::ByteLengthBytes(func::ByteLengthBytes) => Int32, 720;
2323            params!(String) => UnaryFunc::ByteLengthString(func::ByteLengthString) => Int32, 1374;
2324            params!(Char) => Operation::unary(|ecx, e| {
2325                let length = ecx.scalar_type(&e).unwrap_char_length();
2326                Ok(e.call_unary(UnaryFunc::PadChar(func::PadChar { length }))
2327                    .call_unary(UnaryFunc::ByteLengthString(func::ByteLengthString))
2328                )
2329            }) => Int32, 1375;
2330        },
2331        // SQL closely matches PostgreSQL's implementation.
2332        // We don't yet support casting to regnamespace, so use our constant for
2333        // the oid of 'pg_catalog'.
2334        "obj_description" => Scalar {
2335            params!(Oid, String) => sql_impl_func(&format!(
2336                "(SELECT description FROM pg_description
2337                  WHERE objoid = $1
2338                    AND classoid = (
2339                      SELECT oid FROM pg_class WHERE relname = $2 AND relnamespace = {})
2340                    AND objsubid = 0)",
2341                oid::SCHEMA_PG_CATALOG_OID
2342            )) => String, 1215;
2343        },
2344        "pg_column_size" => Scalar {
2345            params!(Any) => UnaryFunc::PgColumnSize(func::PgColumnSize) => Int32, 1269;
2346        },
2347        "pg_size_pretty" => Scalar {
2348            params!(Numeric) => UnaryFunc::PgSizePretty(func::PgSizePretty) => String, 3166;
2349        },
2350        "mz_row_size" => Scalar {
2351            params!(Any) => Operation::unary(|ecx, e| {
2352                let s = ecx.scalar_type(&e);
2353                if !matches!(s, SqlScalarType::Record{..}) {
2354                    sql_bail!("mz_row_size requires a record type");
2355                }
2356                Ok(e.call_unary(UnaryFunc::MzRowSize(func::MzRowSize)))
2357            }) => Int32, oid::FUNC_MZ_ROW_SIZE;
2358        },
2359        "parse_ident" => Scalar {
2360            params!(String) => Operation::unary(|_ecx, ident| {
2361                Ok(ident.call_binary(HirScalarExpr::literal_true(), func::ParseIdent))
2362            }) => SqlScalarType::Array(Box::new(SqlScalarType::String)),
2363                oid::FUNC_PARSE_IDENT_DEFAULT_STRICT;
2364            params!(String, Bool) => BinaryFunc::from(func::ParseIdent)
2365                => SqlScalarType::Array(Box::new(SqlScalarType::String)), 1268;
2366        },
2367        "pg_encoding_to_char" => Scalar {
2368            // Materialize only supports UT8-encoded databases. Return 'UTF8' if Postgres'
2369            // encoding id for UTF8 (6) is provided, otherwise return 'NULL'.
2370            params!(Int64) => sql_impl_func("CASE WHEN $1 = 6 THEN 'UTF8' ELSE NULL END") => String, 1597;
2371        },
2372        "pg_backend_pid" => Scalar {
2373            params!() => UnmaterializableFunc::PgBackendPid => Int32, 2026;
2374        },
2375        // pg_get_constraintdef gives more info about a constraint within the `pg_constraint`
2376        // view. Certain meta commands rely on this function not throwing an error, but the
2377        // `pg_constraint` view is empty in materialize. Therefore we know any oid provided is
2378        // not a valid constraint, so we can return NULL which is what PostgreSQL does when
2379        // provided an invalid OID.
2380        "pg_get_constraintdef" => Scalar {
2381            params!(Oid) => Operation::unary(|_ecx, _oid|
2382                Ok(HirScalarExpr::literal_null(SqlScalarType::String))) => String, 1387;
2383            params!(Oid, Bool) => Operation::binary(|_ecx, _oid, _pretty|
2384                Ok(HirScalarExpr::literal_null(SqlScalarType::String))) => String, 2508;
2385        },
2386        // pg_get_indexdef reconstructs the creating command for an index. We only support
2387        // arrangement based indexes, so we can hardcode that in.
2388        // TODO(jkosh44): In order to include the index WITH options, they will need to be saved somewhere in the catalog
2389        "pg_get_indexdef" => Scalar {
2390            params!(Oid) => sql_impl_func(
2391                "(SELECT 'CREATE INDEX ' || i.name || ' ON ' || r.name || ' USING arrangement (' || (
2392                        SELECT pg_catalog.string_agg(cols.col_exp, ',' ORDER BY cols.index_position)
2393                        FROM (
2394                            SELECT c.name AS col_exp, ic.index_position
2395                            FROM mz_catalog.mz_index_columns AS ic
2396                            JOIN mz_catalog.mz_indexes AS i2 ON ic.index_id = i2.id
2397                            JOIN mz_catalog.mz_columns AS c ON i2.on_id = c.id AND ic.on_position = c.position
2398                            WHERE ic.index_id = i.id AND ic.on_expression IS NULL
2399                            UNION
2400                            SELECT ic.on_expression AS col_exp, ic.index_position
2401                            FROM mz_catalog.mz_index_columns AS ic
2402                            WHERE ic.index_id = i.id AND ic.on_expression IS NOT NULL
2403                        ) AS cols
2404                    ) || ')'
2405                    FROM mz_catalog.mz_indexes AS i
2406                    JOIN mz_catalog.mz_relations AS r ON i.on_id = r.id
2407                    WHERE i.oid = $1)"
2408            ) => String, 1643;
2409            // A position of 0 is treated as if no position was given.
2410            // Third parameter, pretty, is ignored.
2411            params!(Oid, Int32, Bool) => sql_impl_func(
2412                "(SELECT CASE WHEN $2 = 0 THEN pg_catalog.pg_get_indexdef($1) ELSE
2413                        (SELECT c.name
2414                        FROM mz_catalog.mz_indexes AS i
2415                        JOIN mz_catalog.mz_index_columns AS ic ON i.id = ic.index_id
2416                        JOIN mz_catalog.mz_columns AS c ON i.on_id = c.id AND ic.on_position = c.position
2417                        WHERE i.oid = $1 AND ic.on_expression IS NULL AND ic.index_position = $2
2418                        UNION
2419                        SELECT ic.on_expression
2420                        FROM mz_catalog.mz_indexes AS i
2421                        JOIN mz_catalog.mz_index_columns AS ic ON i.id = ic.index_id
2422                        WHERE i.oid = $1 AND ic.on_expression IS NOT NULL AND ic.index_position = $2)
2423                    END)"
2424            ) => String, 2507;
2425        },
2426        // pg_get_viewdef returns the (query part of) the given view's definition.
2427        // We currently don't support pretty-printing (the `Bool`/`Int32` parameters).
2428        "pg_get_viewdef" => Scalar {
2429            params!(String) => sql_impl_func(
2430                "(SELECT definition FROM mz_catalog.mz_views WHERE name = $1)"
2431            ) => String, 1640;
2432            params!(Oid) => sql_impl_func(
2433                "(SELECT definition FROM mz_catalog.mz_views WHERE oid = $1)"
2434            ) => String, 1641;
2435            params!(String, Bool) => sql_impl_func(
2436                "(SELECT definition FROM mz_catalog.mz_views WHERE name = $1)"
2437            ) => String, 2505;
2438            params!(Oid, Bool) => sql_impl_func(
2439                "(SELECT definition FROM mz_catalog.mz_views WHERE oid = $1)"
2440            ) => String, 2506;
2441            params!(Oid, Int32) => sql_impl_func(
2442                "(SELECT definition FROM mz_catalog.mz_views WHERE oid = $1)"
2443            ) => String, 3159;
2444        },
2445        // pg_get_expr is meant to convert the textual version of
2446        // pg_node_tree data into parseable expressions. However, we don't
2447        // use the pg_get_expr structure anywhere and the equivalent columns
2448        // in Materialize (e.g. index expressions) are already stored as
2449        // parseable expressions. So, we offer this function in the catalog
2450        // for ORM support, but make no effort to provide its semantics,
2451        // e.g. this also means we drop the Oid argument on the floor.
2452        "pg_get_expr" => Scalar {
2453            params!(String, Oid) => Operation::binary(|_ecx, l, _r| Ok(l)) => String, 1716;
2454            params!(String, Oid, Bool) => Operation::variadic(move |_ecx, mut args| Ok(args.remove(0))) => String, 2509;
2455        },
2456        "pg_get_userbyid" => Scalar {
2457            params!(Oid) => sql_impl_func(
2458                "CASE \
2459                   WHEN $1 IS NULL THEN NULL \
2460                   ELSE COALESCE(\
2461                     (SELECT name FROM mz_catalog.mz_roles WHERE oid = $1),\
2462                     'unknown (OID=' || $1 || ')'\
2463                   ) \
2464                END"
2465            ) => String, 1642;
2466        },
2467        // The privilege param is validated but ignored. That's because we haven't implemented
2468        // NOINHERIT roles, so it has no effect on the result.
2469        //
2470        // In PostgreSQL, this should always return true for superusers. In Materialize it's
2471        // impossible to determine if a role is a superuser since it's specific to a session. So we
2472        // cannot copy PostgreSQL semantics there.
2473        "pg_has_role" => Scalar {
2474            params!(String, String, String) => sql_impl_func("pg_has_role(mz_internal.mz_role_oid($1), mz_internal.mz_role_oid($2), $3)") => Bool, 2705;
2475            params!(String, Oid, String) => sql_impl_func("pg_has_role(mz_internal.mz_role_oid($1), $2, $3)") => Bool, 2706;
2476            params!(Oid, String, String) => sql_impl_func("pg_has_role($1, mz_internal.mz_role_oid($2), $3)") => Bool, 2707;
2477            params!(Oid, Oid, String) => sql_impl_func(
2478                "CASE
2479                -- We need to validate the privilege to return a proper error before anything
2480                -- else.
2481                WHEN NOT mz_internal.mz_validate_role_privilege($3)
2482                OR $1 IS NULL
2483                OR $2 IS NULL
2484                OR $3 IS NULL
2485                THEN NULL
2486                WHEN $1 NOT IN (SELECT oid FROM mz_catalog.mz_roles)
2487                OR $2 NOT IN (SELECT oid FROM mz_catalog.mz_roles)
2488                THEN false
2489                ELSE $2::text IN (SELECT UNNEST(mz_internal.mz_role_oid_memberships() -> $1::text))
2490                END",
2491            ) => Bool, 2708;
2492            params!(String, String) => sql_impl_func("pg_has_role(current_user, $1, $2)") => Bool, 2709;
2493            params!(Oid, String) => sql_impl_func("pg_has_role(current_user, $1, $2)") => Bool, 2710;
2494        },
2495        // pg_is_in_recovery indicates whether a recovery is still in progress. Materialize does
2496        // not have a concept of recovery, so we default to always returning false.
2497        "pg_is_in_recovery" => Scalar {
2498            params!() => Operation::nullary(|_ecx| {
2499                Ok(HirScalarExpr::literal_false())
2500            }) => Bool, 3810;
2501        },
2502        "pg_postmaster_start_time" => Scalar {
2503            params!() => UnmaterializableFunc::PgPostmasterStartTime => TimestampTz, 2560;
2504        },
2505        "pg_relation_size" => Scalar {
2506            params!(RegClass, String) => sql_impl_func("CASE WHEN $1 IS NULL OR $2 IS NULL THEN NULL ELSE -1::pg_catalog.int8 END") => Int64, 2332;
2507            params!(RegClass) => sql_impl_func("CASE WHEN $1 IS NULL THEN NULL ELSE -1::pg_catalog.int8 END") => Int64, 2325;
2508        },
2509        "pg_stat_get_numscans" => Scalar {
2510            params!(Oid) => sql_impl_func("CASE WHEN $1 IS NULL THEN NULL ELSE -1::pg_catalog.int8 END") => Int64, 1928;
2511        },
2512        "pg_table_is_visible" => Scalar {
2513            params!(Oid) => sql_impl_func(
2514                "(SELECT s.name = ANY(pg_catalog.current_schemas(true))
2515                     FROM mz_catalog.mz_objects o JOIN mz_catalog.mz_schemas s ON o.schema_id = s.id
2516                     WHERE o.oid = $1)"
2517            ) => Bool, 2079;
2518        },
2519        "pg_type_is_visible" => Scalar {
2520            params!(Oid) => sql_impl_func(
2521                "(SELECT s.name = ANY(pg_catalog.current_schemas(true))
2522                     FROM mz_catalog.mz_types t JOIN mz_catalog.mz_schemas s ON t.schema_id = s.id
2523                     WHERE t.oid = $1)"
2524            ) => Bool, 2080;
2525        },
2526        "pg_function_is_visible" => Scalar {
2527            params!(Oid) => sql_impl_func(
2528                "(SELECT s.name = ANY(pg_catalog.current_schemas(true))
2529                     FROM mz_catalog.mz_functions f JOIN mz_catalog.mz_schemas s ON f.schema_id = s.id
2530                     WHERE f.oid = $1)"
2531            ) => Bool, 2081;
2532        },
2533        // pg_tablespace_location indicates what path in the filesystem that a given tablespace is
2534        // located in. This concept does not make sense though in Materialize which is a cloud
2535        // native database, so we just return the null value.
2536        "pg_tablespace_location" => Scalar {
2537            params!(Oid) => Operation::unary(|_ecx, _e| {
2538                Ok(HirScalarExpr::literal_null(SqlScalarType::String))
2539            }) => String, 3778;
2540        },
2541        "pg_typeof" => Scalar {
2542            params!(Any) => Operation::new(|ecx, exprs, params, _order_by| {
2543                // pg_typeof reports the type *before* coercion.
2544                let name = match ecx.scalar_type(&exprs[0]) {
2545                    CoercibleScalarType::Uncoerced => "unknown".to_string(),
2546                    CoercibleScalarType::Record(_) => "record".to_string(),
2547                    CoercibleScalarType::Coerced(ty) => ecx.humanize_scalar_type(&ty, true),
2548                };
2549
2550                // For consistency with other functions, verify that
2551                // coercion is possible, though we don't actually care about
2552                // the coerced results.
2553                coerce_args_to_types(ecx, exprs, params)?;
2554
2555                // TODO(benesch): make this function have return type
2556                // regtype, when we support that type. Document the function
2557                // at that point. For now, it's useful enough to have this
2558                // halfway version that returns a string.
2559                Ok(HirScalarExpr::literal(Datum::String(&name), SqlScalarType::String))
2560            }) => String, 1619;
2561        },
2562        "position" => Scalar {
2563            params!(String, String) => BinaryFunc::from(func::Position) => Int32, 849;
2564        },
2565        "pow" => Scalar {
2566            params!(Float64, Float64) => Operation::nullary(|_ecx| catalog_name_only!("pow")) => Float64, 1346;
2567        },
2568        "power" => Scalar {
2569            params!(Float64, Float64) => BinaryFunc::from(func::Power) => Float64, 1368;
2570            params!(Numeric, Numeric) => BinaryFunc::from(func::PowerNumeric) => Numeric, 2169;
2571        },
2572        "quote_ident" => Scalar {
2573            params!(String) => UnaryFunc::QuoteIdent(func::QuoteIdent) => String, 1282;
2574        },
2575        "radians" => Scalar {
2576            params!(Float64) => UnaryFunc::Radians(func::Radians) => Float64, 1609;
2577        },
2578        "repeat" => Scalar {
2579            params!(String, Int32) => BinaryFunc::RepeatString => String, 1622;
2580        },
2581        "regexp_match" => Scalar {
2582            params!(String, String) => VariadicFunc::RegexpMatch => SqlScalarType::Array(Box::new(SqlScalarType::String)), 3396;
2583            params!(String, String, String) => VariadicFunc::RegexpMatch => SqlScalarType::Array(Box::new(SqlScalarType::String)), 3397;
2584        },
2585        "replace" => Scalar {
2586            params!(String, String, String) => VariadicFunc::Replace => String, 2087;
2587        },
2588        "right" => Scalar {
2589            params!(String, Int32) => BinaryFunc::from(func::Right) => String, 3061;
2590        },
2591        "round" => Scalar {
2592            params!(Float32) => UnaryFunc::RoundFloat32(func::RoundFloat32) => Float32, oid::FUNC_ROUND_F32_OID;
2593            params!(Float64) => UnaryFunc::RoundFloat64(func::RoundFloat64) => Float64, 1342;
2594            params!(Numeric) => UnaryFunc::RoundNumeric(func::RoundNumeric) => Numeric, 1708;
2595            params!(Numeric, Int32) => BinaryFunc::from(func::RoundNumericBinary) => Numeric, 1707;
2596        },
2597        "rtrim" => Scalar {
2598            params!(String) => UnaryFunc::TrimTrailingWhitespace(func::TrimTrailingWhitespace) => String, 882;
2599            params!(String, String) => BinaryFunc::from(func::TrimTrailing) => String, 876;
2600        },
2601        "sha224" => Scalar {
2602            params!(Bytes) => digest("sha224") => Bytes, 3419;
2603        },
2604        "sha256" => Scalar {
2605            params!(Bytes) => digest("sha256") => Bytes, 3420;
2606        },
2607        "sha384" => Scalar {
2608            params!(Bytes) => digest("sha384") => Bytes, 3421;
2609        },
2610        "sha512" => Scalar {
2611            params!(Bytes) => digest("sha512") => Bytes, 3422;
2612        },
2613        "sin" => Scalar {
2614            params!(Float64) => UnaryFunc::Sin(func::Sin) => Float64, 1604;
2615        },
2616        "asin" => Scalar {
2617            params!(Float64) => UnaryFunc::Asin(func::Asin) => Float64, 1600;
2618        },
2619        "sinh" => Scalar {
2620            params!(Float64) => UnaryFunc::Sinh(func::Sinh) => Float64, 2462;
2621        },
2622        "asinh" => Scalar {
2623            params!(Float64) => UnaryFunc::Asinh(func::Asinh) => Float64, 2465;
2624        },
2625        "split_part" => Scalar {
2626            params!(String, String, Int32) => VariadicFunc::SplitPart => String, 2088;
2627        },
2628        "stddev" => Scalar {
2629            params!(Float32) => Operation::nullary(|_ecx| catalog_name_only!("stddev")) => Float64, 2157;
2630            params!(Float64) => Operation::nullary(|_ecx| catalog_name_only!("stddev")) => Float64, 2158;
2631            params!(Int16) => Operation::nullary(|_ecx| catalog_name_only!("stddev")) => Numeric, 2156;
2632            params!(Int32) => Operation::nullary(|_ecx| catalog_name_only!("stddev")) => Numeric, 2155;
2633            params!(Int64) => Operation::nullary(|_ecx| catalog_name_only!("stddev")) => Numeric, 2154;
2634            params!(UInt16) => Operation::nullary(|_ecx| catalog_name_only!("stddev")) => Numeric, oid::FUNC_STDDEV_UINT16_OID;
2635            params!(UInt32) => Operation::nullary(|_ecx| catalog_name_only!("stddev")) => Numeric, oid::FUNC_STDDEV_UINT32_OID;
2636            params!(UInt64) => Operation::nullary(|_ecx| catalog_name_only!("stddev")) => Numeric, oid::FUNC_STDDEV_UINT64_OID;
2637        },
2638        "stddev_pop" => Scalar {
2639            params!(Float32) => Operation::nullary(|_ecx| catalog_name_only!("stddev_pop")) => Float64, 2727;
2640            params!(Float64) => Operation::nullary(|_ecx| catalog_name_only!("stddev_pop")) => Float64, 2728;
2641            params!(Int16) => Operation::nullary(|_ecx| catalog_name_only!("stddev_pop")) => Numeric, 2726;
2642            params!(Int32) => Operation::nullary(|_ecx| catalog_name_only!("stddev_pop")) => Numeric, 2725;
2643            params!(Int64) => Operation::nullary(|_ecx| catalog_name_only!("stddev_pop")) => Numeric, 2724;
2644            params!(UInt16) => Operation::nullary(|_ecx| catalog_name_only!("stddev_pop")) => Numeric, oid::FUNC_STDDEV_POP_UINT16_OID;
2645            params!(UInt32) => Operation::nullary(|_ecx| catalog_name_only!("stddev_pop")) => Numeric, oid::FUNC_STDDEV_POP_UINT32_OID;
2646            params!(UInt64) => Operation::nullary(|_ecx| catalog_name_only!("stddev_pop")) => Numeric, oid::FUNC_STDDEV_POP_UINT64_OID;
2647        },
2648        "stddev_samp" => Scalar {
2649            params!(Float32) => Operation::nullary(|_ecx| catalog_name_only!("stddev_samp")) => Float64, 2715;
2650            params!(Float64) => Operation::nullary(|_ecx| catalog_name_only!("stddev_samp")) => Float64, 2716;
2651            params!(Int16) => Operation::nullary(|_ecx| catalog_name_only!("stddev_samp")) => Numeric, 2714;
2652            params!(Int32) => Operation::nullary(|_ecx| catalog_name_only!("stddev_samp")) => Numeric, 2713;
2653            params!(Int64) => Operation::nullary(|_ecx| catalog_name_only!("stddev_samp")) => Numeric, 2712;
2654            params!(UInt16) => Operation::nullary(|_ecx| catalog_name_only!("stddev_samp")) => Numeric, oid::FUNC_STDDEV_SAMP_UINT16_OID;
2655            params!(UInt32) => Operation::nullary(|_ecx| catalog_name_only!("stddev_samp")) => Numeric, oid::FUNC_STDDEV_SAMP_UINT32_OID;
2656            params!(UInt64) => Operation::nullary(|_ecx| catalog_name_only!("stddev_samp")) => Numeric, oid::FUNC_STDDEV_SAMP_UINT64_OID;
2657        },
2658        "substr" => Scalar {
2659            params!(String, Int32) => VariadicFunc::Substr => String, 883;
2660            params!(String, Int32, Int32) => VariadicFunc::Substr => String, 877;
2661        },
2662        "substring" => Scalar {
2663            params!(String, Int32) => VariadicFunc::Substr => String, 937;
2664            params!(String, Int32, Int32) => VariadicFunc::Substr => String, 936;
2665        },
2666        "sqrt" => Scalar {
2667            params!(Float64) => UnaryFunc::SqrtFloat64(func::SqrtFloat64) => Float64, 1344;
2668            params!(Numeric) => UnaryFunc::SqrtNumeric(func::SqrtNumeric) => Numeric, 1730;
2669        },
2670        "tan" => Scalar {
2671            params!(Float64) => UnaryFunc::Tan(func::Tan) => Float64, 1606;
2672        },
2673        "atan" => Scalar {
2674            params!(Float64) => UnaryFunc::Atan(func::Atan) => Float64, 1602;
2675        },
2676        "tanh" => Scalar {
2677            params!(Float64) => UnaryFunc::Tanh(func::Tanh) => Float64, 2464;
2678        },
2679        "atanh" => Scalar {
2680            params!(Float64) => UnaryFunc::Atanh(func::Atanh) => Float64, 2467;
2681        },
2682        "age" => Scalar {
2683            params!(Timestamp, Timestamp) => BinaryFunc::from(func::AgeTimestamp) => Interval, 2058;
2684            params!(TimestampTz, TimestampTz) => BinaryFunc::from(func::AgeTimestampTz) => Interval, 1199;
2685        },
2686        "timezone" => Scalar {
2687            params!(String, Timestamp) => BinaryFunc::TimezoneTimestamp => TimestampTz, 2069;
2688            params!(String, TimestampTz) => BinaryFunc::TimezoneTimestampTz => Timestamp, 1159;
2689            // PG defines this as `text timetz`
2690            params!(String, Time) => Operation::binary(|ecx, lhs, rhs| {
2691                // NOTE: this overload is wrong. It should take and return a
2692                // `timetz`, which is a type we don't support because it has
2693                // inscrutable semantics (timezones are meaningless without a
2694                // date). This implementation attempted to extend those already
2695                // inscrutable semantics to the `time` type, which makes matters
2696                // even worse.
2697                //
2698                // This feature flag ensures we don't get *new* uses of this
2699                // function. At some point in the future, we should either
2700                // remove this overload entirely, after validating there are no
2701                // catalogs in production that rely on this overload, or we
2702                // should properly support the `timetz` type and adjust this
2703                // overload accordingly.
2704                ecx.require_feature_flag(&ENABLE_TIME_AT_TIME_ZONE)?;
2705                Ok(HirScalarExpr::call_variadic(
2706                    VariadicFunc::TimezoneTime,
2707                    vec![
2708                        lhs,
2709                        rhs,
2710                        HirScalarExpr::call_unmaterializable(UnmaterializableFunc::CurrentTimestamp),
2711                    ],
2712                ))
2713            }) => Time, 2037;
2714            params!(Interval, Timestamp) => BinaryFunc::TimezoneIntervalTimestamp => TimestampTz, 2070;
2715            params!(Interval, TimestampTz) => BinaryFunc::TimezoneIntervalTimestampTz => Timestamp, 1026;
2716            // PG defines this as `interval timetz`
2717            params!(Interval, Time) => BinaryFunc::TimezoneIntervalTime => Time, 2038;
2718        },
2719        "to_char" => Scalar {
2720            params!(Timestamp, String) => BinaryFunc::from(func::ToCharTimestampFormat) => String, 2049;
2721            params!(TimestampTz, String) => BinaryFunc::from(func::ToCharTimestampTzFormat) => String, 1770;
2722        },
2723        // > Returns the value as json or jsonb. Arrays and composites
2724        // > are converted (recursively) to arrays and objects;
2725        // > otherwise, if there is a cast from the type to json, the
2726        // > cast function will be used to perform the conversion;
2727        // > otherwise, a scalar value is produced. For any scalar type
2728        // > other than a number, a Boolean, or a null value, the text
2729        // > representation will be used, in such a fashion that it is a
2730        // > valid json or jsonb value.
2731        //
2732        // https://www.postgresql.org/docs/current/functions-json.html
2733        "to_jsonb" => Scalar {
2734            params!(Any) => Operation::unary(|ecx, e| {
2735                // TODO(see <materialize#7572>): remove this
2736                let e = match ecx.scalar_type(&e) {
2737                    SqlScalarType::Char { length } => e.call_unary(UnaryFunc::PadChar(func::PadChar { length })),
2738                    _ => e,
2739                };
2740                Ok(typeconv::to_jsonb(ecx, e))
2741            }) => Jsonb, 3787;
2742        },
2743        "to_timestamp" => Scalar {
2744            params!(Float64) => UnaryFunc::ToTimestamp(func::ToTimestamp) => TimestampTz, 1158;
2745        },
2746        "translate" => Scalar {
2747            params!(String, String, String) => VariadicFunc::Translate => String, 878;
2748        },
2749        "trunc" => Scalar {
2750            params!(Float32) => UnaryFunc::TruncFloat32(func::TruncFloat32) => Float32, oid::FUNC_TRUNC_F32_OID;
2751            params!(Float64) => UnaryFunc::TruncFloat64(func::TruncFloat64) => Float64, 1343;
2752            params!(Numeric) => UnaryFunc::TruncNumeric(func::TruncNumeric) => Numeric, 1710;
2753        },
2754        "tsrange" => Scalar {
2755            params!(Timestamp, Timestamp) => Operation::variadic(|_ecx, mut exprs| {
2756                exprs.push(HirScalarExpr::literal(Datum::String("[)"), SqlScalarType::String));
2757                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Timestamp {precision: None}},
2758                    exprs))
2759            }) =>  SqlScalarType::Range { element_type: Box::new(SqlScalarType::Timestamp { precision: None})}, 3933;
2760            params!(Timestamp, Timestamp, String) => Operation::variadic(|_ecx, exprs| {
2761                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::Timestamp {precision: None}},
2762                    exprs))
2763            }) => SqlScalarType::Range { element_type: Box::new(SqlScalarType::Timestamp { precision: None})}, 3934;
2764        },
2765        "tstzrange" => Scalar {
2766            params!(TimestampTz, TimestampTz) => Operation::variadic(|_ecx, mut exprs| {
2767                exprs.push(HirScalarExpr::literal(Datum::String("[)"), SqlScalarType::String));
2768                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::TimestampTz {precision: None}},
2769                    exprs,))
2770            }) =>  SqlScalarType::Range { element_type: Box::new(SqlScalarType::TimestampTz { precision: None})}, 3937;
2771            params!(TimestampTz, TimestampTz, String) => Operation::variadic(|_ecx, exprs| {
2772                Ok(HirScalarExpr::call_variadic(VariadicFunc::RangeCreate { elem_type: SqlScalarType::TimestampTz {precision: None}},
2773                    exprs))
2774            }) => SqlScalarType::Range { element_type: Box::new(SqlScalarType::TimestampTz { precision: None})}, 3938;
2775        },
2776        "upper" => Scalar {
2777            params!(String) => UnaryFunc::Upper(func::Upper) => String, 871;
2778            params!(RangeAny) => UnaryFunc::RangeUpper(func::RangeUpper) => AnyElement, 3849;
2779        },
2780        "upper_inc" => Scalar {
2781            params!(RangeAny) => UnaryFunc::RangeUpperInc(func::RangeUpperInc) => Bool, 3852;
2782        },
2783        "upper_inf" => Scalar {
2784            params!(RangeAny) => UnaryFunc::RangeUpperInf(func::RangeUpperInf) => Bool, 3854;
2785        },
2786        "uuid_generate_v5" => Scalar {
2787            params!(Uuid, String) => BinaryFunc::from(func::UuidGenerateV5) => Uuid, oid::FUNC_PG_UUID_GENERATE_V5;
2788        },
2789        "variance" => Scalar {
2790            params!(Float32) => Operation::nullary(|_ecx| catalog_name_only!("variance")) => Float64, 2151;
2791            params!(Float64) => Operation::nullary(|_ecx| catalog_name_only!("variance")) => Float64, 2152;
2792            params!(Int16) => Operation::nullary(|_ecx| catalog_name_only!("variance")) => Numeric, 2150;
2793            params!(Int32) => Operation::nullary(|_ecx| catalog_name_only!("variance")) => Numeric, 2149;
2794            params!(Int64) => Operation::nullary(|_ecx| catalog_name_only!("variance")) => Numeric, 2148;
2795            params!(UInt16) => Operation::nullary(|_ecx| catalog_name_only!("variance")) => Numeric, oid::FUNC_VARIANCE_UINT16_OID;
2796            params!(UInt32) => Operation::nullary(|_ecx| catalog_name_only!("variance")) => Numeric, oid::FUNC_VARIANCE_UINT32_OID;
2797            params!(UInt64) => Operation::nullary(|_ecx| catalog_name_only!("variance")) => Numeric, oid::FUNC_VARIANCE_UINT64_OID;
2798        },
2799        "var_pop" => Scalar {
2800            params!(Float32) => Operation::nullary(|_ecx| catalog_name_only!("var_pop")) => Float64, 2721;
2801            params!(Float64) => Operation::nullary(|_ecx| catalog_name_only!("var_pop")) => Float64, 2722;
2802            params!(Int16) => Operation::nullary(|_ecx| catalog_name_only!("var_pop")) => Numeric, 2720;
2803            params!(Int32) => Operation::nullary(|_ecx| catalog_name_only!("var_pop")) => Numeric, 2719;
2804            params!(Int64) => Operation::nullary(|_ecx| catalog_name_only!("var_pop")) => Numeric, 2718;
2805            params!(UInt16) => Operation::nullary(|_ecx| catalog_name_only!("var_pop")) => Numeric, oid::FUNC_VAR_POP_UINT16_OID;
2806            params!(UInt32) => Operation::nullary(|_ecx| catalog_name_only!("var_pop")) => Numeric, oid::FUNC_VAR_POP_UINT32_OID;
2807            params!(UInt64) => Operation::nullary(|_ecx| catalog_name_only!("var_pop")) => Numeric, oid::FUNC_VAR_POP_UINT64_OID;
2808        },
2809        "var_samp" => Scalar {
2810            params!(Float32) => Operation::nullary(|_ecx| catalog_name_only!("var_samp")) => Float64, 2644;
2811            params!(Float64) => Operation::nullary(|_ecx| catalog_name_only!("var_samp")) => Float64, 2645;
2812            params!(Int16) => Operation::nullary(|_ecx| catalog_name_only!("var_samp")) => Numeric, 2643;
2813            params!(Int32) => Operation::nullary(|_ecx| catalog_name_only!("var_samp")) => Numeric, 2642;
2814            params!(Int64) => Operation::nullary(|_ecx| catalog_name_only!("var_samp")) => Numeric, 2641;
2815            params!(UInt16) => Operation::nullary(|_ecx| catalog_name_only!("var_samp")) => Numeric, oid::FUNC_VAR_SAMP_UINT16_OID;
2816            params!(UInt32) => Operation::nullary(|_ecx| catalog_name_only!("var_samp")) => Numeric, oid::FUNC_VAR_SAMP_UINT32_OID;
2817            params!(UInt64) => Operation::nullary(|_ecx| catalog_name_only!("var_samp")) => Numeric, oid::FUNC_VAR_SAMP_UINT64_OID;
2818        },
2819        "version" => Scalar {
2820            params!() => UnmaterializableFunc::Version => String, 89;
2821        },
2822
2823        // Internal conversion stubs.
2824        "aclitemin" => Scalar {
2825            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("aclitemin")) => AclItem, 1031;
2826        },
2827        "any_in" => Scalar {
2828            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("any_in")) => Any, 2294;
2829        },
2830        "anyarray_in" => Scalar {
2831            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("anyarray_in")) => ArrayAny, 2296;
2832        },
2833        "anycompatible_in" => Scalar {
2834            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("anycompatible_in")) => AnyCompatible, 5086;
2835        },
2836        "anycompatiblearray_in" => Scalar {
2837            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("anycompatiblearray_in")) => ArrayAnyCompatible, 5088;
2838        },
2839        "anycompatiblenonarray_in" => Scalar {
2840            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("anycompatiblenonarray_in")) => NonVecAnyCompatible, 5092;
2841        },
2842        "anycompatiblerange_in" => Scalar {
2843            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("anycompatiblerange_in")) => RangeAnyCompatible, 5094;
2844        },
2845        "anyelement_in" => Scalar {
2846            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("anyelement_in")) => AnyElement, 2312;
2847        },
2848        "anynonarray_in" => Scalar {
2849            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("anynonarray_in")) => NonVecAny, 2777;
2850        },
2851        "anyrange_in" => Scalar {
2852            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("anyrange_in")) => RangeAny, 3832;
2853        },
2854        "array_in" => Scalar {
2855            params!(String, Oid, Int32) =>
2856                Operation::variadic(|_ecx, _exprs| bail_unsupported!("array_in")) => ArrayAny, 750;
2857        },
2858        "boolin" => Scalar {
2859            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("boolin")) => Bool, 1242;
2860        },
2861        "bpcharin" => Scalar {
2862            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("bpcharin")) => Char, 1044;
2863        },
2864        "byteain" => Scalar {
2865            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("byteain")) => Bytes, 1244;
2866        },
2867        "charin" => Scalar {
2868            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("charin")) => PgLegacyChar, 1245;
2869        },
2870        "date_in" => Scalar {
2871            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("date_in")) => Date, 1084;
2872        },
2873        "float4in" => Scalar {
2874            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("float4in")) => Float32, 200;
2875        },
2876        "float8in" => Scalar {
2877            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("float8in")) => Float64, 214;
2878        },
2879        "int2in" => Scalar {
2880            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("int2in")) => Int16, 38;
2881        },
2882        "int2vectorin" => Scalar {
2883            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("int2vectorin")) => Int2Vector, 40;
2884        },
2885        "int4in" => Scalar {
2886            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("int4in")) => Int32, 42;
2887        },
2888        "int8in" => Scalar {
2889            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("int8in")) => Int64, 460;
2890        },
2891        "internal_in" => Scalar {
2892            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("internal_in")) => Internal, 2304;
2893        },
2894        "interval_in" => Scalar {
2895            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("interval_in")) => Interval, 1160;
2896        },
2897        "jsonb_in" => Scalar {
2898            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("jsonb_in")) => Jsonb, 3806;
2899        },
2900        "namein" => Scalar {
2901            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("namein")) => PgLegacyName, 34;
2902        },
2903        "numeric_in" => Scalar {
2904            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("numeric_in")) => Numeric, 1701;
2905        },
2906        "oidin" => Scalar {
2907            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("oidin")) => Oid, 1798;
2908        },
2909        "range_in" => Scalar {
2910            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("range_in")) => RangeAny, 3834;
2911        },
2912        "record_in" => Scalar {
2913            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("record_in")) => RecordAny, 2290;
2914        },
2915        "regclassin" => Scalar {
2916            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("regclassin")) => RegClass, 2218;
2917        },
2918        "regprocin" => Scalar {
2919            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("regprocin")) => RegProc, 44;
2920        },
2921        "regtypein" => Scalar {
2922            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("regtypein")) => RegType, 2220;
2923        },
2924        "textin" => Scalar {
2925            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("textin")) => String, 46;
2926        },
2927        "time_in" => Scalar {
2928            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("time_in")) => Time, 1143;
2929        },
2930        "timestamp_in" => Scalar {
2931            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("timestamp_in")) => Timestamp, 1312;
2932        },
2933        "timestamptz_in" => Scalar {
2934            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("timestamptz_in")) => TimestampTz, 1150;
2935        },
2936        "varcharin" => Scalar {
2937            params!(String, Oid, Int32) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("varcharin")) => VarChar, 1046;
2938        },
2939        "uuid_in" => Scalar {
2940            params!(String) => Operation::variadic(|_ecx, _exprs| bail_unsupported!("uuid_in")) => Uuid, 2952;
2941        },
2942        "boolrecv" => Scalar {
2943            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("boolrecv")) => Bool, 2436;
2944        },
2945        "textrecv" => Scalar {
2946            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("textrecv")) => String, 2414;
2947        },
2948        "anyarray_recv" => Scalar {
2949            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("anyarray_recv")) => ArrayAny, 2502;
2950        },
2951        "bytearecv" => Scalar {
2952            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("bytearecv")) => Bytes, 2412;
2953        },
2954        "bpcharrecv" => Scalar {
2955            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("bpcharrecv")) => Char, 2430;
2956        },
2957        "charrecv" => Scalar {
2958            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("charrecv")) => PgLegacyChar, 2434;
2959        },
2960        "date_recv" => Scalar {
2961            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("date_recv")) => Date, 2468;
2962        },
2963        "float4recv" => Scalar {
2964            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("float4recv")) => Float32, 2424;
2965        },
2966        "float8recv" => Scalar {
2967            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("float8recv")) => Float64, 2426;
2968        },
2969        "int4recv" => Scalar {
2970            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("int4recv")) => Int32, 2406;
2971        },
2972        "int8recv" => Scalar {
2973            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("int8recv")) => Int64, 2408;
2974        },
2975        "interval_recv" => Scalar {
2976            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("interval_recv")) => Interval, 2478;
2977        },
2978        "jsonb_recv" => Scalar {
2979            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("jsonb_recv")) => Jsonb, 3805;
2980        },
2981        "namerecv" => Scalar {
2982            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("namerecv")) => PgLegacyName, 2422;
2983        },
2984        "numeric_recv" => Scalar {
2985            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("numeric_recv")) => Numeric, 2460;
2986        },
2987        "oidrecv" => Scalar {
2988            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("oidrecv")) => Oid, 2418;
2989        },
2990        "record_recv" => Scalar {
2991            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("recordrerecord_recvcv")) => RecordAny, 2402;
2992        },
2993        "regclassrecv" => Scalar {
2994            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("regclassrecv")) => RegClass, 2452;
2995        },
2996        "regprocrecv" => Scalar {
2997            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("regprocrecv")) => RegProc, 2444;
2998        },
2999        "regtyperecv" => Scalar {
3000            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("regtyperecv")) => RegType, 2454;
3001        },
3002        "int2recv" => Scalar {
3003            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("int2recv")) => Int16, 2404;
3004        },
3005        "time_recv" => Scalar {
3006            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("time_recv")) => Time, 2470;
3007        },
3008        "timestamp_recv" => Scalar {
3009            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("timestamp_recv")) => Timestamp, 2474;
3010        },
3011        "timestamptz_recv" => Scalar {
3012            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("timestamptz_recv")) => TimestampTz, 2476;
3013        },
3014        "uuid_recv" => Scalar {
3015            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("uuid_recv")) => Uuid, 2961;
3016        },
3017        "varcharrecv" => Scalar {
3018            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("varcharrecv")) => VarChar, 2432;
3019        },
3020        "int2vectorrecv" => Scalar {
3021            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("int2vectorrecv")) => Int2Vector, 2410;
3022        },
3023        "anycompatiblearray_recv" => Scalar {
3024            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("anycompatiblearray_recv")) => ArrayAnyCompatible, 5090;
3025        },
3026        "array_recv" => Scalar {
3027            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("array_recv")) => ArrayAny, 2400;
3028        },
3029        "range_recv" => Scalar {
3030            params!(Internal) => Operation::nullary(|_ecx| catalog_name_only!("range_recv")) => RangeAny, 3836;
3031        },
3032
3033
3034        // Aggregates.
3035        "array_agg" => Aggregate {
3036            params!(NonVecAny) => Operation::unary_ordered(|ecx, e, order_by| {
3037                let elem_type = ecx.scalar_type(&e);
3038
3039                let elem_type = match elem_type.array_of_self_elem_type() {
3040                    Ok(elem_type) => elem_type,
3041                    Err(elem_type) => bail_unsupported!(
3042                        format!("array_agg on {}", ecx.humanize_scalar_type(&elem_type, false))
3043                    ),
3044                };
3045
3046                // ArrayConcat excepts all inputs to be arrays, so wrap all input datums into
3047                // arrays.
3048                let e_arr = HirScalarExpr::call_variadic(
3049                    VariadicFunc::ArrayCreate { elem_type },
3050                    vec![e],
3051                );
3052                Ok((e_arr, AggregateFunc::ArrayConcat { order_by }))
3053            }) => ArrayAny, 2335;
3054            params!(ArrayAny) => Operation::unary(|_ecx, _e| bail_unsupported!("array_agg on arrays")) => ArrayAny, 4053;
3055        },
3056        "bool_and" => Aggregate {
3057            params!(Bool) => Operation::nullary(|_ecx| catalog_name_only!("bool_and")) => Bool, 2517;
3058        },
3059        "bool_or" => Aggregate {
3060            params!(Bool) => Operation::nullary(|_ecx| catalog_name_only!("bool_or")) => Bool, 2518;
3061        },
3062        "count" => Aggregate {
3063            params!() => Operation::nullary(|_ecx| {
3064                // COUNT(*) is equivalent to COUNT(true).
3065                // This is mirrored in `AggregateExpr::is_count_asterisk`, so if you modify this,
3066                // then attend to that code also (in both HIR and MIR).
3067                Ok((HirScalarExpr::literal_true(), AggregateFunc::Count))
3068            }) => Int64, 2803;
3069            params!(Any) => AggregateFunc::Count => Int64, 2147;
3070        },
3071        "max" => Aggregate {
3072            params!(Bool) => AggregateFunc::MaxBool => Bool, oid::FUNC_MAX_BOOL_OID;
3073            params!(Int16) => AggregateFunc::MaxInt16 => Int16, 2117;
3074            params!(Int32) => AggregateFunc::MaxInt32 => Int32, 2116;
3075            params!(Int64) => AggregateFunc::MaxInt64 => Int64, 2115;
3076            params!(UInt16) => AggregateFunc::MaxUInt16 => UInt16, oid::FUNC_MAX_UINT16_OID;
3077            params!(UInt32) => AggregateFunc::MaxUInt32 => UInt32, oid::FUNC_MAX_UINT32_OID;
3078            params!(UInt64) => AggregateFunc::MaxUInt64 => UInt64, oid::FUNC_MAX_UINT64_OID;
3079            params!(MzTimestamp) => AggregateFunc::MaxMzTimestamp => MzTimestamp, oid::FUNC_MAX_MZ_TIMESTAMP_OID;
3080            params!(Float32) => AggregateFunc::MaxFloat32 => Float32, 2119;
3081            params!(Float64) => AggregateFunc::MaxFloat64 => Float64, 2120;
3082            params!(String) => AggregateFunc::MaxString => String, 2129;
3083            // TODO(see <materialize#7572>): make this its own function
3084            params!(Char) => AggregateFunc::MaxString => Char, 2244;
3085            params!(Date) => AggregateFunc::MaxDate => Date, 2122;
3086            params!(Timestamp) => AggregateFunc::MaxTimestamp => Timestamp, 2126;
3087            params!(TimestampTz) => AggregateFunc::MaxTimestampTz => TimestampTz, 2127;
3088            params!(Numeric) => AggregateFunc::MaxNumeric => Numeric, oid::FUNC_MAX_NUMERIC_OID;
3089            params!(Interval) => AggregateFunc::MaxInterval => Interval, 2128;
3090            params!(Time) => AggregateFunc::MaxTime => Time, 2123;
3091        },
3092        "min" => Aggregate {
3093            params!(Bool) => AggregateFunc::MinBool => Bool, oid::FUNC_MIN_BOOL_OID;
3094            params!(Int16) => AggregateFunc::MinInt16 => Int16, 2133;
3095            params!(Int32) => AggregateFunc::MinInt32 => Int32, 2132;
3096            params!(Int64) => AggregateFunc::MinInt64 => Int64, 2131;
3097            params!(UInt16) => AggregateFunc::MinUInt16 => UInt16, oid::FUNC_MIN_UINT16_OID;
3098            params!(UInt32) => AggregateFunc::MinUInt32 => UInt32, oid::FUNC_MIN_UINT32_OID;
3099            params!(UInt64) => AggregateFunc::MinUInt64 => UInt64, oid::FUNC_MIN_UINT64_OID;
3100            params!(MzTimestamp) => AggregateFunc::MinMzTimestamp => MzTimestamp, oid::FUNC_MIN_MZ_TIMESTAMP_OID;
3101            params!(Float32) => AggregateFunc::MinFloat32 => Float32, 2135;
3102            params!(Float64) => AggregateFunc::MinFloat64 => Float64, 2136;
3103            params!(String) => AggregateFunc::MinString => String, 2145;
3104            // TODO(see <materialize#7572>): make this its own function
3105            params!(Char) => AggregateFunc::MinString => Char, 2245;
3106            params!(Date) => AggregateFunc::MinDate => Date, 2138;
3107            params!(Timestamp) => AggregateFunc::MinTimestamp => Timestamp, 2142;
3108            params!(TimestampTz) => AggregateFunc::MinTimestampTz => TimestampTz, 2143;
3109            params!(Numeric) => AggregateFunc::MinNumeric => Numeric, oid::FUNC_MIN_NUMERIC_OID;
3110            params!(Interval) => AggregateFunc::MinInterval => Interval, 2144;
3111            params!(Time) => AggregateFunc::MinTime => Time, 2139;
3112        },
3113        "jsonb_agg" => Aggregate {
3114            params!(Any) => Operation::unary_ordered(|ecx, e, order_by| {
3115                // TODO(see <materialize#7572>): remove this
3116                let e = match ecx.scalar_type(&e) {
3117                    SqlScalarType::Char { length } => e.call_unary(UnaryFunc::PadChar(func::PadChar { length })),
3118                    _ => e,
3119                };
3120                // `AggregateFunc::JsonbAgg` filters out `Datum::Null` (it
3121                // needs to have *some* identity input), but the semantics
3122                // of the SQL function require that `Datum::Null` is treated
3123                // as `Datum::JsonbNull`. This call to `coalesce` converts
3124                // between the two semantics.
3125                let json_null = HirScalarExpr::literal(Datum::JsonNull, SqlScalarType::Jsonb);
3126                let e = HirScalarExpr::call_variadic(
3127                    VariadicFunc::Coalesce,
3128                    vec![typeconv::to_jsonb(ecx, e), json_null],
3129                );
3130                Ok((e, AggregateFunc::JsonbAgg { order_by }))
3131            }) => Jsonb, 3267;
3132        },
3133        "jsonb_object_agg" => Aggregate {
3134            params!(Any, Any) => Operation::binary_ordered(|ecx, key, val, order_by| {
3135                // TODO(see <materialize#7572>): remove this
3136                let key = match ecx.scalar_type(&key) {
3137                    SqlScalarType::Char { length } => key.call_unary(UnaryFunc::PadChar(func::PadChar { length })),
3138                    _ => key,
3139                };
3140                let val = match ecx.scalar_type(&val) {
3141                    SqlScalarType::Char { length } => val.call_unary(UnaryFunc::PadChar(func::PadChar { length })),
3142                    _ => val,
3143                };
3144
3145                let json_null = HirScalarExpr::literal(Datum::JsonNull, SqlScalarType::Jsonb);
3146                let key = typeconv::to_string(ecx, key);
3147                // `AggregateFunc::JsonbObjectAgg` uses the same underlying
3148                // implementation as `AggregateFunc::MapAgg`, so it's our
3149                // responsibility to apply the JSON-specific behavior of casting
3150                // SQL nulls to JSON nulls; otherwise the produced `Datum::Map`
3151                // can contain `Datum::Null` values that are not valid for the
3152                // `SqlScalarType::Jsonb` type.
3153                let val = HirScalarExpr::call_variadic(
3154                    VariadicFunc::Coalesce,
3155                    vec![typeconv::to_jsonb(ecx, val), json_null],
3156                );
3157                let e = HirScalarExpr::call_variadic(
3158                    VariadicFunc::RecordCreate {
3159                        field_names: vec![ColumnName::from("key"), ColumnName::from("val")],
3160                    },
3161                    vec![key, val],
3162                );
3163                Ok((e, AggregateFunc::JsonbObjectAgg { order_by }))
3164            }) => Jsonb, 3270;
3165        },
3166        "string_agg" => Aggregate {
3167            params!(String, String) => Operation::binary_ordered(|_ecx, value, sep, order_by| {
3168                let e = HirScalarExpr::call_variadic(
3169                    VariadicFunc::RecordCreate {
3170                        field_names: vec![ColumnName::from("value"), ColumnName::from("sep")],
3171                    },
3172                    vec![value, sep],
3173                );
3174                Ok((e, AggregateFunc::StringAgg { order_by }))
3175            }) => String, 3538;
3176            params!(Bytes, Bytes) => Operation::binary(|_ecx, _l, _r| bail_unsupported!("string_agg on BYTEA")) => Bytes, 3545;
3177        },
3178        "string_to_array" => Scalar {
3179            params!(String, String) => VariadicFunc::StringToArray => SqlScalarType::Array(Box::new(SqlScalarType::String)), 376;
3180            params!(String, String, String) => VariadicFunc::StringToArray => SqlScalarType::Array(Box::new(SqlScalarType::String)), 394;
3181        },
3182        "sum" => Aggregate {
3183            params!(Int16) => AggregateFunc::SumInt16 => Int64, 2109;
3184            params!(Int32) => AggregateFunc::SumInt32 => Int64, 2108;
3185            params!(Int64) => AggregateFunc::SumInt64 => Numeric, 2107;
3186            params!(UInt16) => AggregateFunc::SumUInt16 => UInt64, oid::FUNC_SUM_UINT16_OID;
3187            params!(UInt32) => AggregateFunc::SumUInt32 => UInt64, oid::FUNC_SUM_UINT32_OID;
3188            params!(UInt64) => AggregateFunc::SumUInt64 => Numeric, oid::FUNC_SUM_UINT64_OID;
3189            params!(Float32) => AggregateFunc::SumFloat32 => Float32, 2110;
3190            params!(Float64) => AggregateFunc::SumFloat64 => Float64, 2111;
3191            params!(Numeric) => AggregateFunc::SumNumeric => Numeric, 2114;
3192            params!(Interval) => Operation::unary(|_ecx, _e| {
3193                // Explicitly providing this unsupported overload
3194                // prevents `sum(NULL)` from choosing the `Float64`
3195                // implementation, so that we match PostgreSQL's behavior.
3196                // Plus we will one day want to support this overload.
3197                bail_unsupported!("sum(interval)");
3198            }) => Interval, 2113;
3199        },
3200
3201        // Scalar window functions.
3202        "row_number" => ScalarWindow {
3203            params!() => ScalarWindowFunc::RowNumber => Int64, 3100;
3204        },
3205        "rank" => ScalarWindow {
3206            params!() => ScalarWindowFunc::Rank => Int64, 3101;
3207        },
3208        "dense_rank" => ScalarWindow {
3209            params!() => ScalarWindowFunc::DenseRank => Int64, 3102;
3210        },
3211        "lag" => ValueWindow {
3212            // All args are encoded into a single record to be handled later
3213            params!(AnyElement) => Operation::unary(|ecx, e| {
3214                let typ = ecx.scalar_type(&e);
3215                let e = HirScalarExpr::call_variadic(
3216                    VariadicFunc::RecordCreate {
3217                        field_names: vec![ColumnName::from("expr"), ColumnName::from("offset"), ColumnName::from("default")],
3218                    },
3219                    vec![e, HirScalarExpr::literal(Datum::Int32(1), SqlScalarType::Int32), HirScalarExpr::literal_null(typ)],
3220                );
3221                Ok((e, ValueWindowFunc::Lag))
3222            }) => AnyElement, 3106;
3223            params!(AnyElement, Int32) => Operation::binary(|ecx, e, offset| {
3224                let typ = ecx.scalar_type(&e);
3225                let e = HirScalarExpr::call_variadic(
3226                    VariadicFunc::RecordCreate {
3227                        field_names: vec![ColumnName::from("expr"), ColumnName::from("offset"), ColumnName::from("default")],
3228                    },
3229                    vec![e, offset, HirScalarExpr::literal_null(typ)],
3230                );
3231                Ok((e, ValueWindowFunc::Lag))
3232            }) => AnyElement, 3107;
3233            params!(AnyCompatible, Int32, AnyCompatible) => Operation::variadic(|_ecx, exprs| {
3234                let e = HirScalarExpr::call_variadic(
3235                    VariadicFunc::RecordCreate {
3236                        field_names: vec![ColumnName::from("expr"), ColumnName::from("offset"), ColumnName::from("default")],
3237                    },
3238                    exprs,
3239                );
3240                Ok((e, ValueWindowFunc::Lag))
3241            }) => AnyCompatible, 3108;
3242        },
3243        "lead" => ValueWindow {
3244            // All args are encoded into a single record to be handled later
3245            params!(AnyElement) => Operation::unary(|ecx, e| {
3246                let typ = ecx.scalar_type(&e);
3247                let e = HirScalarExpr::call_variadic(
3248                    VariadicFunc::RecordCreate {
3249                        field_names: vec![ColumnName::from("expr"), ColumnName::from("offset"), ColumnName::from("default")],
3250                    },
3251                    vec![e, HirScalarExpr::literal(Datum::Int32(1), SqlScalarType::Int32), HirScalarExpr::literal_null(typ)],
3252                );
3253                Ok((e, ValueWindowFunc::Lead))
3254            }) => AnyElement, 3109;
3255            params!(AnyElement, Int32) => Operation::binary(|ecx, e, offset| {
3256                let typ = ecx.scalar_type(&e);
3257                let e = HirScalarExpr::call_variadic(
3258                    VariadicFunc::RecordCreate {
3259                        field_names: vec![ColumnName::from("expr"), ColumnName::from("offset"), ColumnName::from("default")],
3260                    },
3261                    vec![e, offset, HirScalarExpr::literal_null(typ)],
3262                );
3263                Ok((e, ValueWindowFunc::Lead))
3264            }) => AnyElement, 3110;
3265            params!(AnyCompatible, Int32, AnyCompatible) => Operation::variadic(|_ecx, exprs| {
3266                let e = HirScalarExpr::call_variadic(
3267                    VariadicFunc::RecordCreate {
3268                        field_names: vec![ColumnName::from("expr"), ColumnName::from("offset"), ColumnName::from("default")],
3269                    },
3270                    exprs,
3271                );
3272                Ok((e, ValueWindowFunc::Lead))
3273            }) => AnyCompatible, 3111;
3274        },
3275        "first_value" => ValueWindow {
3276            params!(AnyElement) => ValueWindowFunc::FirstValue => AnyElement, 3112;
3277        },
3278        "last_value" => ValueWindow {
3279            params!(AnyElement) => ValueWindowFunc::LastValue => AnyElement, 3113;
3280        },
3281
3282        // Table functions.
3283        "generate_series" => Table {
3284            params!(Int32, Int32, Int32) => Operation::variadic(move |_ecx, exprs| {
3285                Ok(TableFuncPlan {
3286                    imp: TableFuncImpl::CallTable {
3287                        func: TableFunc::GenerateSeriesInt32,
3288                        exprs,
3289                    },
3290                    column_names: vec!["generate_series".into()],
3291                })
3292            }) => ReturnType::set_of(Int32.into()), 1066;
3293            params!(Int32, Int32) => Operation::binary(move |_ecx, start, stop| {
3294                Ok(TableFuncPlan {
3295                    imp: TableFuncImpl::CallTable {
3296                        func: TableFunc::GenerateSeriesInt32,
3297                        exprs: vec![start, stop, HirScalarExpr::literal(Datum::Int32(1), SqlScalarType::Int32)],
3298                    },
3299                    column_names: vec!["generate_series".into()],
3300                })
3301            }) => ReturnType::set_of(Int32.into()), 1067;
3302            params!(Int64, Int64, Int64) => Operation::variadic(move |_ecx, exprs| {
3303                Ok(TableFuncPlan {
3304                    imp: TableFuncImpl::CallTable {
3305                        func: TableFunc::GenerateSeriesInt64,
3306                        exprs,
3307                    },
3308                    column_names: vec!["generate_series".into()],
3309                })
3310            }) => ReturnType::set_of(Int64.into()), 1068;
3311            params!(Int64, Int64) => Operation::binary(move |_ecx, start, stop| {
3312                Ok(TableFuncPlan {
3313                    imp: TableFuncImpl::CallTable {
3314                        func: TableFunc::GenerateSeriesInt64,
3315                        exprs: vec![start, stop, HirScalarExpr::literal(Datum::Int64(1), SqlScalarType::Int64)],
3316                    },
3317                    column_names: vec!["generate_series".into()],
3318                })
3319            }) => ReturnType::set_of(Int64.into()), 1069;
3320            params!(Timestamp, Timestamp, Interval) => Operation::variadic(move |_ecx, exprs| {
3321                Ok(TableFuncPlan {
3322                    imp: TableFuncImpl::CallTable {
3323                        func: TableFunc::GenerateSeriesTimestamp,
3324                        exprs,
3325                    },
3326                    column_names: vec!["generate_series".into()],
3327                })
3328            }) => ReturnType::set_of(Timestamp.into()), 938;
3329            params!(TimestampTz, TimestampTz, Interval) => Operation::variadic(move |_ecx, exprs| {
3330                Ok(TableFuncPlan {
3331                    imp: TableFuncImpl::CallTable {
3332                        func: TableFunc::GenerateSeriesTimestampTz,
3333                        exprs,
3334                    },
3335                    column_names: vec!["generate_series".into()],
3336                })
3337            }) => ReturnType::set_of(TimestampTz.into()), 939;
3338        },
3339
3340        "generate_subscripts" => Table {
3341            params!(ArrayAny, Int32) => Operation::variadic(move |_ecx, exprs| {
3342                Ok(TableFuncPlan {
3343                    imp: TableFuncImpl::CallTable {
3344                        func: TableFunc::GenerateSubscriptsArray,
3345                        exprs,
3346                    },
3347                    column_names: vec!["generate_subscripts".into()],
3348                })
3349            }) => ReturnType::set_of(Int32.into()), 1192;
3350        },
3351
3352        "jsonb_array_elements" => Table {
3353            params!(Jsonb) => Operation::unary(move |_ecx, jsonb| {
3354                Ok(TableFuncPlan {
3355                    imp: TableFuncImpl::CallTable {
3356                        func: TableFunc::JsonbArrayElements { stringify: false },
3357                        exprs: vec![jsonb],
3358                    },
3359                    column_names: vec!["value".into()],
3360                })
3361            }) => ReturnType::set_of(Jsonb.into()), 3219;
3362        },
3363        "jsonb_array_elements_text" => Table {
3364            params!(Jsonb) => Operation::unary(move |_ecx, jsonb| {
3365                Ok(TableFuncPlan {
3366                    imp: TableFuncImpl::CallTable {
3367                        func: TableFunc::JsonbArrayElements { stringify: true },
3368                        exprs: vec![jsonb],
3369                    },
3370                    column_names: vec!["value".into()],
3371                })
3372            }) => ReturnType::set_of(String.into()), 3465;
3373        },
3374        "jsonb_each" => Table {
3375            params!(Jsonb) => Operation::unary(move |_ecx, jsonb| {
3376                Ok(TableFuncPlan {
3377                    imp: TableFuncImpl::CallTable {
3378                        func: TableFunc::JsonbEach { stringify: false },
3379                        exprs: vec![jsonb],
3380                    },
3381                    column_names: vec!["key".into(), "value".into()],
3382                })
3383            }) => ReturnType::set_of(RecordAny), 3208;
3384        },
3385        "jsonb_each_text" => Table {
3386            params!(Jsonb) => Operation::unary(move |_ecx, jsonb| {
3387                Ok(TableFuncPlan {
3388                    imp: TableFuncImpl::CallTable {
3389                        func: TableFunc::JsonbEach { stringify: true },
3390                        exprs: vec![jsonb],
3391                    },
3392                    column_names: vec!["key".into(), "value".into()],
3393                })
3394            }) => ReturnType::set_of(RecordAny), 3932;
3395        },
3396        "jsonb_object_keys" => Table {
3397            params!(Jsonb) => Operation::unary(move |_ecx, jsonb| {
3398                Ok(TableFuncPlan {
3399                    imp: TableFuncImpl::CallTable {
3400                        func: TableFunc::JsonbObjectKeys,
3401                        exprs: vec![jsonb],
3402                    },
3403                    column_names: vec!["jsonb_object_keys".into()],
3404                })
3405            }) => ReturnType::set_of(String.into()), 3931;
3406        },
3407        // Note that these implementations' input to `generate_series` is
3408        // contrived to match Flink's expected values. There are other,
3409        // equally valid windows we could generate.
3410        "date_bin_hopping" => Table {
3411            // (hop, width, timestamp)
3412            params!(Interval, Interval, Timestamp) => experimental_sql_impl_table_func(&vars::ENABLE_DATE_BIN_HOPPING, "
3413                    SELECT *
3414                    FROM pg_catalog.generate_series(
3415                        pg_catalog.date_bin($1, $3 + $1, '1970-01-01') - $2, $3, $1
3416                    ) AS dbh(date_bin_hopping)
3417                ") => ReturnType::set_of(Timestamp.into()), oid::FUNC_MZ_DATE_BIN_HOPPING_UNIX_EPOCH_TS_OID;
3418            // (hop, width, timestamp)
3419            params!(Interval, Interval, TimestampTz) => experimental_sql_impl_table_func(&vars::ENABLE_DATE_BIN_HOPPING, "
3420                    SELECT *
3421                    FROM pg_catalog.generate_series(
3422                        pg_catalog.date_bin($1, $3 + $1, '1970-01-01') - $2, $3, $1
3423                    ) AS dbh(date_bin_hopping)
3424                ") => ReturnType::set_of(TimestampTz.into()), oid::FUNC_MZ_DATE_BIN_HOPPING_UNIX_EPOCH_TSTZ_OID;
3425            // (hop, width, timestamp, origin)
3426            params!(Interval, Interval, Timestamp, Timestamp) => experimental_sql_impl_table_func(&vars::ENABLE_DATE_BIN_HOPPING, "
3427                    SELECT *
3428                    FROM pg_catalog.generate_series(
3429                        pg_catalog.date_bin($1, $3 + $1, $4) - $2, $3, $1
3430                    ) AS dbh(date_bin_hopping)
3431                ") => ReturnType::set_of(Timestamp.into()), oid::FUNC_MZ_DATE_BIN_HOPPING_TS_OID;
3432            // (hop, width, timestamp, origin)
3433            params!(Interval, Interval, TimestampTz, TimestampTz) => experimental_sql_impl_table_func(&vars::ENABLE_DATE_BIN_HOPPING, "
3434                    SELECT *
3435                    FROM pg_catalog.generate_series(
3436                        pg_catalog.date_bin($1, $3 + $1, $4) - $2, $3, $1
3437                    ) AS dbh(date_bin_hopping)
3438                ") => ReturnType::set_of(TimestampTz.into()), oid::FUNC_MZ_DATE_BIN_HOPPING_TSTZ_OID;
3439        },
3440        "encode" => Scalar {
3441            params!(Bytes, String) => BinaryFunc::from(func::Encode) => String, 1946;
3442        },
3443        "decode" => Scalar {
3444            params!(String, String) => BinaryFunc::from(func::Decode) => Bytes, 1947;
3445        },
3446        "regexp_split_to_array" => Scalar {
3447            params!(String, String) => VariadicFunc::RegexpSplitToArray => SqlScalarType::Array(Box::new(SqlScalarType::String)), 2767;
3448            params!(String, String, String) => VariadicFunc::RegexpSplitToArray => SqlScalarType::Array(Box::new(SqlScalarType::String)), 2768;
3449        },
3450        "regexp_split_to_table" => Table {
3451            params!(String, String) => sql_impl_table_func("
3452                SELECT unnest(regexp_split_to_array($1, $2))
3453            ") => ReturnType::set_of(String.into()), 2765;
3454            params!(String, String, String) => sql_impl_table_func("
3455                SELECT unnest(regexp_split_to_array($1, $2, $3))
3456            ") => ReturnType::set_of(String.into()), 2766;
3457        },
3458        "regexp_replace" => Scalar {
3459            params!(String, String, String) => VariadicFunc::RegexpReplace => String, 2284;
3460            params!(String, String, String, String) => VariadicFunc::RegexpReplace => String, 2285;
3461            // TODO: PostgreSQL supports additional five and six argument forms of this function which
3462            // allow controlling where to start the replacement and how many replacements to make.
3463        },
3464        "regexp_matches" => Table {
3465            params!(String, String) => Operation::variadic(move |_ecx, exprs| {
3466                let column_names = vec!["regexp_matches".into()];
3467                Ok(TableFuncPlan {
3468                    imp: TableFuncImpl::CallTable {
3469                        func: TableFunc::RegexpMatches,
3470                        exprs: vec![exprs[0].clone(), exprs[1].clone()],
3471                    },
3472                    column_names,
3473                })
3474            }) => ReturnType::set_of(SqlScalarType::Array(Box::new(SqlScalarType::String)).into()), 2763;
3475            params!(String, String, String) => Operation::variadic(move |_ecx, exprs| {
3476                let column_names = vec!["regexp_matches".into()];
3477                Ok(TableFuncPlan {
3478                    imp: TableFuncImpl::CallTable {
3479                        func: TableFunc::RegexpMatches,
3480                        exprs: vec![exprs[0].clone(), exprs[1].clone(), exprs[2].clone()],
3481                    },
3482                    column_names,
3483                })
3484            }) => ReturnType::set_of(SqlScalarType::Array(Box::new(SqlScalarType::String)).into()), 2764;
3485        },
3486        "reverse" => Scalar {
3487            params!(String) => UnaryFunc::Reverse(func::Reverse) => String, 3062;
3488        }
3489    };
3490
3491    // Add side-effecting functions, which are defined in a separate module
3492    // using a restricted set of function definition features (e.g., no
3493    // overloads) to make them easier to plan.
3494    for sef_builtin in PG_CATALOG_SEF_BUILTINS.values() {
3495        builtins.insert(
3496            sef_builtin.name,
3497            Func::Scalar(vec![FuncImpl {
3498                oid: sef_builtin.oid,
3499                params: ParamList::Exact(
3500                    sef_builtin
3501                        .param_types
3502                        .iter()
3503                        .map(|t| ParamType::from(t.clone()))
3504                        .collect(),
3505                ),
3506                return_type: ReturnType::scalar(ParamType::from(
3507                    sef_builtin.return_type.scalar_type.clone(),
3508                )),
3509                op: Operation::variadic(|_ecx, _e| {
3510                    bail_unsupported!(format!("{} in this position", sef_builtin.name))
3511                }),
3512            }]),
3513        );
3514    }
3515
3516    builtins
3517});
3518
3519pub static INFORMATION_SCHEMA_BUILTINS: LazyLock<BTreeMap<&'static str, Func>> =
3520    LazyLock::new(|| {
3521        use ParamType::*;
3522        builtins! {
3523            "_pg_expandarray" => Table {
3524                // See: https://github.com/postgres/postgres/blob/16e3ad5d143795b05a21dc887c2ab384cce4bcb8/src/backend/catalog/information_schema.sql#L43
3525                params!(ArrayAny) => sql_impl_table_func("
3526                    SELECT
3527                        $1[s] AS x,
3528                        s - pg_catalog.array_lower($1, 1) + 1 AS n
3529                    FROM pg_catalog.generate_series(
3530                        pg_catalog.array_lower($1, 1),
3531                        pg_catalog.array_upper($1, 1),
3532                        1) as g(s)
3533                ") => ReturnType::set_of(RecordAny), oid::FUNC_PG_EXPAND_ARRAY;
3534            }
3535        }
3536    });
3537
3538pub static MZ_CATALOG_BUILTINS: LazyLock<BTreeMap<&'static str, Func>> = LazyLock::new(|| {
3539    use ParamType::*;
3540    use ScalarBaseType::*;
3541    builtins! {
3542        "constant_time_eq" => Scalar {
3543            params!(Bytes, Bytes) => BinaryFunc::from(func::ConstantTimeEqBytes) => Bool, oid::FUNC_CONSTANT_TIME_EQ_BYTES_OID;
3544            params!(String, String) => BinaryFunc::from(func::ConstantTimeEqString) => Bool, oid::FUNC_CONSTANT_TIME_EQ_STRING_OID;
3545        },
3546        // Note: this is the original version of the AVG(...) function, as it existed prior to
3547        // v0.66. We updated the internal type promotion used when summing values to increase
3548        // precision, but objects (e.g. materialized views) that already used the AVG(...) function
3549        // could not be changed. So we migrated all existing uses of the AVG(...) function to this
3550        // version.
3551        //
3552        // TODO(parkmycar): When objects no longer depend on this function we can safely delete it.
3553        "avg_internal_v1" => Scalar {
3554            params!(Int64) => Operation::nullary(|_ecx| catalog_name_only!("avg_internal_v1")) => Numeric, oid::FUNC_AVG_INTERNAL_V1_INT64_OID;
3555            params!(Int32) => Operation::nullary(|_ecx| catalog_name_only!("avg_internal_v1")) => Numeric, oid::FUNC_AVG_INTERNAL_V1_INT32_OID;
3556            params!(Int16) => Operation::nullary(|_ecx| catalog_name_only!("avg_internal_v1")) => Numeric, oid::FUNC_AVG_INTERNAL_V1_INT16_OID;
3557            params!(UInt64) => Operation::nullary(|_ecx| catalog_name_only!("avg_internal_v1")) => Numeric, oid::FUNC_AVG_INTERNAL_V1_UINT64_OID;
3558            params!(UInt32) => Operation::nullary(|_ecx| catalog_name_only!("avg_internal_v1")) => Numeric, oid::FUNC_AVG_INTERNAL_V1_UINT32_OID;
3559            params!(UInt16) => Operation::nullary(|_ecx| catalog_name_only!("avg_internal_v1")) => Numeric, oid::FUNC_AVG_INTERNAL_V1_UINT16_OID;
3560            params!(Float32) => Operation::nullary(|_ecx| catalog_name_only!("avg_internal_v1")) => Float64, oid::FUNC_AVG_INTERNAL_V1_FLOAT32_OID;
3561            params!(Float64) => Operation::nullary(|_ecx| catalog_name_only!("avg_internal_v1")) => Float64, oid::FUNC_AVG_INTERNAL_V1_FLOAT64_OID;
3562            params!(Interval) => Operation::nullary(|_ecx| catalog_name_only!("avg_internal_v1")) => Interval, oid::FUNC_AVG_INTERNAL_V1_INTERVAL_OID;
3563        },
3564        "csv_extract" => Table {
3565            params!(Int64, String) => Operation::binary(move |_ecx, ncols, input| {
3566                const MAX_EXTRACT_COLUMNS: i64 = 8192;
3567                const TOO_MANY_EXTRACT_COLUMNS: i64 = MAX_EXTRACT_COLUMNS + 1;
3568
3569                let ncols = match ncols.into_literal_int64() {
3570                    None | Some(i64::MIN..=0) => {
3571                        sql_bail!("csv_extract number of columns must be a positive integer literal");
3572                    },
3573                    Some(ncols @ 1..=MAX_EXTRACT_COLUMNS) => ncols,
3574                    Some(ncols @ TOO_MANY_EXTRACT_COLUMNS..) => {
3575                        return Err(PlanError::TooManyColumns {
3576                            max_num_columns: usize::try_from(MAX_EXTRACT_COLUMNS).unwrap_or(usize::MAX),
3577                            req_num_columns: usize::try_from(ncols).unwrap_or(usize::MAX),
3578                        });
3579                    },
3580                };
3581                let ncols = usize::try_from(ncols).expect("known to be greater than zero");
3582
3583                let column_names = (1..=ncols).map(|i| format!("column{}", i).into()).collect();
3584                Ok(TableFuncPlan {
3585                    imp: TableFuncImpl::CallTable {
3586                        func: TableFunc::CsvExtract(ncols),
3587                        exprs: vec![input],
3588                    },
3589                    column_names,
3590                })
3591            }) => ReturnType::set_of(RecordAny), oid::FUNC_CSV_EXTRACT_OID;
3592        },
3593        "concat_agg" => Aggregate {
3594            params!(Any) => Operation::unary(|_ecx, _e| bail_unsupported!("concat_agg")) => String, oid::FUNC_CONCAT_AGG_OID;
3595        },
3596        "crc32" => Scalar {
3597            params!(String) => UnaryFunc::Crc32String(func::Crc32String) => UInt32, oid::FUNC_CRC32_STRING_OID;
3598            params!(Bytes) => UnaryFunc::Crc32Bytes(func::Crc32Bytes) => UInt32, oid::FUNC_CRC32_BYTES_OID;
3599        },
3600        "datediff" => Scalar {
3601            params!(String, Timestamp, Timestamp) => VariadicFunc::DateDiffTimestamp => Int64, oid::FUNC_DATEDIFF_TIMESTAMP;
3602            params!(String, TimestampTz, TimestampTz) => VariadicFunc::DateDiffTimestampTz => Int64, oid::FUNC_DATEDIFF_TIMESTAMPTZ;
3603            params!(String, Date, Date) => VariadicFunc::DateDiffDate => Int64, oid::FUNC_DATEDIFF_DATE;
3604            params!(String, Time, Time) => VariadicFunc::DateDiffTime => Int64, oid::FUNC_DATEDIFF_TIME;
3605        },
3606        // We can't use the `privilege_fn!` macro because the macro relies on the object having an
3607        // OID, and clusters do not have OIDs.
3608        "has_cluster_privilege" => Scalar {
3609            params!(String, String, String) => sql_impl_func("has_cluster_privilege(mz_internal.mz_role_oid($1), $2, $3)") => Bool, oid::FUNC_HAS_CLUSTER_PRIVILEGE_TEXT_TEXT_TEXT_OID;
3610            params!(Oid, String, String) => sql_impl_func(&format!("
3611                CASE
3612                -- We must first check $2 to avoid a potentially null error message (an error itself).
3613                WHEN $2 IS NULL
3614                THEN NULL
3615                -- Validate the cluster name in order to return a proper error.
3616                WHEN NOT EXISTS (SELECT name FROM mz_clusters WHERE name = $2)
3617                THEN mz_unsafe.mz_error_if_null(NULL::boolean, 'error cluster \"' || $2 || '\" does not exist')
3618                -- Validate the privileges and other arguments.
3619                WHEN NOT mz_internal.mz_validate_privileges($3)
3620                OR $1 IS NULL
3621                OR $3 IS NULL
3622                OR $1 NOT IN (SELECT oid FROM mz_catalog.mz_roles)
3623                THEN NULL
3624                ELSE COALESCE(
3625                    (
3626                        SELECT
3627                            bool_or(
3628                                mz_internal.mz_acl_item_contains_privilege(privilege, $3)
3629                            )
3630                                AS has_cluster_privilege
3631                        FROM
3632                            (
3633                                SELECT
3634                                    unnest(privileges)
3635                                FROM
3636                                    mz_clusters
3637                                WHERE
3638                                    mz_clusters.name = $2
3639                            )
3640                                AS user_privs (privilege)
3641                            LEFT JOIN mz_catalog.mz_roles ON
3642                                    mz_internal.mz_aclitem_grantee(privilege) = mz_roles.id
3643                        WHERE
3644                            mz_internal.mz_aclitem_grantee(privilege) = '{}' OR pg_has_role($1, mz_roles.oid, 'USAGE')
3645                    ),
3646                    false
3647                )
3648                END
3649            ", RoleId::Public)) => Bool, oid::FUNC_HAS_CLUSTER_PRIVILEGE_OID_TEXT_TEXT_OID;
3650            params!(String, String) => sql_impl_func("has_cluster_privilege(current_user, $1, $2)") => Bool, oid::FUNC_HAS_CLUSTER_PRIVILEGE_TEXT_TEXT_OID;
3651        },
3652        "has_connection_privilege" => Scalar {
3653            params!(String, String, String) => sql_impl_func("has_connection_privilege(mz_internal.mz_role_oid($1), mz_internal.mz_connection_oid($2), $3)") => Bool, oid::FUNC_HAS_CONNECTION_PRIVILEGE_TEXT_TEXT_TEXT_OID;
3654            params!(String, Oid, String) => sql_impl_func("has_connection_privilege(mz_internal.mz_role_oid($1), $2, $3)") => Bool, oid::FUNC_HAS_CONNECTION_PRIVILEGE_TEXT_OID_TEXT_OID;
3655            params!(Oid, String, String) => sql_impl_func("has_connection_privilege($1, mz_internal.mz_connection_oid($2), $3)") => Bool, oid::FUNC_HAS_CONNECTION_PRIVILEGE_OID_TEXT_TEXT_OID;
3656            params!(Oid, Oid, String) => sql_impl_func(&privilege_fn!("has_connection_privilege", "mz_connections")) => Bool, oid::FUNC_HAS_CONNECTION_PRIVILEGE_OID_OID_TEXT_OID;
3657            params!(String, String) => sql_impl_func("has_connection_privilege(current_user, $1, $2)") => Bool, oid::FUNC_HAS_CONNECTION_PRIVILEGE_TEXT_TEXT_OID;
3658            params!(Oid, String) => sql_impl_func("has_connection_privilege(current_user, $1, $2)") => Bool, oid::FUNC_HAS_CONNECTION_PRIVILEGE_OID_TEXT_OID;
3659        },
3660        "has_role" => Scalar {
3661            params!(String, String, String) => sql_impl_func("pg_has_role($1, $2, $3)") => Bool, oid::FUNC_HAS_ROLE_TEXT_TEXT_TEXT_OID;
3662            params!(String, Oid, String) => sql_impl_func("pg_has_role($1, $2, $3)") => Bool, oid::FUNC_HAS_ROLE_TEXT_OID_TEXT_OID;
3663            params!(Oid, String, String) => sql_impl_func("pg_has_role($1, $2, $3)") => Bool, oid::FUNC_HAS_ROLE_OID_TEXT_TEXT_OID;
3664            params!(Oid, Oid, String) => sql_impl_func("pg_has_role($1, $2, $3)") => Bool, oid::FUNC_HAS_ROLE_OID_OID_TEXT_OID;
3665            params!(String, String) => sql_impl_func("pg_has_role($1, $2)") => Bool, oid::FUNC_HAS_ROLE_TEXT_TEXT_OID;
3666            params!(Oid, String) => sql_impl_func("pg_has_role($1, $2)") => Bool, oid::FUNC_HAS_ROLE_OID_TEXT_OID;
3667        },
3668        "has_secret_privilege" => Scalar {
3669            params!(String, String, String) => sql_impl_func("has_secret_privilege(mz_internal.mz_role_oid($1), mz_internal.mz_secret_oid($2), $3)") => Bool, oid::FUNC_HAS_SECRET_PRIVILEGE_TEXT_TEXT_TEXT_OID;
3670            params!(String, Oid, String) => sql_impl_func("has_secret_privilege(mz_internal.mz_role_oid($1), $2, $3)") => Bool, oid::FUNC_HAS_SECRET_PRIVILEGE_TEXT_OID_TEXT_OID;
3671            params!(Oid, String, String) => sql_impl_func("has_secret_privilege($1, mz_internal.mz_secret_oid($2), $3)") => Bool, oid::FUNC_HAS_SECRET_PRIVILEGE_OID_TEXT_TEXT_OID;
3672            params!(Oid, Oid, String) => sql_impl_func(&privilege_fn!("has_secret_privilege", "mz_secrets")) => Bool, oid::FUNC_HAS_SECRET_PRIVILEGE_OID_OID_TEXT_OID;
3673            params!(String, String) => sql_impl_func("has_secret_privilege(current_user, $1, $2)") => Bool, oid::FUNC_HAS_SECRET_PRIVILEGE_TEXT_TEXT_OID;
3674            params!(Oid, String) => sql_impl_func("has_secret_privilege(current_user, $1, $2)") => Bool, oid::FUNC_HAS_SECRET_PRIVILEGE_OID_TEXT_OID;
3675        },
3676        "has_system_privilege" => Scalar {
3677            params!(String, String) => sql_impl_func("has_system_privilege(mz_internal.mz_role_oid($1), $2)") => Bool, oid::FUNC_HAS_SYSTEM_PRIVILEGE_TEXT_TEXT_OID;
3678            params!(Oid, String) => sql_impl_func(&format!("
3679                CASE
3680                -- We need to validate the privileges to return a proper error before
3681                -- anything else.
3682                WHEN NOT mz_internal.mz_validate_privileges($2)
3683                OR $1 IS NULL
3684                OR $2 IS NULL
3685                OR $1 NOT IN (SELECT oid FROM mz_catalog.mz_roles)
3686                THEN NULL
3687                ELSE COALESCE(
3688                    (
3689                        SELECT
3690                            bool_or(
3691                                mz_internal.mz_acl_item_contains_privilege(privileges, $2)
3692                            )
3693                                AS has_system_privilege
3694                        FROM mz_catalog.mz_system_privileges
3695                        LEFT JOIN mz_catalog.mz_roles ON
3696                                mz_internal.mz_aclitem_grantee(privileges) = mz_roles.id
3697                        WHERE
3698                            mz_internal.mz_aclitem_grantee(privileges) = '{}' OR pg_has_role($1, mz_roles.oid, 'USAGE')
3699                    ),
3700                    false
3701                )
3702                END
3703            ", RoleId::Public)) => Bool, oid::FUNC_HAS_SYSTEM_PRIVILEGE_OID_TEXT_OID;
3704            params!(String) => sql_impl_func("has_system_privilege(current_user, $1)") => Bool, oid::FUNC_HAS_SYSTEM_PRIVILEGE_TEXT_OID;
3705        },
3706        "has_type_privilege" => Scalar {
3707            params!(String, String, String) => sql_impl_func("has_type_privilege(mz_internal.mz_role_oid($1), $2::regtype::oid, $3)") => Bool, 3138;
3708            params!(String, Oid, String) => sql_impl_func("has_type_privilege(mz_internal.mz_role_oid($1), $2, $3)") => Bool, 3139;
3709            params!(Oid, String, String) => sql_impl_func("has_type_privilege($1, $2::regtype::oid, $3)") => Bool, 3140;
3710            params!(Oid, Oid, String) => sql_impl_func(&privilege_fn!("has_type_privilege", "mz_types")) => Bool, 3141;
3711            params!(String, String) => sql_impl_func("has_type_privilege(current_user, $1, $2)") => Bool, 3142;
3712            params!(Oid, String) => sql_impl_func("has_type_privilege(current_user, $1, $2)") => Bool, 3143;
3713        },
3714        "kafka_murmur2" => Scalar {
3715            params!(String) => UnaryFunc::KafkaMurmur2String(func::KafkaMurmur2String) => Int32, oid::FUNC_KAFKA_MURMUR2_STRING_OID;
3716            params!(Bytes) => UnaryFunc::KafkaMurmur2Bytes(func::KafkaMurmur2Bytes) => Int32, oid::FUNC_KAFKA_MURMUR2_BYTES_OID;
3717        },
3718        "list_agg" => Aggregate {
3719            params!(Any) => Operation::unary_ordered(|ecx, e, order_by| {
3720                if let SqlScalarType::Char {.. }  = ecx.scalar_type(&e) {
3721                    bail_unsupported!("list_agg on char");
3722                };
3723                // ListConcat excepts all inputs to be lists, so wrap all input datums into
3724                // lists.
3725                let e_arr = HirScalarExpr::call_variadic(
3726                    VariadicFunc::ListCreate { elem_type: ecx.scalar_type(&e) },
3727                    vec![e],
3728                );
3729                Ok((e_arr, AggregateFunc::ListConcat { order_by }))
3730            }) => ListAnyCompatible,  oid::FUNC_LIST_AGG_OID;
3731        },
3732        "list_append" => Scalar {
3733            vec![ListAnyCompatible, ListElementAnyCompatible] => BinaryFunc::from(func::ListElementConcat) => ListAnyCompatible, oid::FUNC_LIST_APPEND_OID;
3734        },
3735        "list_cat" => Scalar {
3736            vec![ListAnyCompatible, ListAnyCompatible] => BinaryFunc::from(func::ListListConcat) => ListAnyCompatible, oid::FUNC_LIST_CAT_OID;
3737        },
3738        "list_n_layers" => Scalar {
3739            vec![ListAny] => Operation::unary(|ecx, e| {
3740                ecx.require_feature_flag(&crate::session::vars::ENABLE_LIST_N_LAYERS)?;
3741                let d = ecx.scalar_type(&e).unwrap_list_n_layers();
3742                match i32::try_from(d) {
3743                    Ok(d) => Ok(HirScalarExpr::literal(Datum::Int32(d), SqlScalarType::Int32)),
3744                    Err(_) => sql_bail!("list has more than {} layers", i32::MAX),
3745                }
3746
3747            }) => Int32, oid::FUNC_LIST_N_LAYERS_OID;
3748        },
3749        "list_length" => Scalar {
3750            vec![ListAny] => UnaryFunc::ListLength(func::ListLength) => Int32, oid::FUNC_LIST_LENGTH_OID;
3751        },
3752        "list_length_max" => Scalar {
3753            vec![ListAny, Plain(SqlScalarType::Int64)] => Operation::binary(|ecx, lhs, rhs| {
3754                ecx.require_feature_flag(&crate::session::vars::ENABLE_LIST_LENGTH_MAX)?;
3755                let max_layer = ecx.scalar_type(&lhs).unwrap_list_n_layers();
3756                Ok(lhs.call_binary(rhs, BinaryFunc::ListLengthMax { max_layer }))
3757            }) => Int32, oid::FUNC_LIST_LENGTH_MAX_OID;
3758        },
3759        "list_prepend" => Scalar {
3760            vec![ListElementAnyCompatible, ListAnyCompatible] => BinaryFunc::from(func::ElementListConcat) => ListAnyCompatible, oid::FUNC_LIST_PREPEND_OID;
3761        },
3762        "list_remove" => Scalar {
3763            vec![ListAnyCompatible, ListElementAnyCompatible] => Operation::binary(|ecx, lhs, rhs| {
3764                ecx.require_feature_flag(&crate::session::vars::ENABLE_LIST_REMOVE)?;
3765                Ok(lhs.call_binary(rhs, func::ListRemove))
3766            }) => ListAnyCompatible, oid::FUNC_LIST_REMOVE_OID;
3767        },
3768        "map_agg" => Aggregate {
3769            params!(String, Any) => Operation::binary_ordered(|ecx, key, val, order_by| {
3770                let (value_type, val) = match ecx.scalar_type(&val) {
3771                    // TODO(see <materialize#7572>): remove this
3772                    SqlScalarType::Char { length } => (SqlScalarType::Char { length }, val.call_unary(UnaryFunc::PadChar(func::PadChar { length }))),
3773                    typ => (typ, val),
3774                };
3775
3776                let e = HirScalarExpr::call_variadic(
3777                    VariadicFunc::RecordCreate {
3778                        field_names: vec![ColumnName::from("key"), ColumnName::from("val")],
3779                    },
3780                    vec![key, val],
3781                );
3782
3783                Ok((e, AggregateFunc::MapAgg { order_by, value_type }))
3784            }) => MapAny, oid::FUNC_MAP_AGG;
3785        },
3786        "map_build" => Scalar {
3787            // TODO: support a function to construct maps that looks like...
3788            //
3789            // params!([String], Any...) => Operation::variadic(|ecx, exprs| {
3790            //
3791            // ...the challenge here is that we don't support constructing other
3792            // complex types from varidaic functions and instead use a SQL
3793            // keyword; however that doesn't work very well for map because the
3794            // intuitive syntax would be something akin to `MAP[key=>value]`,
3795            // but that doesn't work out of the box because `key=>value` looks
3796            // like an expression.
3797            params!(ListAny) => Operation::unary(|ecx, expr| {
3798                let ty = ecx.scalar_type(&expr);
3799
3800                // This is a fake error but should suffice given how exotic the
3801                // function is.
3802                let err = || {
3803                    Err(sql_err!(
3804                        "function map_build({}) does not exist",
3805                        ecx.humanize_scalar_type(&ty.clone(), false)
3806                    ))
3807                };
3808
3809                // This function only accepts lists of records whose schema is
3810                // (text, T).
3811                let value_type = match &ty {
3812                    SqlScalarType::List { element_type, .. } => match &**element_type {
3813                        SqlScalarType::Record { fields, .. } if fields.len() == 2 => {
3814                            if fields[0].1.scalar_type != SqlScalarType::String {
3815                                return err();
3816                            }
3817
3818                            fields[1].1.scalar_type.clone()
3819                        }
3820                        _ => return err(),
3821                    },
3822                    _ => unreachable!("input guaranteed to be list"),
3823                };
3824
3825                Ok(expr.call_unary(UnaryFunc::MapBuildFromRecordList(
3826                    func::MapBuildFromRecordList { value_type },
3827                )))
3828            }) => MapAny, oid::FUNC_MAP_BUILD;
3829        },
3830        "map_length" => Scalar {
3831            params![MapAny] => UnaryFunc::MapLength(func::MapLength) => Int32, oid::FUNC_MAP_LENGTH_OID;
3832        },
3833        "mz_environment_id" => Scalar {
3834            params!() => UnmaterializableFunc::MzEnvironmentId => String, oid::FUNC_MZ_ENVIRONMENT_ID_OID;
3835        },
3836        "mz_is_superuser" => Scalar {
3837            params!() => UnmaterializableFunc::MzIsSuperuser => SqlScalarType::Bool, oid::FUNC_MZ_IS_SUPERUSER;
3838        },
3839        "mz_logical_timestamp" => Scalar {
3840            params!() => Operation::nullary(|_ecx| sql_bail!("mz_logical_timestamp() has been renamed to mz_now()")) => MzTimestamp, oid::FUNC_MZ_LOGICAL_TIMESTAMP_OID;
3841        },
3842        "mz_now" => Scalar {
3843            params!() => UnmaterializableFunc::MzNow => MzTimestamp, oid::FUNC_MZ_NOW_OID;
3844        },
3845        "mz_uptime" => Scalar {
3846            params!() => UnmaterializableFunc::MzUptime => Interval, oid::FUNC_MZ_UPTIME_OID;
3847        },
3848        "mz_version" => Scalar {
3849            params!() => UnmaterializableFunc::MzVersion => String, oid::FUNC_MZ_VERSION_OID;
3850        },
3851        "mz_version_num" => Scalar {
3852            params!() => UnmaterializableFunc::MzVersionNum => Int32, oid::FUNC_MZ_VERSION_NUM_OID;
3853        },
3854        "pretty_sql" => Scalar {
3855            params!(String, Int32) => BinaryFunc::from(func::PrettySql) => String, oid::FUNC_PRETTY_SQL;
3856            params!(String) => Operation::unary(|_ecx, s| {
3857                let width = HirScalarExpr::literal(Datum::Int32(mz_sql_pretty::DEFAULT_WIDTH.try_into().expect("must fit")), SqlScalarType::Int32);
3858                Ok(s.call_binary(width, func::PrettySql))
3859            }) => String, oid::FUNC_PRETTY_SQL_NOWIDTH;
3860        },
3861        "regexp_extract" => Table {
3862            params!(String, String) => Operation::binary(move |_ecx, regex, haystack| {
3863                let regex = match regex.into_literal_string() {
3864                    None => sql_bail!("regexp_extract requires a string literal as its first argument"),
3865                    Some(regex) => mz_expr::AnalyzedRegex::new(&regex, mz_expr::AnalyzedRegexOpts::default()).map_err(|e| sql_err!("analyzing regex: {}", e))?,
3866                };
3867                let column_names = regex
3868                    .capture_groups_iter()
3869                    .map(|cg| {
3870                        cg.name.clone().unwrap_or_else(|| format!("column{}", cg.index)).into()
3871                    })
3872                    .collect::<Vec<_>>();
3873                if column_names.is_empty(){
3874                    sql_bail!("regexp_extract must specify at least one capture group");
3875                }
3876                Ok(TableFuncPlan {
3877                    imp: TableFuncImpl::CallTable {
3878                        func: TableFunc::RegexpExtract(regex),
3879                        exprs: vec![haystack],
3880                    },
3881                    column_names,
3882                })
3883            }) => ReturnType::set_of(RecordAny), oid::FUNC_REGEXP_EXTRACT_OID;
3884        },
3885        "repeat_row" => Table {
3886            params!(Int64) => Operation::unary(move |ecx, n| {
3887                ecx.require_feature_flag(&crate::session::vars::ENABLE_REPEAT_ROW)?;
3888                Ok(TableFuncPlan {
3889                    imp: TableFuncImpl::CallTable {
3890                        func: TableFunc::Repeat,
3891                        exprs: vec![n],
3892                    },
3893                    column_names: vec![]
3894                })
3895            }) => ReturnType::none(true), oid::FUNC_REPEAT_OID;
3896        },
3897        "seahash" => Scalar {
3898            params!(String) => UnaryFunc::SeahashString(func::SeahashString) => UInt32, oid::FUNC_SEAHASH_STRING_OID;
3899            params!(Bytes) => UnaryFunc::SeahashBytes(func::SeahashBytes) => UInt32, oid::FUNC_SEAHASH_BYTES_OID;
3900        },
3901        "starts_with" => Scalar {
3902            params!(String, String) => BinaryFunc::from(func::StartsWith) => Bool, 3696;
3903        },
3904        "timezone_offset" => Scalar {
3905            params!(String, TimestampTz) => BinaryFunc::from(func::TimezoneOffset) => RecordAny, oid::FUNC_TIMEZONE_OFFSET;
3906        },
3907        "try_parse_monotonic_iso8601_timestamp" => Scalar {
3908            params!(String) => Operation::unary(move |_ecx, e| {
3909                Ok(e.call_unary(UnaryFunc::TryParseMonotonicIso8601Timestamp(func::TryParseMonotonicIso8601Timestamp)))
3910            }) => Timestamp, oid::FUNC_TRY_PARSE_MONOTONIC_ISO8601_TIMESTAMP;
3911        },
3912        "unnest" => Table {
3913            vec![ArrayAny] => Operation::unary(move |ecx, e| {
3914                let el_typ = ecx.scalar_type(&e).unwrap_array_element_type().clone();
3915                Ok(TableFuncPlan {
3916                    imp: TableFuncImpl::CallTable {
3917                        func: TableFunc::UnnestArray { el_typ },
3918                        exprs: vec![e],
3919                    },
3920                    column_names: vec!["unnest".into()],
3921                })
3922            }) =>
3923                // This return type should be equivalent to "ArrayElementAny", but this would be its sole use.
3924                ReturnType::set_of(AnyElement), 2331;
3925            vec![ListAny] => Operation::unary(move |ecx, e| {
3926                let el_typ = ecx.scalar_type(&e).unwrap_list_element_type().clone();
3927                Ok(TableFuncPlan {
3928                    imp: TableFuncImpl::CallTable {
3929                        func: TableFunc::UnnestList { el_typ },
3930                        exprs: vec![e],
3931                    },
3932                    column_names: vec!["unnest".into()],
3933                })
3934            }) =>
3935                // This return type should be equivalent to "ListElementAny", but this would be its sole use.
3936                ReturnType::set_of(Any), oid::FUNC_UNNEST_LIST_OID;
3937            vec![MapAny] => Operation::unary(move |ecx, e| {
3938                let value_type = ecx.scalar_type(&e).unwrap_map_value_type().clone();
3939                Ok(TableFuncPlan {
3940                    imp: TableFuncImpl::CallTable {
3941                        func: TableFunc::UnnestMap { value_type },
3942                        exprs: vec![e],
3943                    },
3944                    column_names: vec!["key".into(), "value".into()],
3945                })
3946            }) =>
3947                // This return type should be equivalent to "ListElementAny", but this would be its sole use.
3948                ReturnType::set_of(Any), oid::FUNC_UNNEST_MAP_OID;
3949        }
3950    }
3951});
3952
3953pub static MZ_INTERNAL_BUILTINS: LazyLock<BTreeMap<&'static str, Func>> = LazyLock::new(|| {
3954    use ParamType::*;
3955    use ScalarBaseType::*;
3956    builtins! {
3957        "aclitem_grantor" => Scalar {
3958            params!(AclItem) => UnaryFunc::AclItemGrantor(func::AclItemGrantor) => Oid, oid::FUNC_ACL_ITEM_GRANTOR_OID;
3959        },
3960        "aclitem_grantee" => Scalar {
3961            params!(AclItem) => UnaryFunc::AclItemGrantee(func::AclItemGrantee) => Oid, oid::FUNC_ACL_ITEM_GRANTEE_OID;
3962        },
3963        "aclitem_privileges" => Scalar {
3964            params!(AclItem) => UnaryFunc::AclItemPrivileges(func::AclItemPrivileges) => String, oid::FUNC_ACL_ITEM_PRIVILEGES_OID;
3965        },
3966        "is_rbac_enabled" => Scalar {
3967            params!() => UnmaterializableFunc::IsRbacEnabled => Bool, oid::FUNC_IS_RBAC_ENABLED_OID;
3968        },
3969        "make_mz_aclitem" => Scalar {
3970            params!(String, String, String) => VariadicFunc::MakeMzAclItem => MzAclItem, oid::FUNC_MAKE_MZ_ACL_ITEM_OID;
3971        },
3972        "mz_acl_item_contains_privilege" => Scalar {
3973            params!(MzAclItem, String) => BinaryFunc::from(func::MzAclItemContainsPrivilege) => Bool, oid::FUNC_MZ_ACL_ITEM_CONTAINS_PRIVILEGE_OID;
3974        },
3975        "mz_aclexplode" => Table {
3976            params!(SqlScalarType::Array(Box::new(SqlScalarType::MzAclItem))) =>  Operation::unary(move |_ecx, mz_aclitems| {
3977                Ok(TableFuncPlan {
3978                    imp: TableFuncImpl::CallTable {
3979                        func: TableFunc::MzAclExplode,
3980                        exprs: vec![mz_aclitems],
3981                    },
3982                    column_names: vec!["grantor".into(), "grantee".into(), "privilege_type".into(), "is_grantable".into()],
3983                })
3984            }) => ReturnType::set_of(RecordAny), oid::FUNC_MZ_ACL_ITEM_EXPLODE_OID;
3985        },
3986        "mz_aclitem_grantor" => Scalar {
3987            params!(MzAclItem) => UnaryFunc::MzAclItemGrantor(func::MzAclItemGrantor) => String, oid::FUNC_MZ_ACL_ITEM_GRANTOR_OID;
3988        },
3989        "mz_aclitem_grantee" => Scalar {
3990            params!(MzAclItem) => UnaryFunc::MzAclItemGrantee(func::MzAclItemGrantee) => String, oid::FUNC_MZ_ACL_ITEM_GRANTEE_OID;
3991        },
3992        "mz_aclitem_privileges" => Scalar {
3993            params!(MzAclItem) => UnaryFunc::MzAclItemPrivileges(func::MzAclItemPrivileges) => String, oid::FUNC_MZ_ACL_ITEM_PRIVILEGES_OID;
3994        },
3995        // There is no regclass equivalent for roles to look up connections, so we
3996        // have this helper function instead.
3997        //
3998        // TODO: invent an OID alias for connections
3999        "mz_connection_oid" => Scalar {
4000            params!(String) => sql_impl_func("
4001                CASE
4002                WHEN $1 IS NULL THEN NULL
4003                ELSE (
4004                    mz_unsafe.mz_error_if_null(
4005                        (SELECT oid FROM mz_catalog.mz_objects WHERE name = $1 AND type = 'connection'),
4006                        'connection \"' || $1 || '\" does not exist'
4007                    )
4008                )
4009                END
4010            ") => Oid, oid::FUNC_CONNECTION_OID_OID;
4011        },
4012        "mz_format_privileges" => Scalar {
4013            params!(String) => UnaryFunc::MzFormatPrivileges(func::MzFormatPrivileges) => SqlScalarType::Array(Box::new(SqlScalarType::String)), oid::FUNC_MZ_FORMAT_PRIVILEGES_OID;
4014        },
4015        "mz_name_rank" => Table {
4016            // Determines the id, rank of all objects that can be matched using
4017            // the provided args.
4018            params!(
4019                // Database
4020                String,
4021                // Schemas/search path
4022                ParamType::Plain(SqlScalarType::Array(Box::new(SqlScalarType::String))),
4023                // Item name
4024                String,
4025                // Get rank among particular OID alias (e.g. regclass)
4026                String
4027            ) =>
4028            // credit for using rank() to @def-
4029            sql_impl_table_func("
4030            -- The best ranked name is the one that belongs to the schema correlated with the lowest
4031            -- index in the search path
4032            SELECT id, name, count, min(schema_pref) OVER () = schema_pref AS best_ranked FROM (
4033                SELECT DISTINCT
4034                    o.id,
4035                    ARRAY[CASE WHEN s.database_id IS NULL THEN NULL ELSE d.name END, s.name, o.name]
4036                    AS name,
4037                    o.count,
4038                    pg_catalog.array_position($2, s.name) AS schema_pref
4039                FROM
4040                    (
4041                        SELECT
4042                            o.id,
4043                            o.schema_id,
4044                            o.name,
4045                            count(*)
4046                        FROM mz_catalog.mz_objects AS o
4047                        JOIN mz_internal.mz_object_oid_alias AS a
4048                            ON o.type = a.object_type
4049                        WHERE o.name = CAST($3 AS pg_catalog.text) AND a.oid_alias = $4
4050                        GROUP BY 1, 2, 3
4051                    )
4052                        AS o
4053                    JOIN mz_catalog.mz_schemas AS s ON o.schema_id = s.id
4054                    JOIN
4055                        unnest($2) AS search_schema (name)
4056                        ON search_schema.name = s.name
4057                    JOIN
4058                        (
4059                            SELECT id, name FROM mz_catalog.mz_databases
4060                            -- If the provided database does not exist, add a row for it so that it
4061                            -- can still join against ambient schemas.
4062                            UNION ALL
4063                            SELECT '', $1 WHERE $1 NOT IN (SELECT name FROM mz_catalog.mz_databases)
4064                        ) AS d
4065                        ON d.id = COALESCE(s.database_id, d.id)
4066                WHERE d.name = CAST($1 AS pg_catalog.text)
4067            );
4068            ") => ReturnType::set_of(RecordAny), oid::FUNC_MZ_NAME_RANK;
4069        },
4070        "mz_resolve_object_name" => Table {
4071            params!(String, String) =>
4072            // Normalize the input name, and for any NULL values (e.g. not database qualified), use
4073            // the defaults used during name resolution.
4074            sql_impl_table_func("
4075                SELECT
4076                    o.id, o.oid, o.schema_id, o.name, o.type, o.owner_id, o.privileges
4077                FROM
4078                    (SELECT mz_internal.mz_normalize_object_name($2))
4079                            AS normalized (n),
4080                    mz_internal.mz_name_rank(
4081                        COALESCE(n[1], pg_catalog.current_database()),
4082                        CASE
4083                            WHEN n[2] IS NULL
4084                                THEN pg_catalog.current_schemas(true)
4085                            ELSE
4086                                ARRAY[n[2]]
4087                        END,
4088                        n[3],
4089                        $1
4090                    ) AS r,
4091                    mz_catalog.mz_objects AS o
4092                WHERE r.id = o.id AND r.best_ranked;
4093            ") => ReturnType::set_of(RecordAny), oid::FUNC_MZ_RESOLVE_OBJECT_NAME;
4094        },
4095        // Returns the an array representing the minimal namespace a user must
4096        // provide to refer to an item whose name is the first argument.
4097        //
4098        // The first argument must be a fully qualified name (i.e. contain
4099        // database.schema.object), with each level of the namespace being an
4100        // element.
4101        //
4102        // The second argument represents the `GlobalId` of the resolved object.
4103        // This is a safeguard to ensure that the name we are resolving refers
4104        // to the expected entry. For example, this helps us disambiguate cases
4105        // where e.g. types and functions have the same name.
4106        "mz_minimal_name_qualification" => Scalar {
4107            params!(SqlScalarType::Array(Box::new(SqlScalarType::String)), String) => {
4108                sql_impl_func("(
4109                    SELECT
4110                    CASE
4111                        WHEN $1::pg_catalog.text[] IS NULL
4112                            THEN NULL
4113                    -- If DB doesn't match, requires full qual
4114                        WHEN $1[1] != pg_catalog.current_database()
4115                            THEN $1
4116                    -- If not in currently searchable schema, must be schema qualified
4117                        WHEN NOT $1[2] = ANY(pg_catalog.current_schemas(true))
4118                            THEN ARRAY[$1[2], $1[3]]
4119                    ELSE
4120                        minimal_name
4121                    END
4122                FROM (
4123                    -- Subquery so we return one null row in the cases where
4124                    -- there are no matches.
4125                    SELECT (
4126                        SELECT DISTINCT
4127                            CASE
4128                                -- If there is only one item with this name and it's rank 1,
4129                                -- it is uniquely nameable with just the final element
4130                                WHEN best_ranked AND count = 1
4131                                    THEN ARRAY[r.name[3]]
4132                                -- Otherwise, it is findable in the search path, so does not
4133                                -- need database qualification
4134                                ELSE
4135                                    ARRAY[r.name[2], r.name[3]]
4136                            END AS minimal_name
4137                        FROM mz_catalog.mz_objects AS o
4138                            JOIN mz_internal.mz_object_oid_alias AS a
4139                                ON o.type = a.object_type,
4140                            -- implied lateral to put the OID alias into scope
4141                            mz_internal.mz_name_rank(
4142                                pg_catalog.current_database(),
4143                                pg_catalog.current_schemas(true),
4144                                $1[3],
4145                                a.oid_alias
4146                            ) AS r
4147                        WHERE o.id = $2 AND r.id = $2
4148                    )
4149                )
4150            )")
4151            } => SqlScalarType::Array(Box::new(SqlScalarType::String)), oid::FUNC_MZ_MINIMINAL_NAME_QUALIFICATION;
4152        },
4153        "mz_global_id_to_name" => Scalar {
4154            params!(String) => sql_impl_func("
4155            CASE
4156                WHEN $1 IS NULL THEN NULL
4157                ELSE (
4158                    SELECT array_to_string(minimal_name, '.')
4159                    FROM (
4160                        SELECT mz_unsafe.mz_error_if_null(
4161                            (
4162                                -- Return the fully-qualified name
4163                                SELECT DISTINCT ARRAY[qual.d, qual.s, item.name]
4164                                FROM
4165                                    mz_catalog.mz_objects AS item
4166                                JOIN
4167                                (
4168                                    SELECT
4169                                        d.name AS d,
4170                                        s.name AS s,
4171                                        s.id AS schema_id
4172                                    FROM
4173                                        mz_catalog.mz_schemas AS s
4174                                        LEFT JOIN
4175                                            (SELECT id, name FROM mz_catalog.mz_databases)
4176                                            AS d
4177                                            ON s.database_id = d.id
4178                                ) AS qual
4179                                ON qual.schema_id = item.schema_id
4180                                WHERE item.id = CAST($1 AS text)
4181                            ),
4182                            'global ID ' || $1 || ' does not exist'
4183                        )
4184                    ) AS n (fqn),
4185                    LATERAL (
4186                        -- Get the minimal qualification of the fully qualified name
4187                        SELECT mz_internal.mz_minimal_name_qualification(fqn, $1)
4188                    ) AS m (minimal_name)
4189                )
4190                END
4191            ") => String, oid::FUNC_MZ_GLOBAL_ID_TO_NAME;
4192        },
4193        "mz_normalize_object_name" => Scalar {
4194            params!(String) => sql_impl_func("
4195            (
4196                SELECT
4197                    CASE
4198                        WHEN $1 IS NULL THEN NULL
4199                        WHEN pg_catalog.array_length(ident, 1) > 3
4200                            THEN mz_unsafe.mz_error_if_null(
4201                                NULL::pg_catalog.text[],
4202                                'improper relation name (too many dotted names): ' || $1
4203                            )
4204                        ELSE pg_catalog.array_cat(
4205                            pg_catalog.array_fill(
4206                                CAST(NULL AS pg_catalog.text),
4207                                ARRAY[3 - pg_catalog.array_length(ident, 1)]
4208                            ),
4209                            ident
4210                        )
4211                    END
4212                FROM (
4213                    SELECT pg_catalog.parse_ident($1) AS ident
4214                ) AS i
4215            )") => SqlScalarType::Array(Box::new(SqlScalarType::String)), oid::FUNC_MZ_NORMALIZE_OBJECT_NAME;
4216        },
4217        "mz_normalize_schema_name" => Scalar {
4218            params!(String) => sql_impl_func("
4219             (
4220                SELECT
4221                    CASE
4222                        WHEN $1 IS NULL THEN NULL
4223                        WHEN pg_catalog.array_length(ident, 1) > 2
4224                            THEN mz_unsafe.mz_error_if_null(
4225                                NULL::pg_catalog.text[],
4226                                'improper schema name (too many dotted names): ' || $1
4227                            )
4228                        ELSE pg_catalog.array_cat(
4229                            pg_catalog.array_fill(
4230                                CAST(NULL AS pg_catalog.text),
4231                                ARRAY[2 - pg_catalog.array_length(ident, 1)]
4232                            ),
4233                            ident
4234                        )
4235                    END
4236                FROM (
4237                    SELECT pg_catalog.parse_ident($1) AS ident
4238                ) AS i
4239            )") => SqlScalarType::Array(Box::new(SqlScalarType::String)), oid::FUNC_MZ_NORMALIZE_SCHEMA_NAME;
4240        },
4241        "mz_render_typmod" => Scalar {
4242            params!(Oid, Int32) => BinaryFunc::from(func::MzRenderTypmod) => String, oid::FUNC_MZ_RENDER_TYPMOD_OID;
4243        },
4244        "mz_role_oid_memberships" => Scalar {
4245            params!() => UnmaterializableFunc::MzRoleOidMemberships => SqlScalarType::Map{ value_type: Box::new(SqlScalarType::Array(Box::new(SqlScalarType::String))), custom_id: None }, oid::FUNC_MZ_ROLE_OID_MEMBERSHIPS;
4246        },
4247        // There is no regclass equivalent for databases to look up oids, so we have this helper function instead.
4248        "mz_database_oid" => Scalar {
4249            params!(String) => sql_impl_func("
4250                CASE
4251                WHEN $1 IS NULL THEN NULL
4252                ELSE (
4253                    mz_unsafe.mz_error_if_null(
4254                        (SELECT oid FROM mz_databases WHERE name = $1),
4255                        'database \"' || $1 || '\" does not exist'
4256                    )
4257                )
4258                END
4259            ") => Oid, oid::FUNC_DATABASE_OID_OID;
4260        },
4261        // There is no regclass equivalent for schemas to look up oids, so we have this helper function instead.
4262        "mz_schema_oid" => Scalar {
4263            params!(String) => sql_impl_func("
4264            CASE
4265                WHEN $1 IS NULL THEN NULL
4266            ELSE
4267                mz_unsafe.mz_error_if_null(
4268                    (
4269                        SELECT
4270                            (
4271                                SELECT s.oid
4272                                FROM mz_catalog.mz_schemas AS s
4273                                LEFT JOIN mz_databases AS d ON s.database_id = d.id
4274                                WHERE
4275                                    (
4276                                        -- Filter to only schemas in the named database or the
4277                                        -- current database if no database was specified.
4278                                        d.name = COALESCE(n[1], pg_catalog.current_database())
4279                                        -- Always include all ambient schemas.
4280                                        OR s.database_id IS NULL
4281                                    )
4282                                    AND s.name = n[2]
4283                            )
4284                        FROM mz_internal.mz_normalize_schema_name($1) AS n
4285                    ),
4286                    'schema \"' || $1 || '\" does not exist'
4287                )
4288            END
4289            ") => Oid, oid::FUNC_SCHEMA_OID_OID;
4290        },
4291        // There is no regclass equivalent for roles to look up oids, so we have this helper function instead.
4292        "mz_role_oid" => Scalar {
4293            params!(String) => sql_impl_func("
4294                CASE
4295                WHEN $1 IS NULL THEN NULL
4296                ELSE (
4297                    mz_unsafe.mz_error_if_null(
4298                        (SELECT oid FROM mz_catalog.mz_roles WHERE name = $1),
4299                        'role \"' || $1 || '\" does not exist'
4300                    )
4301                )
4302                END
4303            ") => Oid, oid::FUNC_ROLE_OID_OID;
4304        },
4305        // There is no regclass equivalent for roles to look up secrets, so we
4306        // have this helper function instead.
4307        //
4308        // TODO: invent an OID alias for secrets
4309        "mz_secret_oid" => Scalar {
4310            params!(String) => sql_impl_func("
4311                CASE
4312                WHEN $1 IS NULL THEN NULL
4313                ELSE (
4314                    mz_unsafe.mz_error_if_null(
4315                        (SELECT oid FROM mz_catalog.mz_objects WHERE name = $1 AND type = 'secret'),
4316                        'secret \"' || $1 || '\" does not exist'
4317                    )
4318                )
4319                END
4320            ") => Oid, oid::FUNC_SECRET_OID_OID;
4321        },
4322        // This ought to be exposed in `mz_catalog`, but its name is rather
4323        // confusing. It does not identify the SQL session, but the
4324        // invocation of this `environmentd` process.
4325        "mz_session_id" => Scalar {
4326            params!() => UnmaterializableFunc::MzSessionId => Uuid, oid::FUNC_MZ_SESSION_ID_OID;
4327        },
4328        "mz_type_name" => Scalar {
4329            params!(Oid) => UnaryFunc::MzTypeName(func::MzTypeName) => String, oid::FUNC_MZ_TYPE_NAME;
4330        },
4331        "mz_validate_privileges" => Scalar {
4332            params!(String) => UnaryFunc::MzValidatePrivileges(func::MzValidatePrivileges) => Bool, oid::FUNC_MZ_VALIDATE_PRIVILEGES_OID;
4333        },
4334        "mz_validate_role_privilege" => Scalar {
4335            params!(String) => UnaryFunc::MzValidateRolePrivilege(func::MzValidateRolePrivilege) => Bool, oid::FUNC_MZ_VALIDATE_ROLE_PRIVILEGE_OID;
4336        }
4337    }
4338});
4339
4340pub static MZ_UNSAFE_BUILTINS: LazyLock<BTreeMap<&'static str, Func>> = LazyLock::new(|| {
4341    use ParamType::*;
4342    use ScalarBaseType::*;
4343    builtins! {
4344        "mz_all" => Aggregate {
4345            params!(Any) => AggregateFunc::All => Bool, oid::FUNC_MZ_ALL_OID;
4346        },
4347        "mz_any" => Aggregate {
4348            params!(Any) => AggregateFunc::Any => Bool, oid::FUNC_MZ_ANY_OID;
4349        },
4350        "mz_avg_promotion_internal_v1" => Scalar {
4351            // Promotes a numeric type to the smallest fractional type that
4352            // can represent it. This is primarily useful for the avg
4353            // aggregate function, so that the avg of an integer column does
4354            // not get truncated to an integer, which would be surprising to
4355            // users (#549).
4356            params!(Float32) => Operation::identity() => Float32, oid::FUNC_MZ_AVG_PROMOTION_F32_OID_INTERNAL_V1;
4357            params!(Float64) => Operation::identity() => Float64, oid::FUNC_MZ_AVG_PROMOTION_F64_OID_INTERNAL_V1;
4358            params!(Int16) => Operation::unary(|ecx, e| {
4359                typeconv::plan_cast(
4360                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4361                )
4362            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_I16_OID_INTERNAL_V1;
4363            params!(Int32) => Operation::unary(|ecx, e| {
4364                typeconv::plan_cast(
4365                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4366                )
4367            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_I32_OID_INTERNAL_V1;
4368            params!(UInt16) => Operation::unary(|ecx, e| {
4369                typeconv::plan_cast(
4370                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4371                )
4372            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_U16_OID_INTERNAL_V1;
4373            params!(UInt32) => Operation::unary(|ecx, e| {
4374                typeconv::plan_cast(
4375                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4376                )
4377            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_U32_OID_INTERNAL_V1;
4378        },
4379        "mz_avg_promotion" => Scalar {
4380            // Promotes a numeric type to the smallest fractional type that
4381            // can represent it. This is primarily useful for the avg
4382            // aggregate function, so that the avg of an integer column does
4383            // not get truncated to an integer, which would be surprising to
4384            // users (#549).
4385            params!(Float32) => Operation::identity() => Float32, oid::FUNC_MZ_AVG_PROMOTION_F32_OID;
4386            params!(Float64) => Operation::identity() => Float64, oid::FUNC_MZ_AVG_PROMOTION_F64_OID;
4387            params!(Int16) => Operation::unary(|ecx, e| {
4388                typeconv::plan_cast(
4389                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4390                )
4391            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_I16_OID;
4392            params!(Int32) => Operation::unary(|ecx, e| {
4393                typeconv::plan_cast(
4394                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4395                )
4396            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_I32_OID;
4397            params!(Int64) => Operation::unary(|ecx, e| {
4398                typeconv::plan_cast(
4399                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4400                )
4401            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_I64_OID;
4402            params!(UInt16) => Operation::unary(|ecx, e| {
4403                typeconv::plan_cast(
4404                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4405                )
4406            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_U16_OID;
4407            params!(UInt32) => Operation::unary(|ecx, e| {
4408                typeconv::plan_cast(
4409                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4410                )
4411            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_U32_OID;
4412            params!(UInt64) => Operation::unary(|ecx, e| {
4413                typeconv::plan_cast(
4414                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4415                )
4416            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_U64_OID;
4417            params!(Numeric) => Operation::unary(|ecx, e| {
4418                typeconv::plan_cast(
4419                    ecx, CastContext::Explicit, e, &SqlScalarType::Numeric {max_scale: None},
4420                )
4421            }) => Numeric, oid::FUNC_MZ_AVG_PROMOTION_NUMERIC_OID;
4422        },
4423        "mz_error_if_null" => Scalar {
4424            // If the first argument is NULL, returns an EvalError::Internal whose error
4425            // message is the second argument.
4426            params!(Any, String) => VariadicFunc::ErrorIfNull => Any, oid::FUNC_MZ_ERROR_IF_NULL_OID;
4427        },
4428        "mz_sleep" => Scalar {
4429            params!(Float64) => UnaryFunc::Sleep(func::Sleep) => TimestampTz, oid::FUNC_MZ_SLEEP_OID;
4430        },
4431        "mz_panic" => Scalar {
4432            params!(String) => UnaryFunc::Panic(func::Panic) => String, oid::FUNC_MZ_PANIC_OID;
4433        }
4434    }
4435});
4436
4437fn digest(algorithm: &'static str) -> Operation<HirScalarExpr> {
4438    Operation::unary(move |_ecx, input| {
4439        let algorithm = HirScalarExpr::literal(Datum::String(algorithm), SqlScalarType::String);
4440        Ok(input.call_binary(algorithm, BinaryFunc::from(func::DigestBytes)))
4441    })
4442}
4443
4444fn array_to_string(
4445    ecx: &ExprContext,
4446    exprs: Vec<HirScalarExpr>,
4447) -> Result<HirScalarExpr, PlanError> {
4448    let elem_type = match ecx.scalar_type(&exprs[0]) {
4449        SqlScalarType::Array(elem_type) => *elem_type,
4450        _ => unreachable!("array_to_string is guaranteed to receive array as first argument"),
4451    };
4452    Ok(HirScalarExpr::call_variadic(
4453        VariadicFunc::ArrayToString { elem_type },
4454        exprs,
4455    ))
4456}
4457
4458/// Correlates an operator with all of its implementations.
4459pub static OP_IMPLS: LazyLock<BTreeMap<&'static str, Func>> = LazyLock::new(|| {
4460    use BinaryFunc as BF;
4461    use ParamType::*;
4462    use ScalarBaseType::*;
4463    builtins! {
4464        // Literal OIDs collected from PG 13 using a version of this query
4465        // ```sql
4466        // SELECT
4467        //     oid,
4468        //     oprname,
4469        //     oprleft::regtype,
4470        //     oprright::regtype
4471        // FROM
4472        //     pg_operator
4473        // WHERE
4474        //     oprname IN (
4475        //         '+', '-', '*', '/', '%',
4476        //         '|', '&', '#', '~', '<<', '>>',
4477        //         '~~', '!~~'
4478        //     )
4479        // ORDER BY
4480        //     oprname;
4481        // ```
4482        // Values are also available through
4483        // https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_operator.dat
4484
4485        // ARITHMETIC
4486        "+" => Scalar {
4487            params!(Any) => Operation::new(|ecx, exprs, _params, _order_by| {
4488                // Unary plus has unusual compatibility requirements.
4489                //
4490                // In PostgreSQL, it is only defined for numeric types, so
4491                // `+$1` and `+'1'` get coerced to `Float64` per the usual
4492                // rules, but `+'1'::text` is rejected.
4493                //
4494                // In SQLite, unary plus can be applied to *any* type, and
4495                // is always the identity function.
4496                //
4497                // To try to be compatible with both PostgreSQL and SQlite,
4498                // we accept explicitly-typed arguments of any type, but try
4499                // to coerce unknown-type arguments as `Float64`.
4500                typeconv::plan_coerce(ecx, exprs.into_element(), &SqlScalarType::Float64)
4501            }) => Any, oid::OP_UNARY_PLUS_OID;
4502            params!(Int16, Int16) => BF::from(func::AddInt16) => Int16, 550;
4503            params!(Int32, Int32) => BF::from(func::AddInt32) => Int32, 551;
4504            params!(Int64, Int64) => BF::from(func::AddInt64) => Int64, 684;
4505            params!(UInt16, UInt16) => BF::from(func::AddUint16) => UInt16, oid::FUNC_ADD_UINT16;
4506            params!(UInt32, UInt32) => BF::from(func::AddUint32) => UInt32, oid::FUNC_ADD_UINT32;
4507            params!(UInt64, UInt64) => BF::from(func::AddUint64) => UInt64, oid::FUNC_ADD_UINT64;
4508            params!(Float32, Float32) => BF::from(func::AddFloat32) => Float32, 586;
4509            params!(Float64, Float64) => BF::from(func::AddFloat64) => Float64, 591;
4510            params!(Interval, Interval) => BF::from(func::AddInterval) => Interval, 1337;
4511            params!(Timestamp, Interval) => BF::from(func::AddTimestampInterval) => Timestamp, 2066;
4512            params!(Interval, Timestamp) => {
4513                Operation::binary(|_ecx, lhs, rhs| Ok(rhs.call_binary(lhs, func::AddTimestampInterval)))
4514            } => Timestamp, 2553;
4515            params!(TimestampTz, Interval) => BF::from(func::AddTimestampTzInterval) => TimestampTz, 1327;
4516            params!(Interval, TimestampTz) => {
4517                Operation::binary(|_ecx, lhs, rhs| Ok(rhs.call_binary(lhs, func::AddTimestampTzInterval)))
4518            } => TimestampTz, 2554;
4519            params!(Date, Interval) => BF::from(func::AddDateInterval) => Timestamp, 1076;
4520            params!(Interval, Date) => {
4521                Operation::binary(|_ecx, lhs, rhs| Ok(rhs.call_binary(lhs, func::AddDateInterval)))
4522            } => Timestamp, 2551;
4523            params!(Date, Time) => BF::from(func::AddDateTime) => Timestamp, 1360;
4524            params!(Time, Date) => {
4525                Operation::binary(|_ecx, lhs, rhs| Ok(rhs.call_binary(lhs, func::AddDateTime)))
4526            } => Timestamp, 1363;
4527            params!(Time, Interval) => BF::from(func::AddTimeInterval) => Time, 1800;
4528            params!(Interval, Time) => {
4529                Operation::binary(|_ecx, lhs, rhs| Ok(rhs.call_binary(lhs, func::AddTimeInterval)))
4530            } => Time, 1849;
4531            params!(Numeric, Numeric) => BF::from(func::AddNumeric) => Numeric, 1758;
4532            params!(RangeAny, RangeAny) => BF::from(func::RangeUnion) => RangeAny, 3898;
4533        },
4534        "-" => Scalar {
4535            params!(Int16) => UnaryFunc::NegInt16(func::NegInt16) => Int16, 559;
4536            params!(Int32) => UnaryFunc::NegInt32(func::NegInt32) => Int32, 558;
4537            params!(Int64) => UnaryFunc::NegInt64(func::NegInt64) => Int64, 484;
4538            params!(Float32) => UnaryFunc::NegFloat32(func::NegFloat32) => Float32, 584;
4539            params!(Float64) => UnaryFunc::NegFloat64(func::NegFloat64) => Float64, 585;
4540            params!(Numeric) => UnaryFunc::NegNumeric(func::NegNumeric) => Numeric, 17510;
4541            params!(Interval) => UnaryFunc::NegInterval(func::NegInterval) => Interval, 1336;
4542            params!(Int32, Int32) => BF::from(func::SubInt32) => Int32, 555;
4543            params!(Int64, Int64) => BF::from(func::SubInt64) => Int64, 685;
4544            params!(UInt16, UInt16) => BF::from(func::SubUint16) => UInt16, oid::FUNC_SUB_UINT16;
4545            params!(UInt32, UInt32) => BF::from(func::SubUint32) => UInt32, oid::FUNC_SUB_UINT32;
4546            params!(UInt64, UInt64) => BF::from(func::SubUint64) => UInt64, oid::FUNC_SUB_UINT64;
4547            params!(Float32, Float32) => BF::from(func::SubFloat32) => Float32, 587;
4548            params!(Float64, Float64) => BF::from(func::SubFloat64) => Float64, 592;
4549            params!(Numeric, Numeric) => BF::from(func::SubNumeric) => Numeric, 17590;
4550            params!(Interval, Interval) => BF::from(func::SubInterval) => Interval, 1338;
4551            params!(Timestamp, Timestamp) => BF::from(func::SubTimestamp) => Interval, 2067;
4552            params!(TimestampTz, TimestampTz) => BF::from(func::SubTimestampTz) => Interval, 1328;
4553            params!(Timestamp, Interval) => BF::from(func::SubTimestampInterval) => Timestamp, 2068;
4554            params!(TimestampTz, Interval) => BF::from(func::SubTimestampTzInterval) => TimestampTz, 1329;
4555            params!(Date, Date) => BF::from(func::SubDate) => Int32, 1099;
4556            params!(Date, Interval) => BF::from(func::SubDateInterval) => Timestamp, 1077;
4557            params!(Time, Time) => BF::from(func::SubTime) => Interval, 1399;
4558            params!(Time, Interval) => BF::from(func::SubTimeInterval) => Time, 1801;
4559            params!(Jsonb, Int64) => BF::from(func::JsonbDeleteInt64) => Jsonb, 3286;
4560            params!(Jsonb, String) => BF::from(func::JsonbDeleteString) => Jsonb, 3285;
4561            params!(RangeAny, RangeAny) => BF::from(func::RangeDifference) => RangeAny, 3899;
4562            // TODO(jamii) there should be corresponding overloads for
4563            // Array(Int64) and Array(String)
4564        },
4565        "*" => Scalar {
4566            params!(Int16, Int16) => BF::from(func::MulInt16) => Int16, 526;
4567            params!(Int32, Int32) => BF::from(func::MulInt32) => Int32, 514;
4568            params!(Int64, Int64) => BF::from(func::MulInt64) => Int64, 686;
4569            params!(UInt16, UInt16) => BF::from(func::MulUint16) => UInt16, oid::FUNC_MUL_UINT16;
4570            params!(UInt32, UInt32) => BF::from(func::MulUint32) => UInt32, oid::FUNC_MUL_UINT32;
4571            params!(UInt64, UInt64) => BF::from(func::MulUint64) => UInt64, oid::FUNC_MUL_UINT64;
4572            params!(Float32, Float32) => BF::from(func::MulFloat32) => Float32, 589;
4573            params!(Float64, Float64) => BF::from(func::MulFloat64) => Float64, 594;
4574            params!(Interval, Float64) => BF::from(func::MulInterval) => Interval, 1583;
4575            params!(Float64, Interval) => {
4576                Operation::binary(|_ecx, lhs, rhs| Ok(rhs.call_binary(lhs, func::MulInterval)))
4577            } => Interval, 1584;
4578            params!(Numeric, Numeric) => BF::from(func::MulNumeric) => Numeric, 1760;
4579            params!(RangeAny, RangeAny) => BF::from(func::RangeIntersection) => RangeAny, 3900;
4580        },
4581        "/" => Scalar {
4582            params!(Int16, Int16) => BF::from(func::DivInt16) => Int16, 527;
4583            params!(Int32, Int32) => BF::from(func::DivInt32) => Int32, 528;
4584            params!(Int64, Int64) => BF::from(func::DivInt64) => Int64, 687;
4585            params!(UInt16, UInt16) => BF::from(func::DivUint16) => UInt16, oid::FUNC_DIV_UINT16;
4586            params!(UInt32, UInt32) => BF::from(func::DivUint32) => UInt32, oid::FUNC_DIV_UINT32;
4587            params!(UInt64, UInt64) => BF::from(func::DivUint64) => UInt64, oid::FUNC_DIV_UINT64;
4588            params!(Float32, Float32) => BF::from(func::DivFloat32) => Float32, 588;
4589            params!(Float64, Float64) => BF::from(func::DivFloat64) => Float64, 593;
4590            params!(Interval, Float64) => BF::from(func::DivInterval) => Interval, 1585;
4591            params!(Numeric, Numeric) => BF::from(func::DivNumeric) => Numeric, 1761;
4592        },
4593        "%" => Scalar {
4594            params!(Int16, Int16) => BF::from(func::ModInt16) => Int16, 529;
4595            params!(Int32, Int32) => BF::from(func::ModInt32) => Int32, 530;
4596            params!(Int64, Int64) => BF::from(func::ModInt64) => Int64, 439;
4597            params!(UInt16, UInt16) => BF::from(func::ModUint16) => UInt16, oid::FUNC_MOD_UINT16;
4598            params!(UInt32, UInt32) => BF::from(func::ModUint32) => UInt32, oid::FUNC_MOD_UINT32;
4599            params!(UInt64, UInt64) => BF::from(func::ModUint64) => UInt64, oid::FUNC_MOD_UINT64;
4600            params!(Float32, Float32) => BF::from(func::ModFloat32) => Float32, oid::OP_MOD_F32_OID;
4601            params!(Float64, Float64) => BF::from(func::ModFloat64) => Float64, oid::OP_MOD_F64_OID;
4602            params!(Numeric, Numeric) => BF::from(func::ModNumeric) => Numeric, 1762;
4603        },
4604        "&" => Scalar {
4605            params!(Int16, Int16) => BF::from(func::BitAndInt16) => Int16, 1874;
4606            params!(Int32, Int32) => BF::from(func::BitAndInt32) => Int32, 1880;
4607            params!(Int64, Int64) => BF::from(func::BitAndInt64) => Int64, 1886;
4608            params!(UInt16, UInt16) => BF::from(func::BitAndUint16) => UInt16, oid::FUNC_AND_UINT16;
4609            params!(UInt32, UInt32) => BF::from(func::BitAndUint32) => UInt32, oid::FUNC_AND_UINT32;
4610            params!(UInt64, UInt64) => BF::from(func::BitAndUint64) => UInt64, oid::FUNC_AND_UINT64;
4611        },
4612        "|" => Scalar {
4613            params!(Int16, Int16) => BF::from(func::BitOrInt16) => Int16, 1875;
4614            params!(Int32, Int32) => BF::from(func::BitOrInt32) => Int32, 1881;
4615            params!(Int64, Int64) => BF::from(func::BitOrInt64) => Int64, 1887;
4616            params!(UInt16, UInt16) => BF::from(func::BitOrUint16) => UInt16, oid::FUNC_OR_UINT16;
4617            params!(UInt32, UInt32) => BF::from(func::BitOrUint32) => UInt32, oid::FUNC_OR_UINT32;
4618            params!(UInt64, UInt64) => BF::from(func::BitOrUint64) => UInt64, oid::FUNC_OR_UINT64;
4619        },
4620        "#" => Scalar {
4621            params!(Int16, Int16) => BF::from(func::BitXorInt16) => Int16, 1876;
4622            params!(Int32, Int32) => BF::from(func::BitXorInt32) => Int32, 1882;
4623            params!(Int64, Int64) => BF::from(func::BitXorInt64) => Int64, 1888;
4624            params!(UInt16, UInt16) => BF::from(func::BitXorUint16) => UInt16, oid::FUNC_XOR_UINT16;
4625            params!(UInt32, UInt32) => BF::from(func::BitXorUint32) => UInt32, oid::FUNC_XOR_UINT32;
4626            params!(UInt64, UInt64) => BF::from(func::BitXorUint64) => UInt64, oid::FUNC_XOR_UINT64;
4627        },
4628        "<<" => Scalar {
4629            params!(Int16, Int32) => BF::from(func::BitShiftLeftInt16) => Int16, 1878;
4630            params!(Int32, Int32) => BF::from(func::BitShiftLeftInt32) => Int32, 1884;
4631            params!(Int64, Int32) => BF::from(func::BitShiftLeftInt64) => Int64, 1890;
4632            params!(UInt16, UInt32) => BF::from(func::BitShiftLeftUint16) => UInt16, oid::FUNC_SHIFT_LEFT_UINT16;
4633            params!(UInt32, UInt32) => BF::from(func::BitShiftLeftUint32) => UInt32, oid::FUNC_SHIFT_LEFT_UINT32;
4634            params!(UInt64, UInt32) => BF::from(func::BitShiftLeftUint64) => UInt64, oid::FUNC_SHIFT_LEFT_UINT64;
4635            params!(RangeAny, RangeAny) => BF::from(func::RangeBefore) => Bool, 3893;
4636        },
4637        ">>" => Scalar {
4638            params!(Int16, Int32) => BF::from(func::BitShiftRightInt16) => Int16, 1879;
4639            params!(Int32, Int32) => BF::from(func::BitShiftRightInt32) => Int32, 1885;
4640            params!(Int64, Int32) => BF::from(func::BitShiftRightInt64) => Int64, 1891;
4641            params!(UInt16, UInt32) => BF::from(func::BitShiftRightUint16) => UInt16, oid::FUNC_SHIFT_RIGHT_UINT16;
4642            params!(UInt32, UInt32) => BF::from(func::BitShiftRightUint32) => UInt32, oid::FUNC_SHIFT_RIGHT_UINT32;
4643            params!(UInt64, UInt32) => BF::from(func::BitShiftRightUint64) => UInt64, oid::FUNC_SHIFT_RIGHT_UINT64;
4644            params!(RangeAny, RangeAny) => BF::from(func::RangeAfter) => Bool, 3894;
4645        },
4646
4647        // ILIKE
4648        "~~*" => Scalar {
4649            params!(String, String) => BF::IsLikeMatch { case_insensitive: true } => Bool, 1627;
4650            params!(Char, String) => Operation::binary(|ecx, lhs, rhs| {
4651                let length = ecx.scalar_type(&lhs).unwrap_char_length();
4652                Ok(lhs.call_unary(UnaryFunc::PadChar(func::PadChar { length }))
4653                    .call_binary(rhs, BF::IsLikeMatch { case_insensitive: true })
4654                )
4655            }) => Bool, 1629;
4656        },
4657        "!~~*" => Scalar {
4658            params!(String, String) => Operation::binary(|_ecx, lhs, rhs| {
4659                Ok(lhs
4660                    .call_binary(rhs, BF::IsLikeMatch { case_insensitive: true })
4661                    .call_unary(UnaryFunc::Not(func::Not)))
4662            }) => Bool, 1628;
4663            params!(Char, String) => Operation::binary(|ecx, lhs, rhs| {
4664                let length = ecx.scalar_type(&lhs).unwrap_char_length();
4665                Ok(lhs.call_unary(UnaryFunc::PadChar(func::PadChar { length }))
4666                    .call_binary(rhs, BF::IsLikeMatch { case_insensitive: true })
4667                    .call_unary(UnaryFunc::Not(func::Not))
4668                )
4669            }) => Bool, 1630;
4670        },
4671
4672
4673        // LIKE
4674        "~~" => Scalar {
4675            params!(String, String) => BF::IsLikeMatch { case_insensitive: false } => Bool, 1209;
4676            params!(Char, String) => Operation::binary(|ecx, lhs, rhs| {
4677                let length = ecx.scalar_type(&lhs).unwrap_char_length();
4678                Ok(lhs.call_unary(UnaryFunc::PadChar(func::PadChar { length }))
4679                    .call_binary(rhs, BF::IsLikeMatch { case_insensitive: false })
4680                )
4681            }) => Bool, 1211;
4682        },
4683        "!~~" => Scalar {
4684            params!(String, String) => Operation::binary(|_ecx, lhs, rhs| {
4685                Ok(lhs
4686                    .call_binary(rhs, BF::IsLikeMatch { case_insensitive: false })
4687                    .call_unary(UnaryFunc::Not(func::Not)))
4688            }) => Bool, 1210;
4689            params!(Char, String) => Operation::binary(|ecx, lhs, rhs| {
4690                let length = ecx.scalar_type(&lhs).unwrap_char_length();
4691                Ok(lhs.call_unary(UnaryFunc::PadChar(func::PadChar { length }))
4692                    .call_binary(rhs, BF::IsLikeMatch { case_insensitive: false })
4693                    .call_unary(UnaryFunc::Not(func::Not))
4694                )
4695            }) => Bool, 1212;
4696        },
4697
4698        // REGEX
4699        "~" => Scalar {
4700            params!(Int16) => UnaryFunc::BitNotInt16(func::BitNotInt16) => Int16, 1877;
4701            params!(Int32) => UnaryFunc::BitNotInt32(func::BitNotInt32) => Int32, 1883;
4702            params!(Int64) => UnaryFunc::BitNotInt64(func::BitNotInt64) => Int64, 1889;
4703            params!(UInt16) => UnaryFunc::BitNotUint16(func::BitNotUint16) => UInt16, oid::FUNC_BIT_NOT_UINT16_OID;
4704            params!(UInt32) => UnaryFunc::BitNotUint32(func::BitNotUint32) => UInt32, oid::FUNC_BIT_NOT_UINT32_OID;
4705            params!(UInt64) => UnaryFunc::BitNotUint64(func::BitNotUint64) => UInt64, oid::FUNC_BIT_NOT_UINT64_OID;
4706            params!(String, String) => BinaryFunc::IsRegexpMatch { case_insensitive: false } => Bool, 641;
4707            params!(Char, String) => Operation::binary(|ecx, lhs, rhs| {
4708                let length = ecx.scalar_type(&lhs).unwrap_char_length();
4709                Ok(lhs.call_unary(UnaryFunc::PadChar(func::PadChar { length }))
4710                    .call_binary(rhs, BinaryFunc::IsRegexpMatch { case_insensitive: false })
4711                )
4712            }) => Bool, 1055;
4713        },
4714        "~*" => Scalar {
4715            params!(String, String) => Operation::binary(|_ecx, lhs, rhs| {
4716                Ok(lhs.call_binary(rhs, BinaryFunc::IsRegexpMatch { case_insensitive: true }))
4717            }) => Bool, 1228;
4718            params!(Char, String) => Operation::binary(|ecx, lhs, rhs| {
4719                let length = ecx.scalar_type(&lhs).unwrap_char_length();
4720                Ok(lhs.call_unary(UnaryFunc::PadChar(func::PadChar { length }))
4721                    .call_binary(rhs, BinaryFunc::IsRegexpMatch { case_insensitive: true })
4722                )
4723            }) => Bool, 1234;
4724        },
4725        "!~" => Scalar {
4726            params!(String, String) => Operation::binary(|_ecx, lhs, rhs| {
4727                Ok(lhs
4728                    .call_binary(rhs, BinaryFunc::IsRegexpMatch { case_insensitive: false })
4729                    .call_unary(UnaryFunc::Not(func::Not)))
4730            }) => Bool, 642;
4731            params!(Char, String) => Operation::binary(|ecx, lhs, rhs| {
4732                let length = ecx.scalar_type(&lhs).unwrap_char_length();
4733                Ok(lhs.call_unary(UnaryFunc::PadChar(func::PadChar { length }))
4734                    .call_binary(rhs, BinaryFunc::IsRegexpMatch { case_insensitive: false })
4735                    .call_unary(UnaryFunc::Not(func::Not))
4736                )
4737            }) => Bool, 1056;
4738        },
4739        "!~*" => Scalar {
4740            params!(String, String) => Operation::binary(|_ecx, lhs, rhs| {
4741                Ok(lhs
4742                    .call_binary(rhs, BinaryFunc::IsRegexpMatch { case_insensitive: true })
4743                    .call_unary(UnaryFunc::Not(func::Not)))
4744            }) => Bool, 1229;
4745            params!(Char, String) => Operation::binary(|ecx, lhs, rhs| {
4746                let length = ecx.scalar_type(&lhs).unwrap_char_length();
4747                Ok(lhs.call_unary(UnaryFunc::PadChar(func::PadChar { length }))
4748                    .call_binary(rhs, BinaryFunc::IsRegexpMatch { case_insensitive: true })
4749                    .call_unary(UnaryFunc::Not(func::Not))
4750                )
4751            }) => Bool, 1235;
4752        },
4753
4754        // CONCAT
4755        "||" => Scalar {
4756            params!(String, NonVecAny) => Operation::binary(|ecx, lhs, rhs| {
4757                let rhs = typeconv::plan_cast(
4758                    ecx,
4759                    CastContext::Explicit,
4760                    rhs,
4761                    &SqlScalarType::String,
4762                )?;
4763                Ok(lhs.call_binary(rhs, func::TextConcatBinary))
4764            }) => String, 2779;
4765            params!(NonVecAny, String) => Operation::binary(|ecx, lhs, rhs| {
4766                let lhs = typeconv::plan_cast(
4767                    ecx,
4768                    CastContext::Explicit,
4769                    lhs,
4770                    &SqlScalarType::String,
4771                )?;
4772                Ok(lhs.call_binary(rhs, func::TextConcatBinary))
4773            }) => String, 2780;
4774            params!(String, String) => BF::from(func::TextConcatBinary) => String, 654;
4775            params!(Jsonb, Jsonb) => BF::from(func::JsonbConcat) => Jsonb, 3284;
4776            params!(ArrayAnyCompatible, ArrayAnyCompatible) => BF::from(func::ArrayArrayConcat) => ArrayAnyCompatible, 375;
4777            params!(ListAnyCompatible, ListAnyCompatible) => BF::from(func::ListListConcat) => ListAnyCompatible, oid::OP_CONCAT_LIST_LIST_OID;
4778            params!(ListAnyCompatible, ListElementAnyCompatible) => BF::from(func::ListElementConcat) => ListAnyCompatible, oid::OP_CONCAT_LIST_ELEMENT_OID;
4779            params!(ListElementAnyCompatible, ListAnyCompatible) => BF::from(func::ElementListConcat) => ListAnyCompatible, oid::OP_CONCAT_ELEMENY_LIST_OID;
4780        },
4781
4782        // JSON, MAP, RANGE, LIST, ARRAY
4783        "->" => Scalar {
4784            params!(Jsonb, Int64) => BinaryFunc::JsonbGetInt64 => Jsonb, 3212;
4785            params!(Jsonb, String) => BinaryFunc::JsonbGetString => Jsonb, 3211;
4786            params!(MapAny, String) => BF::from(func::MapGetValue) => Any, oid::OP_GET_VALUE_MAP_OID;
4787        },
4788        "->>" => Scalar {
4789            params!(Jsonb, Int64) => BinaryFunc::JsonbGetInt64Stringify => String, 3481;
4790            params!(Jsonb, String) => BinaryFunc::JsonbGetStringStringify => String, 3477;
4791        },
4792        "#>" => Scalar {
4793            params!(Jsonb, SqlScalarType::Array(Box::new(SqlScalarType::String))) => BinaryFunc::JsonbGetPath => Jsonb, 3213;
4794        },
4795        "#>>" => Scalar {
4796            params!(Jsonb, SqlScalarType::Array(Box::new(SqlScalarType::String))) => BinaryFunc::JsonbGetPathStringify => String, 3206;
4797        },
4798        "@>" => Scalar {
4799            params!(Jsonb, Jsonb) => BF::from(func::JsonbContainsJsonb) => Bool, 3246;
4800            params!(Jsonb, String) => Operation::binary(|_ecx, lhs, rhs| {
4801                Ok(lhs.call_binary(
4802                    rhs.call_unary(UnaryFunc::CastStringToJsonb(func::CastStringToJsonb)),
4803                    BinaryFunc::from(func::JsonbContainsJsonb),
4804                ))
4805            }) => Bool, oid::OP_CONTAINS_JSONB_STRING_OID;
4806            params!(String, Jsonb) => Operation::binary(|_ecx, lhs, rhs| {
4807                Ok(lhs.call_unary(UnaryFunc::CastStringToJsonb(func::CastStringToJsonb))
4808                      .call_binary(rhs, func::JsonbContainsJsonb))
4809            }) => Bool, oid::OP_CONTAINS_STRING_JSONB_OID;
4810            params!(MapAnyCompatible, MapAnyCompatible) => BF::from(func::MapContainsMap) => Bool, oid::OP_CONTAINS_MAP_MAP_OID;
4811            params!(RangeAny, AnyElement) => Operation::binary(|ecx, lhs, rhs| {
4812                let elem_type = ecx.scalar_type(&lhs).unwrap_range_element_type().clone();
4813                Ok(lhs.call_binary(rhs, BinaryFunc::RangeContainsElem { elem_type, rev: false }))
4814            }) => Bool, 3889;
4815            params!(RangeAny, RangeAny) => Operation::binary(|_ecx, lhs, rhs| {
4816                Ok(lhs.call_binary(rhs, BinaryFunc::RangeContainsRange { rev: false }))
4817            }) => Bool, 3890;
4818            params!(ArrayAny, ArrayAny) => Operation::binary(|_ecx, lhs, rhs| {
4819                Ok(lhs.call_binary(rhs, BinaryFunc::ArrayContainsArray { rev: false }))
4820            }) => Bool, 2751;
4821            params!(ListAny, ListAny) => Operation::binary(|_ecx, lhs, rhs| {
4822                Ok(lhs.call_binary(rhs, BinaryFunc::ListContainsList { rev: false }))
4823            }) => Bool, oid::OP_CONTAINS_LIST_LIST_OID;
4824        },
4825        "<@" => Scalar {
4826            params!(Jsonb, Jsonb) => Operation::binary(|_ecx, lhs, rhs| {
4827                Ok(rhs.call_binary(
4828                    lhs,
4829                    func::JsonbContainsJsonb
4830                ))
4831            }) => Bool, 3250;
4832            params!(Jsonb, String) => Operation::binary(|_ecx, lhs, rhs| {
4833                Ok(rhs.call_unary(UnaryFunc::CastStringToJsonb(func::CastStringToJsonb))
4834                      .call_binary(lhs, func::JsonbContainsJsonb))
4835            }) => Bool, oid::OP_CONTAINED_JSONB_STRING_OID;
4836            params!(String, Jsonb) => Operation::binary(|_ecx, lhs, rhs| {
4837                Ok(rhs.call_binary(
4838                    lhs.call_unary(UnaryFunc::CastStringToJsonb(func::CastStringToJsonb)),
4839                    func::JsonbContainsJsonb,
4840                ))
4841            }) => Bool, oid::OP_CONTAINED_STRING_JSONB_OID;
4842            params!(MapAnyCompatible, MapAnyCompatible) => Operation::binary(|_ecx, lhs, rhs| {
4843                Ok(rhs.call_binary(lhs, func::MapContainsMap))
4844            }) => Bool, oid::OP_CONTAINED_MAP_MAP_OID;
4845            params!(AnyElement, RangeAny) => Operation::binary(|ecx, lhs, rhs| {
4846                let elem_type = ecx.scalar_type(&rhs).unwrap_range_element_type().clone();
4847                Ok(rhs.call_binary(lhs, BF::RangeContainsElem { elem_type, rev: true }))
4848            }) => Bool, 3891;
4849            params!(RangeAny, RangeAny) => Operation::binary(|_ecx, lhs, rhs| {
4850                Ok(rhs.call_binary(lhs, BF::RangeContainsRange { rev: true }))
4851            }) => Bool, 3892;
4852            params!(ArrayAny, ArrayAny) => Operation::binary(|_ecx, lhs, rhs| {
4853                Ok(lhs.call_binary(rhs, BF::ArrayContainsArray { rev: true }))
4854            }) => Bool, 2752;
4855            params!(ListAny, ListAny) => Operation::binary(|_ecx, lhs, rhs| {
4856                Ok(lhs.call_binary(rhs, BF::ListContainsList { rev: true }))
4857            }) => Bool, oid::OP_IS_CONTAINED_LIST_LIST_OID;
4858        },
4859        "?" => Scalar {
4860            params!(Jsonb, String) => BF::from(func::JsonbContainsString) => Bool, 3247;
4861            params!(MapAny, String) => BF::from(func::MapContainsKey) => Bool, oid::OP_CONTAINS_KEY_MAP_OID;
4862        },
4863        "?&" => Scalar {
4864            params!(MapAny, SqlScalarType::Array(Box::new(SqlScalarType::String))) => BF::from(func::MapContainsAllKeys) => Bool, oid::OP_CONTAINS_ALL_KEYS_MAP_OID;
4865        },
4866        "?|" => Scalar {
4867            params!(MapAny, SqlScalarType::Array(Box::new(SqlScalarType::String))) => BF::from(func::MapContainsAnyKeys) => Bool, oid::OP_CONTAINS_ANY_KEYS_MAP_OID;
4868        },
4869        "&&" => Scalar {
4870            params!(RangeAny, RangeAny) => BF::from(func::RangeOverlaps) => Bool, 3888;
4871        },
4872        "&<" => Scalar {
4873            params!(RangeAny, RangeAny) => BF::from(func::RangeOverleft) => Bool, 3895;
4874        },
4875        "&>" => Scalar {
4876            params!(RangeAny, RangeAny) => BF::from(func::RangeOverright) => Bool, 3896;
4877        },
4878        "-|-" => Scalar {
4879            params!(RangeAny, RangeAny) => BF::from(func::RangeAdjacent) => Bool, 3897;
4880        },
4881
4882        // COMPARISON OPS
4883        "<" => Scalar {
4884            params!(Numeric, Numeric) => BF::from(func::Lt) => Bool, 1754;
4885            params!(Bool, Bool) => BF::from(func::Lt) => Bool, 58;
4886            params!(Int16, Int16) => BF::from(func::Lt) => Bool, 95;
4887            params!(Int32, Int32) => BF::from(func::Lt) => Bool, 97;
4888            params!(Int64, Int64) => BF::from(func::Lt) => Bool, 412;
4889            params!(UInt16, UInt16) => BF::from(func::Lt) => Bool, oid::FUNC_LT_UINT16_OID;
4890            params!(UInt32, UInt32) => BF::from(func::Lt) => Bool, oid::FUNC_LT_UINT32_OID;
4891            params!(UInt64, UInt64) => BF::from(func::Lt) => Bool, oid::FUNC_LT_UINT64_OID;
4892            params!(Float32, Float32) => BF::from(func::Lt) => Bool, 622;
4893            params!(Float64, Float64) => BF::from(func::Lt) => Bool, 672;
4894            params!(Oid, Oid) => BF::from(func::Lt) => Bool, 609;
4895            params!(Date, Date) => BF::from(func::Lt) => Bool, 1095;
4896            params!(Time, Time) => BF::from(func::Lt) => Bool, 1110;
4897            params!(Timestamp, Timestamp) => BF::from(func::Lt) => Bool, 2062;
4898            params!(TimestampTz, TimestampTz) => BF::from(func::Lt) => Bool, 1322;
4899            params!(Uuid, Uuid) => BF::from(func::Lt) => Bool, 2974;
4900            params!(Interval, Interval) => BF::from(func::Lt) => Bool, 1332;
4901            params!(Bytes, Bytes) => BF::from(func::Lt) => Bool, 1957;
4902            params!(String, String) => BF::from(func::Lt) => Bool, 664;
4903            params!(Char, Char) => BF::from(func::Lt) => Bool, 1058;
4904            params!(PgLegacyChar, PgLegacyChar) => BF::from(func::Lt) => Bool, 631;
4905            params!(PgLegacyName, PgLegacyName) => BF::from(func::Lt) => Bool, 660;
4906            params!(Jsonb, Jsonb) => BF::from(func::Lt) => Bool, 3242;
4907            params!(ArrayAny, ArrayAny) => BF::from(func::Lt) => Bool, 1072;
4908            params!(RecordAny, RecordAny) => BF::from(func::Lt) => Bool, 2990;
4909            params!(MzTimestamp, MzTimestamp) => BF::from(func::Lt) => Bool, oid::FUNC_MZ_TIMESTAMP_LT_MZ_TIMESTAMP_OID;
4910            params!(RangeAny, RangeAny) => BF::from(func::Lt) => Bool, 3884;
4911        },
4912        "<=" => Scalar {
4913            params!(Numeric, Numeric) => BF::from(func::Lte) => Bool, 1755;
4914            params!(Bool, Bool) => BF::from(func::Lte) => Bool, 1694;
4915            params!(Int16, Int16) => BF::from(func::Lte) => Bool, 522;
4916            params!(Int32, Int32) => BF::from(func::Lte) => Bool, 523;
4917            params!(Int64, Int64) => BF::from(func::Lte) => Bool, 414;
4918            params!(UInt16, UInt16) => BF::from(func::Lte) => Bool, oid::FUNC_LTE_UINT16_OID;
4919            params!(UInt32, UInt32) => BF::from(func::Lte) => Bool, oid::FUNC_LTE_UINT32_OID;
4920            params!(UInt64, UInt64) => BF::from(func::Lte) => Bool, oid::FUNC_LTE_UINT64_OID;
4921            params!(Float32, Float32) => BF::from(func::Lte) => Bool, 624;
4922            params!(Float64, Float64) => BF::from(func::Lte) => Bool, 673;
4923            params!(Oid, Oid) => BF::from(func::Lte) => Bool, 611;
4924            params!(Date, Date) => BF::from(func::Lte) => Bool, 1096;
4925            params!(Time, Time) => BF::from(func::Lte) => Bool, 1111;
4926            params!(Timestamp, Timestamp) => BF::from(func::Lte) => Bool, 2063;
4927            params!(TimestampTz, TimestampTz) => BF::from(func::Lte) => Bool, 1323;
4928            params!(Uuid, Uuid) => BF::from(func::Lte) => Bool, 2976;
4929            params!(Interval, Interval) => BF::from(func::Lte) => Bool, 1333;
4930            params!(Bytes, Bytes) => BF::from(func::Lte) => Bool, 1958;
4931            params!(String, String) => BF::from(func::Lte) => Bool, 665;
4932            params!(Char, Char) => BF::from(func::Lte) => Bool, 1059;
4933            params!(PgLegacyChar, PgLegacyChar) => BF::from(func::Lte) => Bool, 632;
4934            params!(PgLegacyName, PgLegacyName) => BF::from(func::Lte) => Bool, 661;
4935            params!(Jsonb, Jsonb) => BF::from(func::Lte) => Bool, 3244;
4936            params!(ArrayAny, ArrayAny) => BF::from(func::Lte) => Bool, 1074;
4937            params!(RecordAny, RecordAny) => BF::from(func::Lte) => Bool, 2992;
4938            params!(MzTimestamp, MzTimestamp) => BF::from(func::Lte) => Bool, oid::FUNC_MZ_TIMESTAMP_LTE_MZ_TIMESTAMP_OID;
4939            params!(RangeAny, RangeAny) => BF::from(func::Lte) => Bool, 3885;
4940        },
4941        ">" => Scalar {
4942            params!(Numeric, Numeric) => BF::from(func::Gt) => Bool, 1756;
4943            params!(Bool, Bool) => BF::from(func::Gt) => Bool, 59;
4944            params!(Int16, Int16) => BF::from(func::Gt) => Bool, 520;
4945            params!(Int32, Int32) => BF::from(func::Gt) => Bool, 521;
4946            params!(Int64, Int64) => BF::from(func::Gt) => Bool, 413;
4947            params!(UInt16, UInt16) => BF::from(func::Gt) => Bool, oid::FUNC_GT_UINT16_OID;
4948            params!(UInt32, UInt32) => BF::from(func::Gt) => Bool, oid::FUNC_GT_UINT32_OID;
4949            params!(UInt64, UInt64) => BF::from(func::Gt) => Bool, oid::FUNC_GT_UINT64_OID;
4950            params!(Float32, Float32) => BF::from(func::Gt) => Bool, 623;
4951            params!(Float64, Float64) => BF::from(func::Gt) => Bool, 674;
4952            params!(Oid, Oid) => BF::from(func::Gt) => Bool, 610;
4953            params!(Date, Date) => BF::from(func::Gt) => Bool, 1097;
4954            params!(Time, Time) => BF::from(func::Gt) => Bool, 1112;
4955            params!(Timestamp, Timestamp) => BF::from(func::Gt) => Bool, 2064;
4956            params!(TimestampTz, TimestampTz) => BF::from(func::Gt) => Bool, 1324;
4957            params!(Uuid, Uuid) => BF::from(func::Gt) => Bool, 2975;
4958            params!(Interval, Interval) => BF::from(func::Gt) => Bool, 1334;
4959            params!(Bytes, Bytes) => BF::from(func::Gt) => Bool, 1959;
4960            params!(String, String) => BF::from(func::Gt) => Bool, 666;
4961            params!(Char, Char) => BF::from(func::Gt) => Bool, 1060;
4962            params!(PgLegacyChar, PgLegacyChar) => BF::from(func::Gt) => Bool, 633;
4963            params!(PgLegacyName, PgLegacyName) => BF::from(func::Gt) => Bool, 662;
4964            params!(Jsonb, Jsonb) => BF::from(func::Gt) => Bool, 3243;
4965            params!(ArrayAny, ArrayAny) => BF::from(func::Gt) => Bool, 1073;
4966            params!(RecordAny, RecordAny) => BF::from(func::Gt) => Bool, 2991;
4967            params!(MzTimestamp, MzTimestamp) => BF::from(func::Gt) => Bool, oid::FUNC_MZ_TIMESTAMP_GT_MZ_TIMESTAMP_OID;
4968            params!(RangeAny, RangeAny) => BF::from(func::Gt) => Bool, 3887;
4969        },
4970        ">=" => Scalar {
4971            params!(Numeric, Numeric) => BF::from(func::Gte) => Bool, 1757;
4972            params!(Bool, Bool) => BF::from(func::Gte) => Bool, 1695;
4973            params!(Int16, Int16) => BF::from(func::Gte) => Bool, 524;
4974            params!(Int32, Int32) => BF::from(func::Gte) => Bool, 525;
4975            params!(Int64, Int64) => BF::from(func::Gte) => Bool, 415;
4976            params!(UInt16, UInt16) => BF::from(func::Gte) => Bool, oid::FUNC_GTE_UINT16_OID;
4977            params!(UInt32, UInt32) => BF::from(func::Gte) => Bool, oid::FUNC_GTE_UINT32_OID;
4978            params!(UInt64, UInt64) => BF::from(func::Gte) => Bool, oid::FUNC_GTE_UINT64_OID;
4979            params!(Float32, Float32) => BF::from(func::Gte) => Bool, 625;
4980            params!(Float64, Float64) => BF::from(func::Gte) => Bool, 675;
4981            params!(Oid, Oid) => BF::from(func::Gte) => Bool, 612;
4982            params!(Date, Date) => BF::from(func::Gte) => Bool, 1098;
4983            params!(Time, Time) => BF::from(func::Gte) => Bool, 1113;
4984            params!(Timestamp, Timestamp) => BF::from(func::Gte) => Bool, 2065;
4985            params!(TimestampTz, TimestampTz) => BF::from(func::Gte) => Bool, 1325;
4986            params!(Uuid, Uuid) => BF::from(func::Gte) => Bool, 2977;
4987            params!(Interval, Interval) => BF::from(func::Gte) => Bool, 1335;
4988            params!(Bytes, Bytes) => BF::from(func::Gte) => Bool, 1960;
4989            params!(String, String) => BF::from(func::Gte) => Bool, 667;
4990            params!(Char, Char) => BF::from(func::Gte) => Bool, 1061;
4991            params!(PgLegacyChar, PgLegacyChar) => BF::from(func::Gte) => Bool, 634;
4992            params!(PgLegacyName, PgLegacyName) => BF::from(func::Gte) => Bool, 663;
4993            params!(Jsonb, Jsonb) => BF::from(func::Gte) => Bool, 3245;
4994            params!(ArrayAny, ArrayAny) => BF::from(func::Gte) => Bool, 1075;
4995            params!(RecordAny, RecordAny) => BF::from(func::Gte) => Bool, 2993;
4996            params!(MzTimestamp, MzTimestamp) => BF::from(func::Gte) => Bool, oid::FUNC_MZ_TIMESTAMP_GTE_MZ_TIMESTAMP_OID;
4997            params!(RangeAny, RangeAny) => BF::from(func::Gte) => Bool, 3886;
4998        },
4999        // Warning!
5000        // - If you are writing functions here that do not simply use
5001        //   `BinaryFunc::Eq`, you will break row equality (used in e.g.
5002        //   DISTINCT operations and JOINs). In short, this is totally verboten.
5003        // - The implementation of `BinaryFunc::Eq` is byte equality on two
5004        //   datums, and we enforce that both inputs to the function are of the
5005        //   same type in planning. However, it's possible that we will perform
5006        //   equality on types not listed here (e.g. `Varchar`) due to decisions
5007        //   made in the optimizer.
5008        // - Null inputs are handled by `BinaryFunc::eval` checking `propagates_nulls`.
5009        "=" => Scalar {
5010            params!(Numeric, Numeric) => BF::from(func::Eq) => Bool, 1752;
5011            params!(Bool, Bool) => BF::from(func::Eq) => Bool, 91;
5012            params!(Int16, Int16) => BF::from(func::Eq) => Bool, 94;
5013            params!(Int32, Int32) => BF::from(func::Eq) => Bool, 96;
5014            params!(Int64, Int64) => BF::from(func::Eq) => Bool, 410;
5015            params!(UInt16, UInt16) => BF::from(func::Eq) => Bool, oid::FUNC_EQ_UINT16_OID;
5016            params!(UInt32, UInt32) => BF::from(func::Eq) => Bool, oid::FUNC_EQ_UINT32_OID;
5017            params!(UInt64, UInt64) => BF::from(func::Eq) => Bool, oid::FUNC_EQ_UINT64_OID;
5018            params!(Float32, Float32) => BF::from(func::Eq) => Bool, 620;
5019            params!(Float64, Float64) => BF::from(func::Eq) => Bool, 670;
5020            params!(Oid, Oid) => BF::from(func::Eq) => Bool, 607;
5021            params!(Date, Date) => BF::from(func::Eq) => Bool, 1093;
5022            params!(Time, Time) => BF::from(func::Eq) => Bool, 1108;
5023            params!(Timestamp, Timestamp) => BF::from(func::Eq) => Bool, 2060;
5024            params!(TimestampTz, TimestampTz) => BF::from(func::Eq) => Bool, 1320;
5025            params!(Uuid, Uuid) => BF::from(func::Eq) => Bool, 2972;
5026            params!(Interval, Interval) => BF::from(func::Eq) => Bool, 1330;
5027            params!(Bytes, Bytes) => BF::from(func::Eq) => Bool, 1955;
5028            params!(String, String) => BF::from(func::Eq) => Bool, 98;
5029            params!(Char, Char) => BF::from(func::Eq) => Bool, 1054;
5030            params!(PgLegacyChar, PgLegacyChar) => BF::from(func::Eq) => Bool, 92;
5031            params!(PgLegacyName, PgLegacyName) => BF::from(func::Eq) => Bool, 93;
5032            params!(Jsonb, Jsonb) => BF::from(func::Eq) => Bool, 3240;
5033            params!(ListAny, ListAny) => BF::from(func::Eq) => Bool, oid::FUNC_LIST_EQ_OID;
5034            params!(ArrayAny, ArrayAny) => BF::from(func::Eq) => Bool, 1070;
5035            params!(RecordAny, RecordAny) => BF::from(func::Eq) => Bool, 2988;
5036            params!(MzTimestamp, MzTimestamp) => BF::from(func::Eq) => Bool, oid::FUNC_MZ_TIMESTAMP_EQ_MZ_TIMESTAMP_OID;
5037            params!(RangeAny, RangeAny) => BF::from(func::Eq) => Bool, 3882;
5038            params!(MzAclItem, MzAclItem) => BF::from(func::Eq) => Bool, oid::FUNC_MZ_ACL_ITEM_EQ_MZ_ACL_ITEM_OID;
5039            params!(AclItem, AclItem) => BF::from(func::Eq) => Bool, 974;
5040        },
5041        "<>" => Scalar {
5042            params!(Numeric, Numeric) => BF::from(func::NotEq) => Bool, 1753;
5043            params!(Bool, Bool) => BF::from(func::NotEq) => Bool, 85;
5044            params!(Int16, Int16) => BF::from(func::NotEq) => Bool, 519;
5045            params!(Int32, Int32) => BF::from(func::NotEq) => Bool, 518;
5046            params!(Int64, Int64) => BF::from(func::NotEq) => Bool, 411;
5047            params!(UInt16, UInt16) => BF::from(func::NotEq) => Bool, oid::FUNC_NOT_EQ_UINT16_OID;
5048            params!(UInt32, UInt32) => BF::from(func::NotEq) => Bool, oid::FUNC_NOT_EQ_UINT32_OID;
5049            params!(UInt64, UInt64) => BF::from(func::NotEq) => Bool, oid::FUNC_NOT_EQ_UINT64_OID;
5050            params!(Float32, Float32) => BF::from(func::NotEq) => Bool, 621;
5051            params!(Float64, Float64) => BF::from(func::NotEq) => Bool, 671;
5052            params!(Oid, Oid) => BF::from(func::NotEq) => Bool, 608;
5053            params!(Date, Date) => BF::from(func::NotEq) => Bool, 1094;
5054            params!(Time, Time) => BF::from(func::NotEq) => Bool, 1109;
5055            params!(Timestamp, Timestamp) => BF::from(func::NotEq) => Bool, 2061;
5056            params!(TimestampTz, TimestampTz) => BF::from(func::NotEq) => Bool, 1321;
5057            params!(Uuid, Uuid) => BF::from(func::NotEq) => Bool, 2973;
5058            params!(Interval, Interval) => BF::from(func::NotEq) => Bool, 1331;
5059            params!(Bytes, Bytes) => BF::from(func::NotEq) => Bool, 1956;
5060            params!(String, String) => BF::from(func::NotEq) => Bool, 531;
5061            params!(Char, Char) => BF::from(func::NotEq) => Bool, 1057;
5062            params!(PgLegacyChar, PgLegacyChar) => BF::from(func::NotEq) => Bool, 630;
5063            params!(PgLegacyName, PgLegacyName) => BF::from(func::NotEq) => Bool, 643;
5064            params!(Jsonb, Jsonb) => BF::from(func::NotEq) => Bool, 3241;
5065            params!(ArrayAny, ArrayAny) => BF::from(func::NotEq) => Bool, 1071;
5066            params!(RecordAny, RecordAny) => BF::from(func::NotEq) => Bool, 2989;
5067            params!(MzTimestamp, MzTimestamp) => BF::from(func::NotEq) => Bool, oid::FUNC_MZ_TIMESTAMP_NOT_EQ_MZ_TIMESTAMP_OID;
5068            params!(RangeAny, RangeAny) => BF::from(func::NotEq) => Bool, 3883;
5069            params!(MzAclItem, MzAclItem) => BF::from(func::NotEq) => Bool, oid::FUNC_MZ_ACL_ITEM_NOT_EQ_MZ_ACL_ITEM_OID;
5070        }
5071    }
5072});
5073
5074/// Resolves the operator to a set of function implementations.
5075pub fn resolve_op(op: &str) -> Result<&'static [FuncImpl<HirScalarExpr>], PlanError> {
5076    match OP_IMPLS.get(op) {
5077        Some(Func::Scalar(impls)) => Ok(impls),
5078        Some(_) => unreachable!("all operators must be scalar functions"),
5079        // TODO: these require sql arrays
5080        // JsonContainsAnyFields
5081        // JsonContainsAllFields
5082        // TODO: these require json paths
5083        // JsonGetPath
5084        // JsonGetPathAsText
5085        // JsonDeletePath
5086        // JsonContainsPath
5087        // JsonApplyPathPredicate
5088        None => bail_unsupported!(format!("[{}]", op)),
5089    }
5090}
5091
5092// Since ViewableVariables is unmaterializeable (which can't be eval'd) that
5093// depend on their arguments, implement directly with Hir.
5094fn current_settings(
5095    name: HirScalarExpr,
5096    missing_ok: HirScalarExpr,
5097) -> Result<HirScalarExpr, PlanError> {
5098    // MapGetValue returns Null if the key doesn't exist in the map.
5099    let expr = HirScalarExpr::call_binary(
5100        HirScalarExpr::call_unmaterializable(UnmaterializableFunc::ViewableVariables),
5101        HirScalarExpr::call_unary(name, UnaryFunc::Lower(func::Lower)),
5102        func::MapGetValue,
5103    );
5104    let expr = HirScalarExpr::if_then_else(
5105        missing_ok,
5106        expr.clone(),
5107        HirScalarExpr::call_variadic(
5108            VariadicFunc::ErrorIfNull,
5109            vec![
5110                expr,
5111                HirScalarExpr::literal(
5112                    Datum::String("unrecognized configuration parameter"),
5113                    SqlScalarType::String,
5114                ),
5115            ],
5116        ),
5117    );
5118    Ok(expr)
5119}