uqa_sql/routines/

resolution.rs

//
// Unified Query Algebra
//
// Copyright (c) 2023-2026 Cognica, Inc.
//

//! Static routine signature matching, invocation binding, and declared return-type resolution.

mod combined_overloads;

use super::{
    declaration::RoutineTypeCatalog, routine_signature_types, SQLUserFunction, StaticFunctionMatch,
};
use crate::type_resolution::{
    canonical_routine_type_name, match_routine_signature, rank_function_matches,
    BuiltinFunctionOverload, FunctionTypeResolver, MatchedRoutineSignature,
    ResolvedFunctionOverload, RoutineCallDescriptor, RoutineParameterDescriptor,
    RoutineSignatureMatchError,
};
use crate::{
    ast::{
        ColumnType, CreateFunction, FunctionBinding, FunctionParamMode, FunctionReturns,
        RoutineInvocationBinding, RoutineVariadicMode,
    },
    catalog::domain::StoredDomain,
    SQLError,
};
use std::{collections::BTreeMap, sync::Arc};
use uqa_core::RelationIdentity;

/// The original immutable domain allocation captured for one signature-matching pass.
pub type RoutineTypeSnapshot = Arc<BTreeMap<String, StoredDomain>>;
pub trait RoutineOverloadCatalog: RoutineTypeCatalog + Send + Sync {
    fn routine_type_snapshot(&self) -> RoutineTypeSnapshot;
    fn routine_search_path(&self) -> Vec<String>;
    fn has_registered_scalar_function(&self, name: &str) -> bool;
    fn lookup_sql_routine_candidates(
        &self,
        name: &str,
    ) -> Result<Option<Vec<Arc<SQLUserFunction>>>, SQLError>;
    fn lookup_bound_sql_routine_candidates_by_binding(
        &self,
        binding: &FunctionBinding,
    ) -> Option<Vec<Arc<SQLUserFunction>>>;
    fn lookup_bound_sql_functions_by_binding(
        &self,
        binding: &FunctionBinding,
    ) -> Option<Vec<Arc<SQLUserFunction>>>;
}
pub struct RoutineOverloadContext<'a> {
    pub catalog: &'a dyn RoutineOverloadCatalog,
}

impl FunctionTypeResolver for RoutineOverloadContext<'_> {
    fn has_untyped_function(&self, name: &str) -> bool {
        self.catalog.has_registered_scalar_function(name)
    }

    fn resolve_type_name(&self, name: &str) -> Result<Option<ColumnType>, SQLError> {
        self.catalog
            .resolve_catalog_column_type_name(name)
            .map(Some)
    }

    fn resolve_function_type(
        &self,
        name: &str,
        binding: Option<&FunctionBinding>,
        argument_names: &[Option<String>],
        argument_types: &[Option<ColumnType>],
        explicit_variadic: bool,
    ) -> Result<Option<ColumnType>, SQLError> {
        self.resolve_function_overload(
            name,
            binding,
            argument_names,
            argument_types,
            explicit_variadic,
        )
        .map(|resolved| resolved.map(|resolved| resolved.return_type))
    }

    fn resolve_function_overload(
        &self,
        name: &str,
        binding: Option<&FunctionBinding>,
        argument_names: &[Option<String>],
        argument_types: &[Option<ColumnType>],
        explicit_variadic: bool,
    ) -> Result<Option<ResolvedFunctionOverload>, SQLError> {
        let Some(matched) = self.resolve_static_sql_routine_match(
            name,
            binding,
            argument_names,
            argument_types,
            explicit_variadic,
            RoutineCallKind::Function,
        )?
        else {
            return Ok(None);
        };
        let function = &matched.function;
        Ok(Some(ResolvedFunctionOverload {
            binding: matched.binding(),
            return_type: static_function_return_type(
                self,
                name,
                &function.def,
                Some(&matched.invocation),
            )?,
            exact_matches: matched.exact_matches,
            known_arguments: argument_types.iter().flatten().count(),
            preferred_matches: matched.preferred_matches,
            precedes_pg_catalog: self.user_function_precedes_pg_catalog(&function.def.name),
        }))
    }

    fn is_scalar_function_binding(&self, binding: &FunctionBinding) -> Result<bool, SQLError> {
        if binding.builtin {
            return Ok(false);
        }
        let function = self
            .catalog
            .lookup_bound_sql_functions_by_binding(binding)
            .and_then(|overloads| {
                overloads.into_iter().find(|function| {
                    !function.def.is_procedure
                        && routine_signature_types(&function.def) == binding.argument_types
                })
            });
        Ok(function.is_some_and(|function| !function.def.returns_set()))
    }

    fn resolve_function_overload_with_builtins(
        &self,
        name: &str,
        binding: Option<&FunctionBinding>,
        argument_names: &[Option<String>],
        argument_types: &[Option<ColumnType>],
        explicit_variadic: bool,
        builtins: &[BuiltinFunctionOverload],
    ) -> Result<Option<ResolvedFunctionOverload>, SQLError> {
        combined_overloads::resolve(
            self,
            name,
            binding,
            argument_names,
            argument_types,
            explicit_variadic,
            builtins,
        )
        .map(Some)
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum RoutineCallKind {
    Function,
    Procedure,
}

impl RoutineCallKind {
    fn is_procedure(self) -> bool {
        self == Self::Procedure
    }

    fn name(self) -> &'static str {
        match self {
            Self::Function => "function",
            Self::Procedure => "procedure",
        }
    }
}

