Skip to main content

uqa_sql/routines/
result_check.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! The final statement of a SQL function body checked against the declared result, as `PostgreSQL`'s `check_sql_fn_retval` checks it at creation and before execution.
8
9use super::declaration::RoutineTypeCatalog;
10use crate::{
11    ast::{ColumnType, CreateFunction, FunctionReturns},
12    binding::statements::AnalyzedResult,
13    expr::composites,
14    type_resolution::canonical_routine_type_name,
15    SQLError,
16};
17
18mod anonymous;
19pub use anonymous::{
20    validate_anonymous_record_result, validate_procedural_record_identity,
21    validate_trigger_record_result,
22};
23
24/// Whether the routine returns nothing, one output value, or the complete output tuple.
25#[derive(Debug, Clone, Copy, PartialEq, Eq)]
26pub enum SQLFunctionResultKind {
27    Void,
28    Value,
29    Tuple,
30}
31
32/// A live attribute in the declared function result, in physical attribute order.
33#[derive(Debug, Clone, PartialEq, Eq)]
34pub struct SQLFunctionResultColumn {
35    pub name: String,
36    pub ty: ColumnType,
37}
38
39/// The SQL-owned result decision. A lone row value is distinct from a tuple whose individual columns need assignment coercion. Domains remain scalar, even when their base type is composite.
40#[derive(Debug, Clone, PartialEq, Eq)]
41pub struct SQLFunctionResultLayout {
42    pub kind: SQLFunctionResultKind,
43    pub declared_type: ColumnType,
44    /// Descriptor of a lone returned row, captured before the statement executes.
45    pub source_record: Option<Vec<Option<ColumnType>>>,
46    /// Named composite attributes or OUT parameters; absent for scalar and unspecified record results.
47    pub columns: Option<Vec<SQLFunctionResultColumn>>,
48}
49
50/// Check the final statement without exposing its execution layout to callers that only validate a definition.
51pub fn check_sql_function_result(
52    types: &dyn RoutineTypeCatalog,
53    def: &CreateFunction,
54    last: Option<&AnalyzedResult>,
55) -> Result<(), SQLError> {
56    sql_function_result_layout(types, def, last).map(|_| ())
57}
58
59/// Decide whether the final statement returns a single value or its complete row, using the same assignment rules at creation and invocation.
60pub fn sql_function_result_layout(
61    types: &dyn RoutineTypeCatalog,
62    def: &CreateFunction,
63    last: Option<&AnalyzedResult>,
64) -> Result<SQLFunctionResultLayout, SQLError> {
65    let mut layout = declared_sql_function_result(types, def)?;
66    if layout.kind == SQLFunctionResultKind::Void {
67        return Ok(layout);
68    }
69    let mismatch = |detail: String| -> Result<SQLError, SQLError> {
70        Ok(SQLError::Diagnostic {
71            sqlstate: "42P13".into(),
72            message: format!(
73                "return type mismatch in function declared to return {}",
74                types.format_type(&layout.declared_type)?
75            ),
76            detail: Some(detail),
77            hint: None,
78        })
79    };
80    let Some(columns) = last.and_then(AnalyzedResult::column_types) else {
81        return Err(mismatch(
82            "Function's final statement must be SELECT or INSERT/UPDATE/DELETE/MERGE RETURNING."
83                .into(),
84        )?);
85    };
86    if let [actual] = columns {
87        layout.source_record = last
88            .and_then(|result| result.record_fields(0))
89            .map(|fields| fields.to_vec())
90            .or(sql_function_composite_columns(types, actual.as_ref())?);
91    }
92    if layout.kind == SQLFunctionResultKind::Value {
93        let [actual] = columns else {
94            return Err(mismatch(
95                "Final statement must return exactly one column.".into(),
96            )?);
97        };
98        if let Some(actual) = actual {
99            if !crate::assignment_type_compatible(actual, &layout.declared_type) {
100                return Err(mismatch(format!(
101                    "Actual return type is {}.",
102                    types.format_type(actual)?
103                ))?);
104            }
105        }
106        return Ok(layout);
107    }
108    if !def.is_procedure {
109        if let [Some(actual)] = columns {
110            if crate::assignment_type_compatible(actual, &layout.declared_type) {
111                layout.kind = SQLFunctionResultKind::Value;
112                if matches!(actual, ColumnType::Record)
113                    && matches!(layout.declared_type, ColumnType::Composite(_))
114                {
115                    if let Some(source) = &layout.source_record {
116                        check_composite_assignment(types, source, &layout)?;
117                    }
118                }
119                return Ok(layout);
120            }
121        }
122    }
123    if let Some(expected) = &layout.columns {
124        for (position, actual) in columns.iter().enumerate() {
125            let Some(expected) = expected.get(position) else {
126                return Err(mismatch(
127                    "Final statement returns too many columns.".into(),
128                )?);
129            };
130            if let Some(actual) = actual {
131                if !crate::assignment_type_compatible(actual, &expected.ty) {
132                    return Err(mismatch(format!(
133                        "Final statement returns {} instead of {} at column {}.",
134                        types.format_type(actual)?,
135                        types.format_type(&expected.ty)?,
136                        position + 1
137                    ))?);
138                }
139            }
140        }
141        if columns.len() < expected.len() {
142            return Err(mismatch("Final statement returns too few columns.".into())?);
143        }
144    }
145    Ok(layout)
146}
147
148/// Resolve the result declaration independently of a final statement.
149pub fn declared_sql_function_result(
150    types: &dyn RoutineTypeCatalog,
151    def: &CreateFunction,
152) -> Result<SQLFunctionResultLayout, SQLError> {
153    let outputs = def.output_params();
154    if def.is_procedure || outputs.len() > 1 {
155        let columns = outputs
156            .iter()
157            .map(|parameter| {
158                Ok(SQLFunctionResultColumn {
159                    name: parameter.name.clone(),
160                    ty: types.resolve_catalog_column_type_name(&parameter.type_name)?,
161                })
162            })
163            .collect::<Result<Vec<_>, SQLError>>()?;
164        return Ok(SQLFunctionResultLayout {
165            kind: if columns.is_empty() {
166                SQLFunctionResultKind::Void
167            } else {
168                SQLFunctionResultKind::Tuple
169            },
170            declared_type: ColumnType::Record,
171            source_record: None,
172            columns: Some(columns),
173        });
174    }
175    let type_name = match (outputs.first(), &def.returns) {
176        (Some(parameter), _) => parameter.type_name.as_str(),
177        (None, FunctionReturns::Scalar { type_name } | FunctionReturns::SetOf { type_name }) => {
178            type_name.as_str()
179        }
180        (None, FunctionReturns::None | FunctionReturns::Table) => "void",
181    };
182    let declared_type = match canonical_routine_type_name(type_name).as_str() {
183        "void" => ColumnType::Void,
184        "record" => ColumnType::Record,
185        _ => types.resolve_catalog_column_type_name(type_name)?,
186    };
187    let (kind, columns) = match &declared_type {
188        ColumnType::Void => (SQLFunctionResultKind::Void, None),
189        ColumnType::Record => (SQLFunctionResultKind::Tuple, None),
190        ColumnType::Composite(reference) => {
191            let descriptor = composites::descriptor(types.composite_types(), reference.oid)?;
192            let columns = descriptor
193                .attributes
194                .iter()
195                .map(|attribute| SQLFunctionResultColumn {
196                    name: attribute.name.clone(),
197                    ty: attribute.ty.clone(),
198                })
199                .collect();
200            (SQLFunctionResultKind::Tuple, Some(columns))
201        }
202        _ => (SQLFunctionResultKind::Value, None),
203    };
204    Ok(SQLFunctionResultLayout {
205        kind,
206        declared_type,
207        source_record: None,
208        columns,
209    })
210}
211
212fn check_composite_assignment(
213    types: &dyn RoutineTypeCatalog,
214    source: &[Option<ColumnType>],
215    layout: &SQLFunctionResultLayout,
216) -> Result<(), SQLError> {
217    let target = layout.columns.as_deref().unwrap_or_default();
218    let detail = match source.len().cmp(&target.len()) {
219        std::cmp::Ordering::Less => Some("Input has too few columns.".into()),
220        std::cmp::Ordering::Greater => Some("Input has too many columns.".into()),
221        std::cmp::Ordering::Equal => source
222            .iter()
223            .zip(target)
224            .enumerate()
225            .find_map(|(index, (source, target))| {
226                source
227                    .as_ref()
228                    .filter(|source| !crate::assignment_type_compatible(source, &target.ty))
229                    .map(|source| {
230                        Ok::<_, SQLError>(format!(
231                            "Cannot cast type {} to {} in column {}.",
232                            types.format_type(source)?,
233                            types.format_type(&target.ty)?,
234                            index + 1
235                        ))
236                    })
237            })
238            .transpose()?,
239    };
240    if let Some(detail) = detail {
241        return Err(SQLError::Diagnostic {
242            sqlstate: "42846".into(),
243            message: format!(
244                "cannot cast type record to {}",
245                types.format_type(&layout.declared_type)?
246            ),
247            detail: Some(detail),
248            hint: None,
249        });
250    }
251    Ok(())
252}
253
254/// Read the live descriptor of a named row value. An anonymous row's descriptor must come from expression analysis, rather than inspecting its values.
255pub fn sql_function_composite_columns(
256    types: &dyn RoutineTypeCatalog,
257    source: Option<&ColumnType>,
258) -> Result<Option<Vec<Option<ColumnType>>>, SQLError> {
259    match source {
260        Some(ColumnType::Composite(reference)) => {
261            let descriptor = composites::descriptor(types.composite_types(), reference.oid)?;
262            Ok(Some(
263                descriptor
264                    .attributes
265                    .iter()
266                    .map(|attribute| Some(attribute.ty.clone()))
267                    .collect(),
268            ))
269        }
270        Some(ColumnType::Domain { base, .. }) => sql_function_composite_columns(types, Some(base)),
271        _ => Ok(None),
272    }
273}
274
275/// A whole returned record must match the caller's tuple descriptor; its fields are not assignment-coerced as independently selected columns are.
276pub fn validate_sql_function_record(
277    types: &dyn RoutineTypeCatalog,
278    source: &[Option<ColumnType>],
279    target: &[ColumnType],
280) -> Result<(), SQLError> {
281    if source.len() != target.len() {
282        return Err(record_mismatch(format!(
283            "Returned row contains {} attribute{}, but query expects {}.",
284            source.len(),
285            if source.len() == 1 { "" } else { "s" },
286            target.len()
287        )));
288    }
289    for (index, (source, target)) in source.iter().zip(target).enumerate() {
290        let Some(source) = source else {
291            return Err(record_mismatch(format!(
292                "Returned type unknown at ordinal position {}, but query expects {}.",
293                index + 1,
294                types.format_type(target)?
295            )));
296        };
297        let source_oid = crate::catalog::type_metadata::pg_type_oid(source);
298        let target_oid = crate::catalog::type_metadata::pg_type_oid(target);
299        let source_modifier = crate::catalog::type_metadata::pg_type_modifier(source);
300        let target_modifier = crate::catalog::type_metadata::pg_type_modifier(target);
301        if source_oid != target_oid || (target_modifier >= 0 && source_modifier != target_modifier)
302        {
303            return Err(record_mismatch(format!(
304                "Returned type {} at ordinal position {}, but query expects {}.",
305                types.format_type(source)?,
306                index + 1,
307                types.format_type(target)?
308            )));
309        }
310    }
311    Ok(())
312}
313
314fn record_mismatch(detail: String) -> SQLError {
315    SQLError::Diagnostic {
316        sqlstate: "42804".into(),
317        message: "function return row and query-specified return row do not match".into(),
318        detail: Some(detail),
319        hint: None,
320    }
321}
322
323/// A SQL set-returning function materializes one record descriptor for all non-null rows before the caller checks the declared result. Inspect borrowed values without copying their fields.
324pub fn validate_sql_function_record_rows(result: &crate::SQLResult) -> Result<(), SQLError> {
325    let mut descriptor = None;
326    for index in 0..result.rows.len() {
327        if let Some(uqa_core::Value::Row(row)) = result.value_at(index, 0) {
328            if let Some(fields) = row.field_types() {
329                if descriptor.is_some_and(|previous| previous != fields) {
330                    return Err(SQLError::Routine {
331                        sqlstate: "42804".into(),
332                        message: "rows returned by function are not all of the same row type"
333                            .into(),
334                    });
335                }
336                descriptor = Some(fields);
337            }
338        }
339    }
340    Ok(())
341}
342
343/// Validate the descriptor carried by the selected runtime row, including CASE branches and materialized intermediate rows whose record shapes differ.
344pub fn validate_sql_function_record_identity(
345    types: &dyn RoutineTypeCatalog,
346    source: &[uqa_core::RecordFieldType],
347    target: &[ColumnType],
348) -> Result<(), SQLError> {
349    if source.len() != target.len() {
350        return Err(record_mismatch(format!(
351            "Returned row contains {} attribute{}, but query expects {}.",
352            source.len(),
353            if source.len() == 1 { "" } else { "s" },
354            target.len(),
355        )));
356    }
357    for (index, (source, target)) in source.iter().zip(target).enumerate() {
358        let target_modifier = crate::catalog::type_metadata::pg_type_modifier(target);
359        if i64::from(source.oid) == crate::catalog::type_metadata::pg_type_oid(target)
360            && (target_modifier < 0 || i64::from(source.type_modifier) == target_modifier)
361        {
362            continue;
363        }
364        let source_name = if source.oid == 705 {
365            "unknown".into()
366        } else {
367            types.format_type_oid(source.oid)?
368        };
369        return Err(record_mismatch(format!(
370            "Returned type {source_name} at ordinal position {}, but query expects {}.",
371            index + 1,
372            types.format_type(target)?,
373        )));
374    }
375    Ok(())
376}
377
378#[cfg(test)]
379mod tests;