Skip to main content

uqa_sql/
routines.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! SQL routine definitions and static signature lookup contracts.
8
9pub mod attributes;
10pub mod body_parameters;
11pub mod body_validation;
12pub mod compilation;
13pub mod configuration;
14pub mod declaration;
15mod defaults;
16pub mod definition_output;
17pub mod dependencies;
18pub mod diagnostics;
19pub mod inlining;
20pub mod lifecycle;
21pub mod merge_columns;
22pub mod privilege_inquiry;
23pub mod regclass;
24pub mod registration;
25pub mod resolution;
26pub mod result_check;
27pub mod security;
28
29use crate::ast::{
30    ColumnType, CreateFunction, FunctionBinding, FunctionReturns, RoutineInvocationBinding,
31};
32use crate::plan::UnifiedPlan;
33use crate::type_resolution::{
34    canonical_routine_type_name, BuiltinFunctionOverload, FunctionTypeResolver,
35    RankedFunctionMatch, ResolvedFunctionOverload,
36};
37use crate::SQLError;
38use std::sync::{Arc, OnceLock};
39
40/// A registered routine: the persistable definition and its body as the catalog keeps it.
41#[derive(Clone)]
42pub struct SQLUserFunction {
43    pub def: CreateFunction,
44    pub body: RoutineBody,
45    version: OnceLock<u64>,
46}
47
48impl SQLUserFunction {
49    #[must_use]
50    pub const fn new(def: CreateFunction, body: RoutineBody) -> Self {
51        Self {
52            def,
53            body,
54            version: OnceLock::new(),
55        }
56    }
57
58    /// The identity of this catalog tuple, preserved through reload and rollback. A legacy definition starts from its immutable object identity; publication assigns a new revision even when it keeps every SQL attribute unchanged.
59    #[must_use]
60    pub fn catalog_revision(&self) -> Option<[u8; 16]> {
61        self.def.catalog_revision.or(self.def.object_id)
62    }
63
64    /// A fingerprint for the compiled-body cache, including the published catalog revision so an identical replacement recompiles its body. Exact catalog dependency checks use `catalog_revision` instead of this hash.
65    pub fn definition_version(&self) -> Result<u64, SQLError> {
66        if let Some(version) = self.version.get() {
67            return Ok(*version);
68        }
69        let encoded = serde_json::to_vec(&self.def).map_err(|error| {
70            SQLError::Internal(format!(
71                "encode routine `{}` definition: {error}",
72                self.def.name
73            ))
74        })?;
75        // FNV-1a over the encoded definition.
76        let version = encoded
77            .iter()
78            .fold(0xcbf2_9ce4_8422_2325_u64, |hash, byte| {
79                (hash ^ u64::from(*byte)).wrapping_mul(0x0000_0100_0000_01b3)
80            });
81        Ok(*self.version.get_or_init(|| version))
82    }
83}
84
85/// A routine body as the catalog keeps it. A SQL-standard body is bound when the routine is defined, as `PostgreSQL` stores `prosqlbody` as parse trees that name objects by OID. A body given as a string is compiled by each session that uses the routine, as the backend function cache compiles `prosrc`, so its names resolve when that session first needs them.
86/// A routine body as the catalog keeps it.
87#[derive(Clone)]
88pub enum RoutineBody {
89    /// A SQL-standard body, which the statement that defines the routine analyzes and binds.
90    Bound(Arc<CompiledFunctionBody>),
91    /// A body given as a string, which the catalog keeps as text, as `PostgreSQL` keeps `prosrc`: `CREATE FUNCTION` validates it only under `check_function_bodies`, and each session compiles it when it first calls the routine, so a body that no longer compiles reports its error then.
92    Source,
93}
94
95/// The body a static analysis sees, or `None` when the body does not compile in this session. Such a routine cannot run in the session, and running it reports the compilation error, as `PostgreSQL` compiles a routine only when it runs; internal failures still propagate.
96pub fn analyzable_routine_body(
97    resolution: &(impl RoutineResolution + ?Sized),
98    function: &SQLUserFunction,
99) -> Result<Option<Arc<CompiledFunctionBody>>, SQLError> {
100    match resolution.routine_analysis_body(function) {
101        Ok(body) => Ok(Some(body)),
102        Err(error) if matches!(error.primary(), SQLError::Internal(_)) => Err(error),
103        Err(_) => Ok(None),
104    }
105}
106
107/// Executable form of a routine body.
108// The project naming convention spells the acronym as `SQL`.
109#[allow(clippy::upper_case_acronyms)]
110#[derive(Clone)]
111pub enum CompiledFunctionBody {
112    PLpgSQL(crate::plpgsql::PLpgSQLFunction),
113    SQL(Vec<UnifiedPlan>),
114}
115pub fn is_routine_namespace_lookup_error(error: &SQLError) -> bool {
116    matches!(
117        error,
118        SQLError::Routine { sqlstate, message }
119            if sqlstate == "3F000"
120                || (sqlstate == "42501"
121                    && message.starts_with("permission denied for schema "))
122    )
123}
124
125/// Function-catalog operations required by static query binding. The interface deliberately excludes storage, transaction, locking, and execution services so the binder can run against a deterministic catalog fixture.
126#[derive(Clone, Copy, Default)]
127pub struct VisibleFunctionSetness {
128    pub found: bool,
129    /// `None` means either no scalar declaration or differing set-returning declarations.
130    pub uniform: Option<bool>,
131}
132
133pub trait RoutineResolution: FunctionTypeResolver {
134    fn has_registered_scalar_function(&self, _name: &str) -> bool {
135        false
136    }
137
138    /// Native registries admit normalized lookup keys before allocation; external catalogs retain their existing scalar callback.
139    fn has_registered_scalar_function_with_control(
140        &self,
141        name: &str,
142        control: &uqa_core::memory::ProductionControl<'_>,
143    ) -> Result<bool, SQLError> {
144        control.check()?;
145        let registered = self.has_registered_scalar_function(name);
146        control.check()?;
147        Ok(registered)
148    }
149
150    /// The compiled body of a routine as this session executes it: a bound body as defined, or a source body as this session compiled it. A resolver without a session holds only bound bodies.
151    fn routine_body(
152        &self,
153        function: &SQLUserFunction,
154    ) -> Result<Arc<CompiledFunctionBody>, SQLError> {
155        match &function.body {
156            RoutineBody::Bound(body) => Ok(Arc::clone(body)),
157            RoutineBody::Source => Err(SQLError::Internal(format!(
158                "routine `{}` has no session to compile its body",
159                function.def.name
160            ))),
161        }
162    }
163
164    /// Inspect a body for static effects and portal dependencies. A session may
165    /// compile source independently here without creating its execution cache
166    /// entry or issuing diagnostics for a body the query never reaches.
167    fn routine_analysis_body(
168        &self,
169        function: &SQLUserFunction,
170    ) -> Result<Arc<CompiledFunctionBody>, SQLError> {
171        self.routine_body(function)
172    }
173
174    fn has_registered_table_function(&self, _name: &str) -> bool {
175        false
176    }
177
178    /// Native registries admit normalized lookup keys before allocation; external catalogs retain their established boolean callback.
179    fn has_registered_table_function_with_control(
180        &self,
181        name: &str,
182        control: &uqa_core::memory::ProductionControl<'_>,
183    ) -> Result<bool, SQLError> {
184        control.check()?;
185        let registered = self.has_registered_table_function(name);
186        control.check()?;
187        Ok(registered)
188    }
189
190    /// A registered function's stable output names, available without calling
191    /// it. `None` means its result supplies the descriptor at execution.
192    fn registered_table_function_columns(&self, _name: &str) -> Option<Vec<String>> {
193        None
194    }
195
196    /// Native registries copy borrowed declarations under the request allowance; external catalogs retain their metadata callback.
197    fn registered_table_function_columns_with_control(
198        &self,
199        name: &str,
200        control: &uqa_core::memory::ProductionControl<'_>,
201    ) -> Result<Option<uqa_core::memory::Produced<Vec<String>>>, SQLError> {
202        control.check()?;
203        self.registered_table_function_columns(name)
204            .map(|columns| {
205                let mut output = uqa_core::memory::ProductionVec::new(*control);
206                for column in columns {
207                    output.push_produced(control.copy_text(&column)?)?;
208                }
209                Ok(output.finish()?)
210            })
211            .transpose()
212    }
213
214    fn has_registered_aggregate_function(&self, _name: &str) -> bool {
215        false
216    }
217
218    fn has_registered_aggregate_function_with_control(
219        &self,
220        name: &str,
221        control: &uqa_core::memory::ProductionControl<'_>,
222    ) -> Result<bool, SQLError> {
223        control.check()?;
224        let registered = self.has_registered_aggregate_function(name);
225        control.check()?;
226        Ok(registered)
227    }
228
229    fn lookup_visible_sql_functions(
230        &self,
231        _name: &str,
232    ) -> Result<Option<Vec<Arc<SQLUserFunction>>>, SQLError> {
233        Ok(None)
234    }
235
236    /// Native catalogs inspect immutable buckets; external catalogs preserve their visible lookup.
237    fn has_visible_sql_functions_with_control(
238        &self,
239        name: &str,
240        control: &uqa_core::memory::ProductionControl<'_>,
241    ) -> Result<bool, SQLError> {
242        control.check()?;
243        let found = self.lookup_visible_sql_functions(name)?.is_some();
244        control.check()?;
245        Ok(found)
246    }
247
248    /// Resolve candidate metadata without refreshing session-visible function state during analysis.
249    fn lookup_visible_sql_functions_for_analysis(
250        &self,
251        name: &str,
252    ) -> Result<Option<Vec<Arc<SQLUserFunction>>>, SQLError> {
253        self.lookup_visible_sql_functions(name)
254    }
255
256    /// Observe the same visible, signature-shadowed candidates used by ordinary analysis without requiring a copied native candidate list.
257    fn visible_function_setness_with_control(
258        &self,
259        name: &str,
260        control: &uqa_core::memory::ProductionControl<'_>,
261    ) -> Result<VisibleFunctionSetness, SQLError> {
262        control.check()?;
263        let candidates = self.lookup_visible_sql_functions_for_analysis(name)?;
264        let mut result = VisibleFunctionSetness {
265            found: candidates.is_some(),
266            uniform: None,
267        };
268        let mut first = None;
269        let mut mixed = false;
270        for function in candidates
271            .iter()
272            .flatten()
273            .filter(|function| !function.def.is_procedure)
274        {
275            control.check()?;
276            let set = function.def.returns_set();
277            match first {
278                None => first = Some(set),
279                Some(first) => mixed |= first != set,
280            }
281        }
282        result.uniform = first.filter(|_| !mixed);
283        Ok(result)
284    }
285
286    /// Observe visible set-returning declarations without constructing candidate snapshots in native catalogs. External adapters retain their existing analysis lookup.
287    fn visible_function_returns_set_with_control(
288        &self,
289        name: &str,
290        control: &uqa_core::memory::ProductionControl<'_>,
291    ) -> Result<bool, SQLError> {
292        control.check()?;
293        let candidates = self.lookup_visible_sql_functions_for_analysis(name)?;
294        let mut returns_set = false;
295        for function in candidates.iter().flatten() {
296            control.check()?;
297            if !function.def.is_procedure && function.def.returns_set() {
298                returns_set = true;
299                break;
300            }
301        }
302        Ok(returns_set)
303    }
304
305    /// Resolve an exact stored routine name without repeating visible-name lookup.
306    fn lookup_bound_sql_functions(&self, _name: &str) -> Option<Vec<Arc<SQLUserFunction>>> {
307        None
308    }
309
310    fn lookup_bound_sql_functions_by_binding(
311        &self,
312        _binding: &FunctionBinding,
313    ) -> Option<Vec<Arc<SQLUserFunction>>> {
314        None
315    }
316
317    /// Select an already bound scalar routine. Native registries borrow their immutable bucket; custom catalogs may supply their existing candidate snapshot.
318    fn select_bound_function_with_control(
319        &self,
320        binding: &FunctionBinding,
321        control: &uqa_core::memory::ProductionControl<'_>,
322    ) -> Result<Option<Arc<SQLUserFunction>>, SQLError> {
323        control.check()?;
324        let candidates = self.lookup_bound_sql_functions_by_binding(binding);
325        lifecycle::lookup::select_bound_function_with_control(
326            candidates.iter().flatten(),
327            binding,
328            control,
329        )
330    }
331
332    fn resolve_static_sql_function(
333        &self,
334        _name: &str,
335        _binding: Option<&FunctionBinding>,
336        _argument_names: &[Option<String>],
337        _argument_types: &[Option<ColumnType>],
338        _explicit_variadic: bool,
339    ) -> Result<Option<Arc<SQLUserFunction>>, SQLError> {
340        Ok(None)
341    }
342
343    /// Observe strictness after the same static function selection; native overload workspace retains the invoking allowance.
344    fn resolve_static_sql_function_strictness_with_control(
345        &self,
346        name: &str,
347        binding: Option<&FunctionBinding>,
348        call: crate::type_resolution::RoutineCallDescriptor<'_>,
349        control: &uqa_core::memory::ProductionControl<'_>,
350    ) -> Result<Option<bool>, SQLError> {
351        control.check()?;
352        let selected = self.resolve_static_sql_function(
353            name,
354            binding,
355            call.argument_names,
356            call.argument_types,
357            call.explicit_variadic,
358        )?;
359        control.check()?;
360        Ok(selected.map(|function| function.def.strict))
361    }
362
363    /// Select the same routine as static expression analysis and observe only its set-returning declaration. Native adapters retain admitted overload workspace; external adapters keep the existing selection callback.
364    fn resolve_static_sql_function_setness_with_control(
365        &self,
366        name: &str,
367        binding: Option<&FunctionBinding>,
368        argument_names: &[Option<String>],
369        argument_types: &[Option<ColumnType>],
370        explicit_variadic: bool,
371        control: &uqa_core::memory::ProductionControl<'_>,
372    ) -> Result<Option<bool>, SQLError> {
373        control.check()?;
374        let result = self.resolve_static_sql_function(
375            name,
376            binding,
377            argument_names,
378            argument_types,
379            explicit_variadic,
380        )?;
381        control.check()?;
382        Ok(result.map(|function| function.def.returns_set()))
383    }
384
385    fn resolve_static_sql_function_match(
386        &self,
387        _name: &str,
388        _binding: Option<&FunctionBinding>,
389        _argument_names: &[Option<String>],
390        _argument_types: &[Option<ColumnType>],
391        _explicit_variadic: bool,
392    ) -> Result<Option<StaticFunctionMatch>, SQLError> {
393        Ok(None)
394    }
395
396    /// Keep the selected invocation owned while callers derive table-result metadata.
397    fn resolve_static_sql_function_match_with_control(
398        &self,
399        name: &str,
400        binding: Option<&FunctionBinding>,
401        call: crate::type_resolution::RoutineCallDescriptor<'_>,
402        control: &uqa_core::memory::ProductionControl<'_>,
403    ) -> Result<Option<resolution::OwnedStaticFunctionMatch>, SQLError> {
404        control.check()?;
405        self.resolve_static_sql_function_match(
406            name,
407            binding,
408            call.argument_names,
409            call.argument_types,
410            call.explicit_variadic,
411        )?
412        .map(|matched| {
413            Ok(resolution::OwnedStaticFunctionMatch {
414                function: matched.function,
415                invocation: crate::plan::copy::copy_syntax_with_control(
416                    matched.invocation.as_ref(),
417                    control,
418                )?,
419            })
420        })
421        .transpose()
422    }
423
424    /// Construct the selected table-call binding without adding scalar output-type validation.
425    fn resolve_static_sql_function_binding_with_control(
426        &self,
427        name: &str,
428        binding: Option<&FunctionBinding>,
429        argument_names: &[Option<String>],
430        argument_types: &[Option<ColumnType>],
431        explicit_variadic: bool,
432        control: &uqa_core::memory::ProductionControl<'_>,
433    ) -> Result<Option<uqa_core::memory::Produced<FunctionBinding>>, SQLError> {
434        control.check()?;
435        self.resolve_static_sql_function_match(
436            name,
437            binding,
438            argument_names,
439            argument_types,
440            explicit_variadic,
441        )?
442        .map(|matched| matched.binding_with_control(control))
443        .transpose()
444    }
445
446    fn resolve_table_function_overload_with_builtins_and_control(
447        &self,
448        name: &str,
449        binding: Option<&FunctionBinding>,
450        call: crate::type_resolution::RoutineCallDescriptor<'_>,
451        builtins: &[BuiltinFunctionOverload],
452        control: &uqa_core::memory::ProductionControl<'_>,
453    ) -> Result<Option<uqa_core::memory::Produced<ResolvedFunctionOverload>>, SQLError> {
454        control.check()?;
455        self.resolve_table_function_overload_with_builtins(
456            name,
457            binding,
458            call.argument_names,
459            call.argument_types,
460            call.explicit_variadic,
461            builtins,
462        )?
463        .map(|selected| selected.clone_with_control(control))
464        .transpose()
465    }
466
467    fn resolve_table_function_overload_with_builtins(
468        &self,
469        _name: &str,
470        _binding: Option<&FunctionBinding>,
471        _argument_names: &[Option<String>],
472        _argument_types: &[Option<ColumnType>],
473        _explicit_variadic: bool,
474        _builtins: &[BuiltinFunctionOverload],
475    ) -> Result<Option<ResolvedFunctionOverload>, SQLError> {
476        Ok(None)
477    }
478}
479
480/// Select the exact retained routine identity and declared signature without rebinding its name or executing its body.
481pub fn selected_bound_function(
482    routines: &dyn RoutineResolution,
483    binding: &FunctionBinding,
484) -> Option<std::sync::Arc<SQLUserFunction>> {
485    selected_bound_function_with_control(
486        routines,
487        binding,
488        &uqa_core::memory::ProductionControl::uncontrolled(),
489    )
490    .expect("ordinary selected routine lookup cannot be cancelled or limited")
491}
492
493pub fn selected_bound_function_with_control(
494    routines: &dyn RoutineResolution,
495    binding: &FunctionBinding,
496    control: &uqa_core::memory::ProductionControl<'_>,
497) -> Result<Option<Arc<SQLUserFunction>>, SQLError> {
498    control.check()?;
499    if binding.builtin || binding.resolution_error.is_some() || binding.object_id.is_none() {
500        return Ok(None);
501    }
502    routines.select_bound_function_with_control(binding, control)
503}
504
505pub(crate) fn routine_signature_matches_with_control(
506    definition: &CreateFunction,
507    types: &[String],
508    control: &uqa_core::memory::ProductionControl<'_>,
509) -> Result<bool, SQLError> {
510    let mut parameters = definition.identity_params();
511    for expected in types {
512        let Some(parameter) = parameters.next() else {
513            return Ok(false);
514        };
515        if *crate::type_resolution::canonical_routine_type_name_with_control(
516            &parameter.type_name,
517            control,
518        )? != *expected
519        {
520            return Ok(false);
521        }
522    }
523    Ok(parameters.next().is_none())
524}
525
526pub fn routine_signature_types(def: &CreateFunction) -> Vec<String> {
527    def.identity_params()
528        .map(|parameter| canonical_routine_type_name(&parameter.type_name))
529        .collect()
530}
531
532pub fn routine_returns_anonymous_record(def: &CreateFunction) -> bool {
533    routine_returns_anonymous_record_with_control(
534        def,
535        &uqa_core::memory::ProductionControl::uncontrolled(),
536    )
537    .expect("ordinary routine return category")
538}
539
540pub struct StaticFunctionMatch {
541    pub function: Arc<SQLUserFunction>,
542    pub invocation: Box<RoutineInvocationBinding>,
543    pub argument_types: Vec<String>,
544    pub raw_exact_matches: usize,
545    pub exact_matches: usize,
546    pub preferred_matches: usize,
547    pub variadic_expansion: bool,
548    pub default_error: Option<crate::type_resolution::RoutineSignatureMatchError>,
549}
550
551impl StaticFunctionMatch {
552    pub fn binding_with_control(
553        &self,
554        control: &uqa_core::memory::ProductionControl<'_>,
555    ) -> Result<uqa_core::memory::Produced<FunctionBinding>, SQLError> {
556        resolution::selected_binding(
557            &self.function,
558            crate::plan::copy::copy_syntax_with_control(self.invocation.as_ref(), control)?,
559            control,
560        )
561    }
562
563    pub fn binding(&self) -> FunctionBinding {
564        FunctionBinding {
565            object_id: self.function.def.object_id,
566            name: self.function.def.name.clone(),
567            argument_types: routine_signature_types(&self.function.def),
568            builtin: false,
569            dispatch: None,
570            invocation: Some(self.invocation.clone()),
571            value_binding: None,
572            resolution_error: None,
573            input_collation: None,
574        }
575    }
576}
577
578impl RankedFunctionMatch for StaticFunctionMatch {
579    fn argument_types(&self) -> &[String] {
580        &self.argument_types
581    }
582
583    fn raw_exact_matches(&self) -> usize {
584        self.raw_exact_matches
585    }
586
587    fn exact_matches(&self) -> usize {
588        self.exact_matches
589    }
590
591    fn preferred_matches(&self) -> usize {
592        self.preferred_matches
593    }
594
595    fn is_variadic_expansion(&self) -> bool {
596        self.variadic_expansion
597    }
598}
599
600pub fn builtin_routine_support_oid(name: &str) -> Option<i64> {
601    Some(match name.strip_prefix("pg_catalog.").unwrap_or(name) {
602        "textlike_support" => 1023,
603        "texticregexeq_support" => 1024,
604        "texticlike_support" => 1025,
605        "network_subset_support" => 1173,
606        "textregexeq_support" => 1364,
607        "varchar_support" => 3097,
608        "numeric_support" => 3157,
609        _ => return None,
610    })
611}
612
613pub fn function_binding_matches(binding: &FunctionBinding, target: &FunctionBinding) -> bool {
614    if binding.builtin || target.builtin {
615        return false;
616    }
617    match (binding.object_id, target.object_id) {
618        (Some(binding), Some(target)) => binding == target,
619        (None, None) => {
620            binding.name == target.name && binding.argument_types == target.argument_types
621        }
622        _ => false,
623    }
624}
625
626pub fn routine_local_name(name: &str) -> Result<String, SQLError> {
627    routine_local_name_with_control(name, &uqa_core::memory::ProductionControl::uncontrolled()).map(
628        |name| {
629            name.into_uncontrolled()
630                .expect("ordinary routine local name")
631        },
632    )
633}
634
635mod names_production;
636pub use names_production::{
637    routine_local_name_with_control, routine_returns_anonymous_record_with_control,
638};
639
640pub fn routine_kind(def: &CreateFunction) -> &'static str {
641    if def.is_procedure {
642        "procedure"
643    } else {
644        "function"
645    }
646}
647
648pub mod anonymous_block;
649pub mod call;
650pub mod invocation;