impl RoutineOverloadContext<'_> {
    pub(super) fn user_function_precedes_pg_catalog(&self, name: &str) -> bool {
        let Ok((Some(schema), _)) = RelationIdentity::parse_reference(name) else {
            return false;
        };
        let search_path = self.catalog.routine_search_path();
        let Some(user_position) = search_path.iter().position(|entry| entry == &schema) else {
            return false;
        };
        search_path
            .iter()
            .position(|entry| entry == "pg_catalog")
            .is_some_and(|catalog_position| user_position < catalog_position)
    }

    pub fn resolve_static_sql_function(
        &self,
        name: &str,
        binding: Option<&FunctionBinding>,
        argument_names: &[Option<String>],
        argument_types: &[Option<ColumnType>],
        explicit_variadic: bool,
    ) -> Result<Option<Arc<SQLUserFunction>>, SQLError> {
        self.resolve_static_sql_function_match(
            name,
            binding,
            argument_names,
            argument_types,
            explicit_variadic,
        )
        .map(|matched| matched.map(|matched| matched.function))
    }

    pub fn resolve_static_sql_function_match(
        &self,
        name: &str,
        binding: Option<&FunctionBinding>,
        argument_names: &[Option<String>],
        argument_types: &[Option<ColumnType>],
        explicit_variadic: bool,
    ) -> Result<Option<StaticFunctionMatch>, SQLError> {
        self.resolve_static_sql_routine_match(
            name,
            binding,
            argument_names,
            argument_types,
            explicit_variadic,
            RoutineCallKind::Function,
        )
    }

    pub fn resolve_table_function_overload_with_builtins(
        &self,
        name: &str,
        binding: Option<&FunctionBinding>,
        argument_names: &[Option<String>],
        argument_types: &[Option<ColumnType>],
        explicit_variadic: bool,
        builtins: &[BuiltinFunctionOverload],
    ) -> Result<Option<ResolvedFunctionOverload>, SQLError> {
        combined_overloads::resolve_table(
            self,
            name,
            binding,
            argument_names,
            argument_types,
            explicit_variadic,
            builtins,
        )
        .map(Some)
    }

    pub fn resolve_static_sql_routine_match(
        &self,
        name: &str,
        binding: Option<&FunctionBinding>,
        argument_names: &[Option<String>],
        argument_types: &[Option<ColumnType>],
        explicit_variadic: bool,
        kind: RoutineCallKind,
    ) -> Result<Option<StaticFunctionMatch>, SQLError> {
        if let Some(binding) = binding {
            if binding.builtin {
                return Ok(None);
            }
            let function = self
                .catalog
                .lookup_bound_sql_routine_candidates_by_binding(binding)
                .and_then(|overloads| {
                    overloads.into_iter().find(|function| {
                        function.def.is_procedure == kind.is_procedure()
                            && routine_signature_types(&function.def) == binding.argument_types
                    })
                })
                .ok_or_else(|| static_bound_routine_error(kind, binding))?;
            let matched = if let Some(invocation) = &binding.invocation {
                let invocation_is_explicit = matches!(
                    invocation.variadic_mode,
                    RoutineVariadicMode::Explicit { .. }
                );
                if invocation.argument_positions.len() != argument_types.len()
                    || invocation_is_explicit != explicit_variadic
                {
                    return Err(static_bound_routine_error(kind, binding));
                }
                StaticFunctionMatch {
                    function,
                    invocation: invocation.clone(),
                    argument_types: invocation.argument_targets.clone(),
                    raw_exact_matches: 0,
                    exact_matches: 0,
                    preferred_matches: 0,
                    variadic_expansion: matches!(
                        invocation.variadic_mode,
                        RoutineVariadicMode::Expanded { .. }
                    ),
                }
            } else {
                static_routine_match(
                    &self.catalog.routine_type_snapshot(),
                    function,
                    argument_names,
                    argument_types,
                    explicit_variadic,
                    kind,
                )
                .map_err(|error| static_signature_error(kind, name, error))?
                .ok_or_else(|| static_bound_routine_error(kind, binding))?
            };
            ensure_routine_kind(
                name,
                argument_names,
                argument_types,
                kind,
                &matched.function.def,
            )?;
            return Ok(Some(matched));
        }
        let Some(overloads) = self.catalog.lookup_sql_routine_candidates(name)? else {
            return Ok(None);
        };
        resolve_static_routine_overload(
            &self.catalog.routine_type_snapshot(),
            name,
            overloads,
            argument_names,
            argument_types,
            explicit_variadic,
            kind,
        )
        .map(Some)
    }
}

