uqa_sql/semantics/
generated_values.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! `PostgreSQL` rules for the values a statement writes to the columns whose values a table generates: identity columns and generated columns.
8
9use crate::{
10    assignment::columns::AssignmentColumnCatalog,
11    ast::{AutoIncrementKind, OverridingKind},
12    SQLError,
13};
14
15/// Stored expressions affected by an UPDATE's target columns. Explicitly assigning an unchanged input still selects its dependents; assigning a generated column to DEFAULT does not select it by itself. Callers with BEFORE ROW UPDATE triggers must recompute every stored expression instead.
16#[must_use]
17pub fn stored_columns_for_update(
18    columns: &[crate::ast::ColumnDef],
19    updated_columns: &[String],
20) -> Vec<String> {
21    columns
22        .iter()
23        .filter(|column| {
24            column.generated.as_ref().is_some_and(|generated| {
25                generated.kind == crate::ast::GeneratedColumnKind::Stored
26                    && updated_columns.iter().any(|updated| {
27                        crate::schema::dependencies::schema_expr_references_column(
28                            &generated.expression,
29                            updated,
30                        )
31                    })
32            })
33        })
34        .map(|column| column.name.clone())
35        .collect()
36}
37
38/// How a table generates a column's value.
39#[derive(Debug, Clone, Copy, PartialEq, Eq)]
40enum Generation {
41    IdentityAlways,
42    IdentityByDefault,
43    Expression,
44}
45
46/// The identity and generated columns of one table, in column order, read once for a statement that writes it. `PostgreSQL` applies these rules when it rewrites the statement, column by column in table order, before it reads or writes a row, so a statement whose source yields no row is rejected as well.
47pub struct GeneratedValueColumns {
48    columns: Vec<(String, Generation)>,
49}
50
51impl GeneratedValueColumns {
52    /// The identity and generated columns of `table`; none for a relation the catalog does not describe as a table.
53    pub fn of(catalog: &dyn AssignmentColumnCatalog, table: &str) -> Result<Self, SQLError> {
54        let columns = catalog
55            .try_describe_table(table)
56            .map_err(|error| SQLError::Internal(format!("read generated columns: {error}")))?
57            .unwrap_or_default()
58            .into_iter()
59            .filter_map(|column| {
60                let generation = match column.auto_increment.as_ref() {
61                    Some(provenance) if provenance.kind == AutoIncrementKind::IdentityAlways => {
62                        Generation::IdentityAlways
63                    }
64                    Some(provenance) if provenance.is_identity() => Generation::IdentityByDefault,
65                    _ if column.generated.is_some() => Generation::Expression,
66                    _ => return None,
67                };
68                Some((column.name, generation))
69            })
70            .collect();
71        Ok(Self { columns })
72    }
73
74    /// Whether `column` is an identity column, whose value `OVERRIDING USER VALUE` leaves to its sequence.
75    #[must_use]
76    pub fn contains(&self, column: &str) -> bool {
77        self.columns.iter().any(|(name, generation)| {
78            name == column
79                && matches!(
80                    generation,
81                    Generation::IdentityAlways | Generation::IdentityByDefault
82                )
83        })
84    }
85
86    /// Reject a value an `INSERT` supplies for a generated column, and for a `GENERATED ALWAYS` identity column without an `OVERRIDING` clause, which a generated column ignores. `targets` names each target column with whether some row supplies a value other than `DEFAULT` for it.
87    pub fn validate_insert<'a>(
88        &self,
89        targets: impl IntoIterator<Item = (&'a str, bool)>,
90        overriding: Option<OverridingKind>,
91    ) -> Result<(), SQLError> {
92        let supplied = targets
93            .into_iter()
94            .filter_map(|(column, supplied)| supplied.then_some(column))
95            .collect::<std::collections::BTreeSet<_>>();
96        for (column, generation) in &self.columns {
97            if !supplied.contains(column.as_str()) {
98                continue;
99            }
100            match generation {
101                Generation::Expression => return Err(generated_column_insert_error(column)),
102                Generation::IdentityAlways if overriding.is_none() => {
103                    return Err(generated_always_insert_error(column));
104                }
105                Generation::IdentityAlways | Generation::IdentityByDefault => {}
106            }
107        }
108        Ok(())
109    }
110
111    /// Reject an assignment other than `DEFAULT` to a generated column or a `GENERATED ALWAYS` identity column by an `UPDATE`, an `ON CONFLICT DO UPDATE` or a `MERGE` update. `assignments` names each assigned column with whether its value is `DEFAULT`.
112    pub fn validate_update<'a>(
113        &self,
114        assignments: impl IntoIterator<Item = (&'a str, bool)>,
115    ) -> Result<(), SQLError> {
116        let assigned = assignments
117            .into_iter()
118            .filter_map(|(column, default)| (!default).then_some(column))
119            .collect::<std::collections::BTreeSet<_>>();
120        for (column, generation) in &self.columns {
121            if !assigned.contains(column.as_str()) {
122                continue;
123            }
124            match generation {
125                Generation::Expression => return Err(generated_column_update_error(column)),
126                Generation::IdentityAlways => return Err(generated_always_update_error(column)),
127                Generation::IdentityByDefault => {}
128            }
129        }
130        Ok(())
131    }
132}
133
134/// The `428C9` error of a value an `INSERT` supplies for a `GENERATED ALWAYS` identity column.
135#[must_use]
136pub fn generated_always_insert_error(column: &str) -> SQLError {
137    SQLError::Diagnostic {
138        sqlstate: "428C9".into(),
139        message: format!("cannot insert a non-DEFAULT value into column \"{column}\""),
140        detail: Some(format!(
141            "Column \"{column}\" is an identity column defined as GENERATED ALWAYS."
142        )),
143        hint: Some("Use OVERRIDING SYSTEM VALUE to override.".into()),
144    }
145}
146
147/// The `428C9` error of an assignment other than `DEFAULT` to a `GENERATED ALWAYS` identity column.
148#[must_use]
149pub fn generated_always_update_error(column: &str) -> SQLError {
150    SQLError::Diagnostic {
151        sqlstate: "428C9".into(),
152        message: format!("column \"{column}\" can only be updated to DEFAULT"),
153        detail: Some(format!(
154            "Column \"{column}\" is an identity column defined as GENERATED ALWAYS."
155        )),
156        hint: None,
157    }
158}
159
160/// The `428C9` error of a value an `INSERT` supplies for a generated column.
161#[must_use]
162pub fn generated_column_insert_error(column: &str) -> SQLError {
163    SQLError::Diagnostic {
164        sqlstate: "428C9".into(),
165        message: format!("cannot insert a non-DEFAULT value into column \"{column}\""),
166        detail: Some(format!("Column \"{column}\" is a generated column.")),
167        hint: None,
168    }
169}
170
171/// The `428C9` error of an assignment other than `DEFAULT` to a generated column.
172#[must_use]
173pub fn generated_column_update_error(column: &str) -> SQLError {
174    SQLError::Diagnostic {
175        sqlstate: "428C9".into(),
176        message: format!("column \"{column}\" can only be updated to DEFAULT"),
177        detail: Some(format!("Column \"{column}\" is a generated column.")),
178        hint: None,
179    }
180}
181
182#[cfg(test)]
183mod tests {
184    #[test]
185    fn stored_update_selection_follows_expression_inputs() {
186        let crate::Statement::CreateTable(table) = crate::compile("CREATE TABLE t(a int,b int,unrelated int,ga int GENERATED ALWAYS AS(coalesce(a,1)) STORED,gb int GENERATED ALWAYS AS(b+2) STORED,c int GENERATED ALWAYS AS(42) STORED,v int GENERATED ALWAYS AS(b+3) VIRTUAL)").unwrap().remove(0) else { panic!("table") };
187        for (targets, expected) in [
188            (vec!["a"], vec!["ga"]),
189            (vec!["b"], vec!["gb"]),
190            (vec!["a", "b"], vec!["ga", "gb"]),
191            (vec!["unrelated"], vec![]),
192            (vec!["ga", "c", "v"], vec![]),
193        ] {
194            let targets = targets.into_iter().map(str::to_owned).collect::<Vec<_>>();
195            assert_eq!(
196                super::stored_columns_for_update(&table.columns, &targets),
197                expected
198            );
199        }
200    }
201}
uqa_sql/semantics/generated_values.rs

uqa_sql/semantics/
generated_values.rs
