uqa_sql/plpgsql/
parsing.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! PL/pgSQL parser invocation, datum lowering, and condition normalization.
8
9use super::{
10    condition_sqlstate, ensure_single_tag, expect_tag, json_bool_or_false, json_kind,
11    json_optional_i64, json_usize_or_zero, lower_block, lower_cursor_scroll_options, lower_expr,
12    lower_full_statement, normalize_plpgsql_type, optional_array, require, require_nonempty_str,
13    validate_assignable_datum, CreateFunction, FunctionBody, FunctionParamMode, FunctionReturns,
14    JSONValue, PLpgSQLCursor, PLpgSQLDatum, PLpgSQLFunction, PLpgSQLRowField, PLpgSQLVar, Result,
15    RoutineColumnTypeReference, SQLError,
16};
17
18pub fn parse_function(def: &CreateFunction) -> Result<PLpgSQLFunction> {
19    let FunctionBody::Source(body) = &def.body else {
20        return Err(SQLError::Internal(
21            "PL/pgSQL parser invoked on a SQL-standard body".into(),
22        ));
23    };
24    let text = synthesize_create_text(def, body);
25    parse_plpgsql_text(&text)
26}
27
28/// Parse a stored routine using the engine's catalog type snapshot.
29pub fn parse_function_with_catalog(
30    def: &CreateFunction,
31    catalog: &pg_query::PlpgsqlCatalog,
32) -> Result<PLpgSQLFunction> {
33    let FunctionBody::Source(body) = &def.body else {
34        return Err(SQLError::Internal(
35            "PL/pgSQL parser invoked on a SQL-standard body".into(),
36        ));
37    };
38    lower_plpgsql_json(&pg_query::parse_plpgsql_with_catalog(
39        &synthesize_create_text(def, body),
40        catalog,
41    )?)
42}
43
44/// Parse an anonymous block using the engine's catalog type snapshot.
45pub fn parse_do_block_with_catalog(
46    body: &str,
47    catalog: &pg_query::PlpgsqlCatalog,
48) -> Result<PLpgSQLFunction> {
49    let tag = fresh_dollar_tag(body);
50    lower_plpgsql_json(&pg_query::parse_plpgsql_with_catalog(
51        &format!("DO {tag}{body}{tag} LANGUAGE plpgsql;"),
52        catalog,
53    )?)
54}
55
56/// Parse a `DO $$ ... $$` body through `PostgreSQL`'s native inline-code path.
57pub fn parse_do_block(body: &str) -> Result<PLpgSQLFunction> {
58    let tag = fresh_dollar_tag(body);
59    let text = format!("DO {tag}{body}{tag} LANGUAGE plpgsql;");
60    parse_plpgsql_text(&text)
61}
62
63/// Canonical `CREATE FUNCTION` / `CREATE PROCEDURE` text used solely
64/// to feed the `PL/pgSQL` parser (parameter DEFAULTs are resolved at
65/// call time and intentionally omitted).
66pub(super) fn synthesize_create_text(def: &CreateFunction, body: &str) -> String {
67    let mut sql = String::new();
68    sql.push_str(if def.is_procedure {
69        "CREATE PROCEDURE "
70    } else {
71        "CREATE FUNCTION "
72    });
73    sql.push_str(&quote_ident(&def.name));
74    sql.push('(');
75    let mut first = true;
76    for p in &def.params {
77        if matches!(p.mode, FunctionParamMode::Table) {
78            continue;
79        }
80        if !first {
81            sql.push_str(", ");
82        }
83        first = false;
84        match p.mode {
85            FunctionParamMode::Out => sql.push_str("OUT "),
86            FunctionParamMode::InOut => sql.push_str("INOUT "),
87            FunctionParamMode::Variadic => sql.push_str("VARIADIC "),
88            FunctionParamMode::In | FunctionParamMode::Table => {}
89        }
90        if !p.name.is_empty() {
91            sql.push_str(&quote_ident(&p.name));
92            sql.push(' ');
93        }
94        sql.push_str(&p.type_name);
95    }
96    sql.push(')');
97    match &def.returns {
98        FunctionReturns::None => {}
99        FunctionReturns::Scalar { type_name } => {
100            sql.push_str(" RETURNS ");
101            sql.push_str(type_name);
102        }
103        FunctionReturns::SetOf { type_name } => {
104            sql.push_str(" RETURNS SETOF ");
105            sql.push_str(type_name);
106        }
107        FunctionReturns::Table => {
108            sql.push_str(" RETURNS TABLE(");
109            let mut first_col = true;
110            for p in &def.params {
111                if !matches!(p.mode, FunctionParamMode::Table) {
112                    continue;
113                }
114                if !first_col {
115                    sql.push_str(", ");
116                }
117                first_col = false;
118                sql.push_str(&quote_ident(&p.name));
119                sql.push(' ');
120                sql.push_str(&p.type_name);
121            }
122            sql.push(')');
123        }
124    }
125    let tag = fresh_dollar_tag(body);
126    sql.push_str(" AS ");
127    sql.push_str(&tag);
128    sql.push_str(body);
129    sql.push_str(&tag);
130    sql.push_str(" LANGUAGE plpgsql;");
131    sql
132}
133
134pub(super) fn quote_ident(name: &str) -> String {
135    format!("\"{}\"", name.replace('"', "\"\""))
136}
137
138/// Dollar-quote tag guaranteed not to collide with the body text.
139pub(super) fn fresh_dollar_tag(body: &str) -> String {
140    let mut n = 0usize;
141    loop {
142        let tag = if n == 0 {
143            "$$".to_string()
144        } else {
145            format!("$plpgsql{n}$")
146        };
147        if !body.contains(&tag) {
148            return tag;
149        }
150        n += 1;
151    }
152}
153
154pub(super) fn parse_plpgsql_text(text: &str) -> Result<PLpgSQLFunction> {
155    lower_plpgsql_json(&pg_query::parse_plpgsql(text)?)
156}
157
158fn lower_plpgsql_json(json: &JSONValue) -> Result<PLpgSQLFunction> {
159    let functions = json
160        .as_array()
161        .ok_or_else(|| SQLError::Internal("PL/pgSQL parse returned no function list".into()))?;
162    if functions.len() != 1 {
163        return Err(SQLError::Internal(format!(
164            "PL/pgSQL parse returned {} functions; expected exactly one",
165            functions.len()
166        )));
167    }
168    let function = expect_tag(&functions[0], "PLpgSQL_function", "parsed function")?;
169    lower_function(function)
170}
171
172// ---------------------------------------------------------------------
173// JSON lowering
174// ---------------------------------------------------------------------
175
176/// Divergence from `PostgreSQL`: the JSON dump does not carry each
177/// block's `initvarnos`, so declared-variable defaults (including
178/// those of nested `DECLARE` sections) are evaluated once at routine
179/// entry rather than on every block entry, and a nested declaration
180/// shadows its outer namesake for the whole body.
181pub(super) fn lower_function(function: &JSONValue) -> Result<PLpgSQLFunction> {
182    let raw_datums = function
183        .get("datums")
184        .and_then(JSONValue::as_array)
185        .ok_or_else(|| SQLError::Internal("PL/pgSQL function without datums".into()))?;
186    let mut datums = Vec::with_capacity(raw_datums.len());
187    for raw in raw_datums {
188        datums.push(lower_datum(raw)?);
189    }
190    validate_datums(&datums)?;
191    let trigger_datum = |field: &str, name: &str| -> Result<Option<usize>> {
192        let explicit = match json_optional_i64(function, field)? {
193            Some(index) if index >= 0 => {
194                let index = usize::try_from(index).map_err(|_| {
195                    SQLError::Internal(format!(
196                        "PL/pgSQL {field} {index} does not fit this platform"
197                    ))
198                })?;
199                if index >= datums.len() {
200                    return Err(SQLError::Internal(format!(
201                        "PL/pgSQL {field} has out-of-range datum index {index}"
202                    )));
203                }
204                Some(index)
205            }
206            Some(index) => {
207                return Err(SQLError::Internal(format!(
208                    "PL/pgSQL {field} has invalid datum index {index}"
209                )))
210            }
211            None => None,
212        };
213        Ok(explicit.or_else(|| {
214            datums.iter().position(|datum| {
215                datum
216                    .name()
217                    .is_some_and(|datum_name| datum_name.eq_ignore_ascii_case(name))
218            })
219        }))
220    };
221    let new_datum = trigger_datum("new_varno", "new")?;
222    let old_datum = trigger_datum("old_varno", "old")?;
223    let found_datum = datums
224        .iter()
225        .position(|d| matches!(d, PLpgSQLDatum::Var(v) if v.name.eq_ignore_ascii_case("found")));
226    let raw_action = require(function, "action")?;
227    let action = expect_tag(raw_action, "PLpgSQL_stmt_block", "function body")?;
228    let action = lower_block(action, &datums)?;
229    Ok(PLpgSQLFunction {
230        datums,
231        action,
232        new_datum,
233        old_datum,
234        found_datum,
235    })
236}
237
238fn has_percent_type_suffix(type_name: &str) -> bool {
239    type_name
240        .get(type_name.len().saturating_sub("%type".len())..)
241        .is_some_and(|suffix| suffix.eq_ignore_ascii_case("%type"))
242}
243
244fn lower_percent_type_reference(
245    datatype: &JSONValue,
246    variable_name: &str,
247) -> Result<RoutineColumnTypeReference> {
248    let identifiers = require(datatype, "typname_identifiers")?
249        .as_array()
250        .ok_or_else(|| {
251            SQLError::Internal(format!(
252                "PL/pgSQL variable `{variable_name}` type metadata `typname_identifiers` must be an array"
253            ))
254        })?;
255    let identifiers = identifiers
256        .iter()
257        .enumerate()
258        .map(|(index, identifier)| match identifier.as_str() {
259            Some(identifier) if !identifier.is_empty() => Ok(identifier.to_string()),
260            _ => Err(SQLError::Internal(format!(
261                "PL/pgSQL variable `{variable_name}` type metadata identifier {index} must be a non-empty string"
262            ))),
263        })
264        .collect::<Result<Vec<_>>>()?;
265    match identifiers.as_slice() {
266        [relation, column] => Ok(RoutineColumnTypeReference::new(
267            None,
268            relation.clone(),
269            column.clone(),
270        )),
271        [schema, relation, column] => Ok(RoutineColumnTypeReference::new(
272            Some(schema.clone()),
273            relation.clone(),
274            column.clone(),
275        )),
276        _ => Err(SQLError::TypeMismatch(format!(
277            "PL/pgSQL variable `{variable_name}` %TYPE must identify a relation column"
278        ))),
279    }
280}
281
282pub(super) fn lower_datum(raw: &JSONValue) -> Result<PLpgSQLDatum> {
283    ensure_single_tag(raw, "datum")?;
284    if let Some(var) = raw.get("PLpgSQL_var") {
285        let name = require_nonempty_str(var, "refname", "variable datum")?;
286        let datatype = require(var, "datatype")?;
287        let datatype = expect_tag(datatype, "PLpgSQL_type", "variable datatype")?;
288        let type_name = normalize_plpgsql_type(&require_nonempty_str(
289            datatype,
290            "typname",
291            "variable datatype",
292        )?);
293        if type_name.is_empty() {
294            return Err(SQLError::Internal(format!(
295                "PL/pgSQL variable `{name}` has an empty normalized type"
296            )));
297        }
298        let type_reference = has_percent_type_suffix(&type_name)
299            .then(|| lower_percent_type_reference(datatype, &name))
300            .transpose()?;
301        let default = match var.get("default_val") {
302            Some(node) => Some(lower_expr(node)?),
303            None => None,
304        };
305        let cursor = if let Some(query) = var.get("cursor_explicit_expr") {
306            Some(PLpgSQLCursor {
307                query: lower_full_statement(query)?,
308                argument_row: match json_optional_i64(var, "cursor_explicit_argrow")? {
309                    None | Some(-1) => None,
310                    Some(index) if index >= 0 => Some(usize::try_from(index).map_err(|_| {
311                        SQLError::Internal(format!(
312                            "PL/pgSQL cursor `{name}` argument row {index} does not fit this platform"
313                        ))
314                    })?),
315                    Some(index) => {
316                        return Err(SQLError::Internal(format!(
317                            "PL/pgSQL cursor `{name}` has invalid argument row {index}"
318                        )));
319                    }
320                },
321                scroll: lower_cursor_scroll_options(var, "cursor declaration")?,
322            })
323        } else {
324            if var.get("cursor_explicit_argrow").is_some() {
325                return Err(SQLError::Internal(format!(
326                    "PL/pgSQL cursor variable `{name}` has arguments but no query"
327                )));
328            }
329            None
330        };
331        return Ok(PLpgSQLDatum::Var(Box::new(PLpgSQLVar {
332            name,
333            type_oid: json_optional_i64(datatype, "typoid")?
334                .map(|oid| {
335                    u32::try_from(oid).map_err(|_| {
336                        SQLError::Internal("PL/pgSQL variable has an invalid type OID".into())
337                    })
338                })
339                .transpose()?,
340            type_name,
341            type_reference,
342            default,
343            constant: json_bool_or_false(var, "isconst")?,
344            not_null: json_bool_or_false(var, "notnull")?,
345            cursor,
346            lineno: json_optional_i64(var, "lineno")?,
347        })));
348    }
349    if let Some(rec) = raw.get("PLpgSQL_rec") {
350        return Ok(PLpgSQLDatum::Rec {
351            name: require_nonempty_str(rec, "refname", "record datum")?,
352        });
353    }
354    if let Some(field) = raw.get("PLpgSQL_recfield") {
355        return Ok(PLpgSQLDatum::RecField {
356            field: require_nonempty_str(field, "fieldname", "record-field datum")?,
357            // libpg_query omits a zero-valued recparentno.
358            parent: json_usize_or_zero(field, "recparentno")?,
359        });
360    }
361    if let Some(row) = raw.get("PLpgSQL_row") {
362        return Ok(PLpgSQLDatum::Row {
363            fields: lower_row_fields(row)?,
364        });
365    }
366    Err(SQLError::Unsupported(format!(
367        "PL/pgSQL datum {}",
368        json_kind(raw)
369    )))
370}
371
372pub(super) fn lower_row_fields(row: &JSONValue) -> Result<Vec<PLpgSQLRowField>> {
373    let mut out = Vec::new();
374    if let Some(fields) = optional_array(row, "fields")? {
375        for f in fields {
376            // libpg_query's JSON dump omits zero-valued fields, so a
377            // missing varno means datum 0.
378            out.push(PLpgSQLRowField {
379                name: require_nonempty_str(f, "name", "row target field")?,
380                varno: json_usize_or_zero(f, "varno")?,
381            });
382        }
383    }
384    Ok(out)
385}
386
387pub(super) fn validate_datums(datums: &[PLpgSQLDatum]) -> Result<()> {
388    for (idx, datum) in datums.iter().enumerate() {
389        match datum {
390            PLpgSQLDatum::RecField { parent, .. } => {
391                let Some(parent_datum) = datums.get(*parent) else {
392                    return Err(SQLError::Internal(format!(
393                        "PL/pgSQL record-field datum {idx} references missing parent datum {parent}"
394                    )));
395                };
396                if !matches!(parent_datum, PLpgSQLDatum::Rec { .. }) {
397                    return Err(SQLError::Internal(format!(
398                        "PL/pgSQL record-field datum {idx} parent {parent} is not a record"
399                    )));
400                }
401            }
402            PLpgSQLDatum::Row { fields } => {
403                if fields.is_empty() {
404                    return Err(SQLError::Internal(format!(
405                        "PL/pgSQL row datum {idx} has no fields"
406                    )));
407                }
408                for field in fields {
409                    validate_assignable_datum(datums, field.varno, "row target field")?;
410                }
411            }
412            PLpgSQLDatum::Var(var) => {
413                if let Some(cursor) = &var.cursor {
414                    if var.type_name != "refcursor" {
415                        return Err(SQLError::Internal(format!(
416                            "PL/pgSQL bound cursor `{}` is not a refcursor datum",
417                            var.name
418                        )));
419                    }
420                    if let Some(argument_row) = cursor.argument_row {
421                        if !matches!(datums.get(argument_row), Some(PLpgSQLDatum::Row { .. })) {
422                            return Err(SQLError::Internal(format!(
423                                "PL/pgSQL cursor `{}` references invalid argument row {argument_row}",
424                                var.name
425                            )));
426                        }
427                    }
428                }
429            }
430            PLpgSQLDatum::Rec { .. } => {}
431        }
432    }
433    Ok(())
434}
435
436pub(super) fn normalize_condition(value: String, allow_others: bool) -> Result<String> {
437    let lower = value.to_ascii_lowercase();
438    if allow_others && lower == "others" {
439        return Ok(lower);
440    }
441    if condition_sqlstate(&lower).is_some() {
442        return Ok(lower);
443    }
444    let upper = value.to_ascii_uppercase();
445    if upper.len() == 5
446        && upper
447            .bytes()
448            .all(|byte| byte.is_ascii_uppercase() || byte.is_ascii_digit())
449    {
450        return Ok(upper);
451    }
452    Err(SQLError::Internal(format!(
453        "unrecognized PL/pgSQL exception condition `{value}`"
454    )))
455}
uqa_sql/plpgsql/parsing.rs

uqa_sql/plpgsql/
parsing.rs