fn resolve_static_routine_overload(
    catalog: &RoutineTypeSnapshot,
    name: &str,
    overloads: Vec<Arc<SQLUserFunction>>,
    argument_names: &[Option<String>],
    argument_types: &[Option<ColumnType>],
    explicit_variadic: bool,
    kind: RoutineCallKind,
) -> Result<StaticFunctionMatch, SQLError> {
    let mut candidates = Vec::new();
    let mut match_error = None;
    for function in overloads {
        match static_routine_match(
            catalog,
            function,
            argument_names,
            argument_types,
            explicit_variadic,
            kind,
        ) {
            Ok(Some(candidate)) => candidates.push(candidate),
            Ok(None) => {}
            Err(error) => {
                match_error.get_or_insert(error);
            }
        }
    }
    retain_earliest_effective_signatures(&mut candidates);
    if candidates.is_empty() {
        if let Some(error) = match_error {
            return Err(static_signature_error(kind, name, error));
        }
        return Err(static_routine_resolution_error(
            kind,
            "42883",
            name,
            argument_types,
            "does not exist",
        ));
    }

    if !rank_function_matches(&mut candidates, argument_types) || candidates.len() != 1 {
        return Err(static_routine_resolution_error(
            kind,
            "42725",
            name,
            argument_types,
            "is not unique",
        ));
    }
    let matched = candidates
        .pop()
        .ok_or_else(|| SQLError::Internal("resolved routine candidate disappeared".into()))?;
    ensure_routine_kind(
        name,
        argument_names,
        argument_types,
        kind,
        &matched.function.def,
    )?;
    Ok(matched)
}

