Skip to main content

uqa_sql/
plpgsql.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! `PL/pgSQL` function bodies: typed AST, parser, and the variable
8//! binding rewriter.
9//!
10//! Bodies are parsed with `libpg_query`'s `PL/pgSQL` parser
11//! (`pg_query::parse_plpgsql`), which returns the same JSON dump
12//! `PostgreSQL` itself produces. This module lowers that JSON into a
13//! typed AST whose embedded SQL fragments are pre-compiled into
14//! [`Expr`] / [`Statement`] values, ready for execution against the
15//! engine.
16//!
17//! Variable references inside embedded SQL are plain column
18//! references after compilation. At execution time the interpreter
19//! rewrites them into literals through [`VariableResolver`] /
20//! [`bind_statement_variables`] / [`bind_expression_variables`] before
21//! handing the statement to the engine, after the binder has checked
22//! each reference against the columns and relations the statement can
23//! see, so that a name that is both a variable and a column resolves
24//! as `plpgsql.variable_conflict` directs.
25
26use serde_json::Value as JSONValue;
27use uqa_core::Value;
28
29use crate::ast::{
30    CreateFunction, CursorDirection, Expr, FromClause, FunctionBody, FunctionParamMode,
31    FunctionReturns, MergeWhen, Projection, RoutineColumnTypeReference, SelectStmt, Statement, CTE,
32};
33use crate::error::{Result, SQLError};
34
35// ---------------------------------------------------------------------
36// Typed AST
37// ---------------------------------------------------------------------
38
39/// A parsed `PL/pgSQL` function body: the flat datum table plus the
40/// outermost block.
41#[derive(Debug, Clone)]
42pub struct PLpgSQLFunction {
43    pub compilation: PLpgSQLCompilationIdentity,
44    pub datums: Vec<PLpgSQLDatum>,
45    pub action: PLpgSQLBlock,
46    /// Datum holding the implicit `NEW` record for a trigger function.
47    pub new_datum: Option<usize>,
48    /// Datum holding the implicit `OLD` record for a trigger function.
49    pub old_datum: Option<usize>,
50    /// Index of the implicit `FOUND` variable in [`Self::datums`].
51    pub found_datum: Option<usize>,
52    /// The options the body declares before its first block.
53    pub options: CompileOptions,
54    /// How a name that is both a variable and a column resolves in this compilation of the body: the body's own option, or the setting of the session that compiled it.
55    pub variable_conflict: VariableConflict,
56}
57
58impl PLpgSQLFunction {
59    /// Datum indices synthesized for loop-local variables. The interpreter
60    /// binds these names only while their loop runs so an outer variable with
61    /// the same name stays visible elsewhere.
62    pub fn loop_local_variable_datums(&self) -> std::collections::BTreeSet<usize> {
63        let mut out = std::collections::BTreeSet::new();
64        collect_loop_local_vars_block(&self.action, &mut out);
65        out
66    }
67
68    /// Datum indices used as bound-cursor arguments. They are visible only
69    /// while the cursor query is bound, not throughout the routine body.
70    pub fn cursor_argument_datums(&self) -> std::collections::BTreeSet<usize> {
71        let mut out = std::collections::BTreeSet::new();
72        for datum in &self.datums {
73            let PLpgSQLDatum::Var(var) = datum else {
74                continue;
75            };
76            let Some(argument_row) = var.cursor.as_ref().and_then(|cursor| cursor.argument_row)
77            else {
78                continue;
79            };
80            if let Some(PLpgSQLDatum::Row { fields }) = self.datums.get(argument_row) {
81                out.extend(fields.iter().map(|field| field.varno));
82            }
83        }
84        out
85    }
86}
87
88fn collect_loop_local_vars_block(
89    block: &PLpgSQLBlock,
90    out: &mut std::collections::BTreeSet<usize>,
91) {
92    collect_loop_local_vars_stmts(&block.body, out);
93    for arm in &block.exceptions {
94        collect_loop_local_vars_stmts(&arm.body, out);
95    }
96}
97
98fn collect_loop_local_vars_stmts(
99    stmts: &[PLpgSQLStmt],
100    out: &mut std::collections::BTreeSet<usize>,
101) {
102    for stmt in stmts {
103        match stmt.unlocated() {
104            PLpgSQLStmt::Block(block) => collect_loop_local_vars_block(block, out),
105            PLpgSQLStmt::If {
106                then_body,
107                elsifs,
108                else_body,
109                ..
110            } => {
111                collect_loop_local_vars_stmts(then_body, out);
112                for (_, body) in elsifs {
113                    collect_loop_local_vars_stmts(body, out);
114                }
115                if let Some(body) = else_body {
116                    collect_loop_local_vars_stmts(body, out);
117                }
118            }
119            PLpgSQLStmt::Case {
120                arms, else_body, ..
121            } => {
122                for (_, body) in arms {
123                    collect_loop_local_vars_stmts(body, out);
124                }
125                if let Some(body) = else_body {
126                    collect_loop_local_vars_stmts(body, out);
127                }
128            }
129            PLpgSQLStmt::Loop { body, .. } | PLpgSQLStmt::While { body, .. } => {
130                collect_loop_local_vars_stmts(body, out);
131            }
132            PLpgSQLStmt::ForI { var, body, .. } => {
133                out.insert(*var);
134                collect_loop_local_vars_stmts(body, out);
135            }
136            PLpgSQLStmt::ForCursor { target, body, .. } => {
137                out.insert(*target);
138                collect_loop_local_vars_stmts(body, out);
139            }
140            PLpgSQLStmt::ForQuery { body, .. }
141            | PLpgSQLStmt::ForDynamic { body, .. }
142            | PLpgSQLStmt::ForeachArray { body, .. } => {
143                collect_loop_local_vars_stmts(body, out);
144            }
145            _ => {}
146        }
147    }
148}
149
150/// One entry in the function's flat datum table. `varno` / `dno`
151/// references inside statements index into this table.
152#[derive(Debug, Clone)]
153pub enum PLpgSQLDatum {
154    Var(Box<PLpgSQLVar>),
155    /// `RECORD` variable (also `FOR rec IN ...` loop targets).
156    Rec {
157        name: String,
158    },
159    /// `rec.field` assignment target.
160    RecField {
161        field: String,
162        parent: usize,
163    },
164    /// Multi-variable target list (`SELECT ... INTO a, b`).
165    Row {
166        fields: Vec<PLpgSQLRowField>,
167    },
168}
169
170impl PLpgSQLDatum {
171    pub fn name(&self) -> Option<&str> {
172        match self {
173            PLpgSQLDatum::Var(v) => Some(&v.name),
174            PLpgSQLDatum::Rec { name } => Some(name),
175            PLpgSQLDatum::RecField { .. } | PLpgSQLDatum::Row { .. } => None,
176        }
177    }
178}
179
180/// Scalar `PL/pgSQL` variable (declared variable, parameter, loop
181/// counter, or an internal compiler temporary).
182#[derive(Debug, Clone)]
183pub struct PLpgSQLVar {
184    pub name: String,
185    /// Resolved identity from a catalog-aware parse; absent in catalog-free JSON.
186    pub type_oid: Option<u32>,
187    /// Normalized type name (`integer`, `text`, ...). The engine resolves
188    /// catalog-backed references such as `%TYPE` before execution.
189    pub type_name: String,
190    /// Exact relation-column identity emitted by the PL/pgSQL parser for a table-backed `%TYPE` declaration.
191    pub type_reference: Option<RoutineColumnTypeReference>,
192    pub default: Option<PLpgSQLExpression>,
193    pub constant: bool,
194    pub not_null: bool,
195    /// Definition of a bound cursor declared with `CURSOR (...) FOR query`.
196    pub cursor: Option<PLpgSQLCursor>,
197    /// Source line of the declaration; used to disambiguate loop
198    /// variables that shadow outer names.
199    pub lineno: Option<i64>,
200}
201
202#[derive(Debug, Clone)]
203pub struct PLpgSQLCursor {
204    pub query: PLpgSQLStatement,
205    pub source_sql: std::sync::Arc<str>,
206    pub argument_row: Option<usize>,
207    /// Explicit declaration scroll mode. `None` leaves scrollability query-dependent.
208    pub scroll: Option<bool>,
209}
210
211#[derive(Debug, Clone)]
212pub struct PLpgSQLCursorArgument {
213    pub name: Option<String>,
214    pub expr: Expr,
215}
216
217/// Query source selected by one `OPEN` statement.
218#[derive(Debug, Clone)]
219pub enum PLpgSQLCursorOpen {
220    Bound {
221        arguments: PLpgSQLCursorArguments,
222    },
223    Static {
224        query: Box<PLpgSQLStatement>,
225        source_sql: std::sync::Arc<str>,
226        scroll: Option<bool>,
227    },
228    Dynamic {
229        query: PLpgSQLExpression,
230        params: Vec<PLpgSQLExpression>,
231        scroll: Option<bool>,
232    },
233}
234
235/// Constant or run-time expression controlling cursor movement.
236#[derive(Debug, Clone)]
237pub enum PLpgSQLCursorCount {
238    Constant(i64),
239    Expression(PLpgSQLExpression),
240}
241
242/// `name -> datum` slot of a row target.
243#[derive(Debug, Clone)]
244pub struct PLpgSQLRowField {
245    pub name: String,
246    pub varno: usize,
247}
248
249/// `[DECLARE ...] BEGIN ... [EXCEPTION ...] END` block.
250#[derive(Debug, Clone)]
251pub struct PLpgSQLBlock {
252    pub initvarnos: Vec<usize>,
253    pub label: Option<String>,
254    pub body: Vec<PLpgSQLStmt>,
255    pub exceptions: Vec<PLpgSQLExceptionArm>,
256}
257
258/// One `WHEN cond [OR cond ...] THEN stmts` arm of an exception
259/// section.
260#[derive(Debug, Clone)]
261pub struct PLpgSQLExceptionArm {
262    /// Lower-cased condition names (`others`, `division_by_zero`,
263    /// ...). Explicit `SQLSTATE 'xxxxx'` conditions arrive as the
264    /// five-character code.
265    pub conditions: Vec<String>,
266    pub body: Vec<PLpgSQLStmt>,
267}
268
269/// `RAISE` severity.
270#[derive(Debug, Clone, Copy, PartialEq, Eq)]
271pub enum RaiseLevel {
272    Debug,
273    Log,
274    Info,
275    Notice,
276    Warning,
277    Error,
278}
279
280impl RaiseLevel {
281    pub fn as_str(self) -> &'static str {
282        match self {
283            RaiseLevel::Debug => "DEBUG",
284            RaiseLevel::Log => "LOG",
285            RaiseLevel::Info => "INFO",
286            RaiseLevel::Notice => "NOTICE",
287            RaiseLevel::Warning => "WARNING",
288            RaiseLevel::Error => "ERROR",
289        }
290    }
291
292    /// The level of the notice a `RAISE` at this level reports, or `None` for `EXCEPTION`, which raises an error.
293    pub const fn notice_level(self) -> Option<crate::NoticeLevel> {
294        match self {
295            RaiseLevel::Debug => Some(crate::NoticeLevel::Debug),
296            RaiseLevel::Log => Some(crate::NoticeLevel::Log),
297            RaiseLevel::Info => Some(crate::NoticeLevel::Info),
298            RaiseLevel::Notice => Some(crate::NoticeLevel::Notice),
299            RaiseLevel::Warning => Some(crate::NoticeLevel::Warning),
300            RaiseLevel::Error => None,
301        }
302    }
303}
304
305/// Assignment / `INTO` target.
306#[derive(Debug, Clone)]
307pub enum IntoTarget {
308    /// A `RECORD` variable receives the whole row.
309    Rec(usize),
310    /// Positional list of scalar targets.
311    Row(Vec<PLpgSQLRowField>),
312}
313
314/// Executable `PL/pgSQL` statement.
315#[derive(Debug, Clone)]
316pub enum PLpgSQLStmt {
317    /// Original parser statement position, retained independently of its executable fragments.
318    Located {
319        line: i64,
320        statement: Box<Self>,
321    },
322    Block(PLpgSQLBlock),
323    /// `target := expr` (also `=`). `target` indexes the datum table.
324    Assign {
325        target: usize,
326        expr: PLpgSQLExpression,
327    },
328    If {
329        cond: PLpgSQLExpression,
330        then_body: Vec<PLpgSQLStmt>,
331        elsifs: Vec<(PLpgSQLExpression, Vec<PLpgSQLStmt>)>,
332        else_body: Option<Vec<PLpgSQLStmt>>,
333    },
334    /// CASE statement. Simple form carries `t_expr` + the temporary
335    /// datum the compiler references from each rewritten WHEN.
336    Case {
337        t_expr: Option<PLpgSQLExpression>,
338        t_varno: Option<usize>,
339        arms: Vec<(PLpgSQLExpression, Vec<PLpgSQLStmt>)>,
340        else_body: Option<Vec<PLpgSQLStmt>>,
341    },
342    Loop {
343        label: Option<String>,
344        body: Vec<PLpgSQLStmt>,
345    },
346    While {
347        label: Option<String>,
348        cond: PLpgSQLExpression,
349        body: Vec<PLpgSQLStmt>,
350    },
351    /// `FOR i IN [REVERSE] lower..upper [BY step] LOOP`.
352    ForI {
353        label: Option<String>,
354        var: usize,
355        lower: PLpgSQLExpression,
356        upper: PLpgSQLExpression,
357        step: Option<PLpgSQLExpression>,
358        reverse: bool,
359        body: Vec<PLpgSQLStmt>,
360    },
361    /// `FOR target IN <query> LOOP`.
362    ForQuery {
363        label: Option<String>,
364        target: IntoTarget,
365        query: PLpgSQLStatement,
366        source_sql: std::sync::Arc<str>,
367        body: Vec<PLpgSQLStmt>,
368    },
369    /// `FOR target IN EXECUTE query [USING params] LOOP`.
370    ForDynamic {
371        label: Option<String>,
372        target: IntoTarget,
373        query: PLpgSQLExpression,
374        params: Vec<PLpgSQLExpression>,
375        body: Vec<PLpgSQLStmt>,
376    },
377    /// `FOR recordvar IN bound_cursor [(arguments)] LOOP`.
378    ForCursor {
379        label: Option<String>,
380        target: usize,
381        cursor: usize,
382        arguments: PLpgSQLCursorArguments,
383        body: Vec<PLpgSQLStmt>,
384    },
385    /// `FOREACH target [SLICE n] IN ARRAY expression LOOP`.
386    ForeachArray {
387        label: Option<String>,
388        target: usize,
389        slice: usize,
390        expr: PLpgSQLExpression,
391        body: Vec<PLpgSQLStmt>,
392    },
393    /// `EXIT` (`is_exit`) or `CONTINUE`, optionally labelled and
394    /// conditional (`WHEN cond`).
395    Exit {
396        is_exit: bool,
397        label: Option<String>,
398        cond: Option<PLpgSQLExpression>,
399    },
400    Return {
401        value: Option<PLpgSQLReturnValue>,
402    },
403    /// `RETURN NEXT [expr]` - bare form emits the current OUT /
404    /// TABLE column values.
405    ReturnNext {
406        value: Option<PLpgSQLReturnValue>,
407    },
408    ReturnQuery {
409        query: PLpgSQLStatement,
410    },
411    ReturnQueryExecute {
412        query: PLpgSQLExpression,
413        params: Vec<PLpgSQLExpression>,
414    },
415    Raise {
416        level: RaiseLevel,
417        condition: Option<String>,
418        message: Option<String>,
419        params: Vec<PLpgSQLExpression>,
420        options: Vec<RaiseOption>,
421    },
422    /// `ASSERT condition [, message]`.
423    Assert {
424        condition: PLpgSQLExpression,
425        message: Option<PLpgSQLExpression>,
426    },
427    /// Embedded SQL statement, optionally `INTO [STRICT] target`.
428    ExecSQL {
429        stmt: PLpgSQLStatement,
430        into: Option<IntoTarget>,
431        strict: bool,
432    },
433    /// `EXECUTE <string> [INTO [STRICT] target] [USING params]`.
434    DynExecute {
435        query: PLpgSQLExpression,
436        params: Vec<PLpgSQLExpression>,
437        into: Option<IntoTarget>,
438        strict: bool,
439    },
440    Perform {
441        query: PLpgSQLStatement,
442    },
443    OpenCursor {
444        cursor: usize,
445        open: PLpgSQLCursorOpen,
446    },
447    FetchCursor {
448        cursor: usize,
449        target: IntoTarget,
450        direction: CursorDirection,
451        count: PLpgSQLCursorCount,
452    },
453    MoveCursor {
454        cursor: usize,
455        direction: CursorDirection,
456        count: PLpgSQLCursorCount,
457    },
458    CloseCursor {
459        cursor: usize,
460    },
461    /// Procedural `COMMIT [AND [NO] CHAIN]`.
462    Commit {
463        chain: bool,
464    },
465    /// Procedural `ROLLBACK [AND [NO] CHAIN]`.
466    Rollback {
467        chain: bool,
468    },
469    /// `GET DIAGNOSTICS var = KIND [, ...]` as `(kind, target datum)`.
470    GetDiagnostics {
471        items: Vec<(String, usize)>,
472    },
473}
474
475/// Value source for `RETURN` and `RETURN NEXT`. `PostgreSQL` 18 stores a simple
476/// datum reference in `retvarno`, distinct from a general SQL expression.
477#[derive(Debug, Clone)]
478pub enum PLpgSQLReturnValue {
479    Expr(PLpgSQLExpression),
480    Datum(usize),
481}
482
483// ---------------------------------------------------------------------
484// Parsing: definition -> canonical text -> libpg_query JSON -> AST
485// ---------------------------------------------------------------------
486
487/// Parse the `PL/pgSQL` body of a stored definition. The definition
488/// is re-serialized into a canonical `CREATE FUNCTION` statement so
489/// restore-from-catalog and fresh DDL take the same path.
490mod binding;
491mod conditions;
492mod diagnostics;
493mod json_validation;
494mod lowering_expression;
495mod lowering_statement;
496mod options;
497mod parsing;
498mod variable_conflicts;
499
500use json_validation::{
501    ensure_single_tag, expect_tag, json_bool_or_false, json_i64_or_zero, json_kind,
502    json_optional_i64, json_optional_str, json_optional_usize, json_usize_or_zero,
503    normalize_plpgsql_type, optional_array, require, require_i64, require_nonempty_str,
504    validate_assignable_datum, validate_record_datum, validate_scalar_datum,
505};
506use lowering_expression::{lower_expr, lower_expr_list, lower_full_statement};
507use lowering_statement::{lower_block, lower_cursor_scroll_options};
508use parsing::{lower_row_fields, normalize_condition};
509
510pub use binding::{
511    bind_expr, bind_select, bind_statement, ResolvedVariable, RewriteReference, VariableResolver,
512};
513pub use conditions::{condition_sqlstate, condition_sqlstates};
514pub use lowering_expression::compile_expression_text;
515pub use options::{compile_options, CompileOptions, VariableConflict};
516pub use parameters::parameter_type_mismatch_with_control;
517pub use parsing::{
518    parse_do_block, parse_do_block_with_catalog, parse_function, parse_function_with_catalog,
519    parse_function_with_catalog_mode,
520};
521pub use pg_query::{PlpgsqlCatalog, PlpgsqlType};
522pub use variable_conflicts::{
523    bind_expression_variables, bind_statement_variables, VariableSiteResolver,
524};
525
526#[cfg(test)]
527mod tests;
528
529pub mod runtime_diagnostics;
530
531mod fragments;
532pub use fragments::{
533    is_simple_expression_plan, is_simple_expression_plan_with_control, PLpgSQLCompilationIdentity,
534    PLpgSQLCompileMode, PLpgSQLCursorArguments, PLpgSQLExpression, PLpgSQLFragment,
535    PLpgSQLParseMode, PLpgSQLSource, PLpgSQLStatement,
536};
537
538mod parameters;
539pub use parameters::{
540    parameter_type_mismatch, parameterize_statement_variables,
541    parameterize_statement_variables_owned, OwnedPLpgSQLVariableBindings,
542    OwnedVariableSiteResolver, PLpgSQLInput, PLpgSQLVariableBindings, PLpgSQLVariableReference,
543};
544
545mod blocks;
546mod raise;
547pub use raise::{RaiseDiagnostic, RaiseOption, RaiseOptionKind};