pub(super) fn retain_earliest_effective_signatures(candidates: &mut Vec<StaticFunctionMatch>) {
    let mut visible = Vec::<(Vec<String>, String)>::new();
    candidates.retain(|candidate| {
        let schema = RelationIdentity::parse_reference(&candidate.function.def.name)
            .ok()
            .and_then(|(schema, _)| schema)
            .unwrap_or_default();
        if let Some((_, first_schema)) = visible
            .iter()
            .find(|(signature, _)| signature == &candidate.argument_types)
        {
            return first_schema == &schema;
        }
        visible.push((candidate.argument_types.clone(), schema));
        true
    });
}

fn static_routine_match(
    catalog: &RoutineTypeSnapshot,
    function: Arc<SQLUserFunction>,
    argument_names: &[Option<String>],
    argument_types: &[Option<ColumnType>],
    explicit_variadic: bool,
    kind: RoutineCallKind,
) -> Result<Option<StaticFunctionMatch>, RoutineSignatureMatchError> {
    let parameter_indices = routine_call_parameter_indices(&function.def, kind);
    let parameters = parameter_indices
        .iter()
        .map(|index| &function.def.params[*index])
        .collect::<Vec<_>>();
    let Some(matched) = match_static_function_signature(
        catalog,
        &parameters,
        argument_names,
        argument_types,
        explicit_variadic,
    )?
    else {
        return Ok(None);
    };
    let invocation = routine_invocation_binding(&function.def, &parameter_indices, &matched);
    Ok(Some(StaticFunctionMatch {
        function,
        argument_types: matched.argument_targets,
        raw_exact_matches: matched.raw_exact_matches,
        exact_matches: matched.exact_matches,
        preferred_matches: matched.preferred_matches,
        variadic_expansion: matches!(
            matched.variadic_mode,
            crate::type_resolution::RoutineVariadicMode::Pack
        ),
        invocation: Box::new(invocation),
    }))
}

pub(super) fn static_function_match(
    catalog: &RoutineTypeSnapshot,
    function: Arc<SQLUserFunction>,
    argument_names: &[Option<String>],
    argument_types: &[Option<ColumnType>],
    explicit_variadic: bool,
) -> Result<Option<StaticFunctionMatch>, RoutineSignatureMatchError> {
    static_routine_match(
        catalog,
        function,
        argument_names,
        argument_types,
        explicit_variadic,
        RoutineCallKind::Function,
    )
}

fn match_static_function_signature(
    catalog: &RoutineTypeSnapshot,
    signature: &[&crate::ast::FunctionParam],
    argument_names: &[Option<String>],
    argument_types: &[Option<ColumnType>],
    explicit_variadic: bool,
) -> Result<Option<MatchedRoutineSignature>, RoutineSignatureMatchError> {
    let parameters = signature
        .iter()
        .map(|parameter| RoutineParameterDescriptor {
            name: Some(parameter.name.clone()),
            type_name: canonical_routine_type_name(&parameter.type_name),
            column_type: declared_parameter_type(catalog, &parameter.type_name),
            has_default: parameter.default.is_some(),
            variadic: parameter.mode == FunctionParamMode::Variadic,
        })
        .collect::<Vec<_>>();
    match_routine_signature(
        &parameters,
        RoutineCallDescriptor {
            argument_names,
            argument_types,
            explicit_variadic,
        },
    )
}

fn declared_parameter_type(catalog: &RoutineTypeSnapshot, type_name: &str) -> Option<ColumnType> {
    if let Some(element) = type_name.strip_suffix("[]") {
        return declared_parameter_type(catalog, element).map(|ty| ColumnType::Array(Box::new(ty)));
    }
    ColumnType::from_sql_name(type_name).ok().or_else(|| {
        catalog.values().map(StoredDomain::column_type).find(|ty| {
            canonical_routine_type_name(&ty.sql_name()) == canonical_routine_type_name(type_name)
        })
    })
}

fn routine_call_parameter_indices(def: &CreateFunction, kind: RoutineCallKind) -> Vec<usize> {
    def.params
        .iter()
        .enumerate()
        .filter_map(|(index, parameter)| {
            let participates = if kind == RoutineCallKind::Procedure && !def.is_procedure {
                true
            } else {
                match parameter.mode {
                    FunctionParamMode::In
                    | FunctionParamMode::InOut
                    | FunctionParamMode::Variadic => true,
                    FunctionParamMode::Out => def.is_procedure,
                    FunctionParamMode::Table => false,
                }
            };
            participates.then_some(index)
        })
        .collect()
}

fn routine_invocation_binding(
    def: &CreateFunction,
    parameter_indices: &[usize],
    matched: &MatchedRoutineSignature,
) -> RoutineInvocationBinding {
    let parameter_types = def
        .params
        .iter()
        .enumerate()
        .map(|(definition_index, parameter)| {
            parameter_indices
                .iter()
                .position(|index| *index == definition_index)
                .and_then(|call_index| matched.parameter_types.get(call_index).cloned())
                .or_else(|| matched.substitute_type_name(&parameter.type_name))
                .unwrap_or_else(|| canonical_routine_type_name(&parameter.type_name))
        })
        .collect::<Vec<_>>();
    let argument_positions = matched
        .argument_positions
        .iter()
        .map(|call_index| parameter_indices[*call_index])
        .collect();
    let variadic_mode = match &matched.variadic_plan {
        crate::type_resolution::RoutineVariadicPlan::Pack {
            parameter_index, ..
        } => RoutineVariadicMode::Expanded {
            parameter_index: parameter_indices[*parameter_index],
        },
        crate::type_resolution::RoutineVariadicPlan::PassThrough {
            parameter_index, ..
        } => RoutineVariadicMode::Explicit {
            parameter_index: parameter_indices[*parameter_index],
        },
        crate::type_resolution::RoutineVariadicPlan::None
        | crate::type_resolution::RoutineVariadicPlan::Default { .. } => RoutineVariadicMode::None,
    };
    let output_indices = def
        .params
        .iter()
        .enumerate()
        .filter_map(|(index, parameter)| {
            matches!(
                parameter.mode,
                FunctionParamMode::Out | FunctionParamMode::InOut | FunctionParamMode::Table
            )
            .then_some(index)
        })
        .collect::<Vec<_>>();
    let return_type = match &def.returns {
        FunctionReturns::Scalar { type_name } | FunctionReturns::SetOf { type_name } => matched
            .substitute_type_name(type_name)
            .or_else(|| Some(canonical_routine_type_name(type_name))),
        FunctionReturns::Table => Some("record".into()),
        FunctionReturns::None => match output_indices.as_slice() {
            [] => None,
            [index] => parameter_types.get(*index).cloned(),
            _ => Some("record".into()),
        },
    };
    RoutineInvocationBinding {
        argument_positions,
        argument_targets: matched.argument_targets.clone(),
        argument_sources: matched.argument_sources.clone(),
        parameter_types,
        return_type,
        variadic_mode,
    }
}

pub(super) fn static_signature_error(
    kind: RoutineCallKind,
    name: &str,
    error: RoutineSignatureMatchError,
) -> SQLError {
    let sqlstate = error.sqlstate().to_string();
    let message = match error {
        RoutineSignatureMatchError::InvalidVariadicSignature { reason } => reason,
        RoutineSignatureMatchError::IndeterminatePolymorphicType { .. } => {
            format!(
                "could not determine polymorphic type for {} `{name}` because an input has type unknown",
                kind.name()
            )
        }
    };
    SQLError::Routine { sqlstate, message }
}

pub(super) fn static_function_return_type(
    resolver: &RoutineOverloadContext<'_>,
    name: &str,
    def: &CreateFunction,
    invocation: Option<&RoutineInvocationBinding>,
) -> Result<ColumnType, SQLError> {
    let invocation_return = invocation.and_then(|invocation| invocation.return_type.as_deref());
    let declared_return = match &def.returns {
        FunctionReturns::Scalar { type_name } | FunctionReturns::SetOf { type_name } => {
            Some(type_name.as_str())
        }
        FunctionReturns::Table | FunctionReturns::None => None,
    };
    if invocation_return
        .or(declared_return)
        .is_some_and(|type_name| canonical_routine_type_name(type_name) == "trigger")
    {
        return Err(SQLError::Routine {
            sqlstate: "0A000".into(),
            message: "trigger functions can only be called as triggers".into(),
        });
    }
    if matches!(def.returns, FunctionReturns::Table) || def.output_params().len() > 1 {
        return Ok(ColumnType::Record);
    }
    if let Some(type_name) = invocation_return {
        return resolver
            .catalog
            .resolve_catalog_column_type(type_name)
            .or_else(|| ColumnType::from_sql_name(type_name).ok())
            .ok_or_else(|| {
                SQLError::TypeMismatch(format!(
                    "function `{name}` has unresolved return type `{type_name}`"
                ))
            });
    }
    let type_name = match &def.returns {
        FunctionReturns::Scalar { type_name } | FunctionReturns::SetOf { type_name } => type_name,
        FunctionReturns::None => {
            let outputs = def.output_params();
            if outputs.len() > 1 {
                return Ok(ColumnType::Record);
            }
            &outputs
                .first()
                .ok_or_else(|| {
                    SQLError::TypeMismatch(format!("function `{name}` does not return a value"))
                })?
                .type_name
        }
        FunctionReturns::Table => unreachable!("table result handled above"),
    };
    resolver
        .catalog
        .resolve_catalog_column_type(type_name)
        .or_else(|| ColumnType::from_sql_name(type_name).ok())
        .ok_or_else(|| SQLError::TypeMismatch(format!("unknown type `{type_name}`")))
}

fn ensure_routine_kind(
    name: &str,
    argument_names: &[Option<String>],
    argument_types: &[Option<ColumnType>],
    expected: RoutineCallKind,
    definition: &CreateFunction,
) -> Result<(), SQLError> {
    if definition.is_procedure == expected.is_procedure() {
        return Ok(());
    }
    let arguments = static_routine_argument_types(argument_names, argument_types);
    let suffix = if definition.is_procedure {
        "is a procedure"
    } else {
        "is not a procedure"
    };
    Err(SQLError::Diagnostic {
        sqlstate: "42809".into(),
        message: format!("{name}({arguments}) {suffix}"),
        detail: None,
        hint: Some(if definition.is_procedure {
            "To call a procedure, use CALL.".into()
        } else {
            "To call a function, use SELECT.".into()
        }),
    })
}

fn static_bound_routine_error(kind: RoutineCallKind, binding: &FunctionBinding) -> SQLError {
    SQLError::Routine {
        sqlstate: "42883".into(),
        message: format!(
            "bound {} {}({}) does not exist",
            kind.name(),
            binding.name,
            binding.argument_types.join(", ")
        ),
    }
}

fn static_routine_resolution_error(
    kind: RoutineCallKind,
    sqlstate: &str,
    name: &str,
    argument_types: &[Option<ColumnType>],
    suffix: &str,
) -> SQLError {
    let arguments = static_routine_argument_types(&[], argument_types);
    SQLError::Routine {
        sqlstate: sqlstate.into(),
        message: format!("{} {name}({arguments}) {suffix}", kind.name()),
    }
}

fn static_routine_argument_types(
    argument_names: &[Option<String>],
    argument_types: &[Option<ColumnType>],
) -> String {
    argument_types
        .iter()
        .enumerate()
        .map(|(index, ty)| {
            let ty = ty
                .as_ref()
                .map_or_else(|| "unknown".into(), ColumnType::regtype_name);
            argument_names
                .get(index)
                .and_then(Option::as_ref)
                .map_or_else(|| ty.clone(), |name| format!("{name} => {ty}"))
        })
        .collect::<Vec<_>>()
        .join(", ")
}