This is an automated email from the ASF dual-hosted git repository. github-bot pushed a commit to branch gh-readonly-queue/main/pr-2127-0cf85d3b3d84596f4f773a2d23fbee2661fda23c in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
commit 4de1ac95a4168b60c8487a888e93123d45d92bef Author: Filipe Guerreiro <[email protected]> AuthorDate: Tue Jan 6 20:12:56 2026 +0900 Add PostgreSQL PARTITION OF syntax support (#2127) --- src/ast/ddl.rs | 119 ++++++++++++- src/ast/helpers/stmt_create_table.rs | 24 ++- src/ast/mod.rs | 74 +++++++- src/ast/spans.rs | 62 +++++-- src/keywords.rs | 2 + src/parser/mod.rs | 126 ++++++++++++++ tests/sqlparser_bigquery.rs | 2 + tests/sqlparser_duckdb.rs | 2 + tests/sqlparser_mssql.rs | 8 + tests/sqlparser_postgres.rs | 327 +++++++++++++++++++++++++++++++++++ 10 files changed, 712 insertions(+), 34 deletions(-) diff --git a/src/ast/ddl.rs b/src/ast/ddl.rs index 4e042a36..2a24741f 100644 --- a/src/ast/ddl.rs +++ b/src/ast/ddl.rs @@ -43,13 +43,14 @@ use crate::ast::{ }, ArgMode, AttachedToken, CommentDef, ConditionalStatements, CreateFunctionBody, CreateFunctionUsing, CreateTableLikeKind, CreateTableOptions, CreateViewParams, DataType, Expr, - FileFormat, FunctionBehavior, FunctionCalledOnNull, FunctionDesc, FunctionDeterminismSpecifier, - FunctionParallel, HiveDistributionStyle, HiveFormat, HiveIOFormat, HiveRowFormat, - HiveSetLocation, Ident, InitializeKind, MySQLColumnPosition, ObjectName, OnCommit, - OneOrManyWithParens, OperateFunctionArg, OrderByExpr, ProjectionSelect, Query, RefreshModeKind, - RowAccessPolicy, SequenceOptions, Spanned, SqlOption, StorageSerializationPolicy, TableVersion, - Tag, TriggerEvent, TriggerExecBody, TriggerObject, TriggerPeriod, TriggerReferencing, Value, - ValueWithSpan, WrappedCollection, + FileFormat, FunctionBehavior, FunctionCalledOnNull, FunctionDefinitionSetParam, FunctionDesc, + FunctionDeterminismSpecifier, FunctionParallel, FunctionSecurity, HiveDistributionStyle, + HiveFormat, HiveIOFormat, HiveRowFormat, HiveSetLocation, Ident, InitializeKind, + MySQLColumnPosition, ObjectName, OnCommit, OneOrManyWithParens, OperateFunctionArg, + OrderByExpr, ProjectionSelect, Query, RefreshModeKind, RowAccessPolicy, SequenceOptions, + Spanned, SqlOption, StorageSerializationPolicy, TableVersion, Tag, TriggerEvent, + TriggerExecBody, TriggerObject, TriggerPeriod, TriggerReferencing, Value, ValueWithSpan, + WrappedCollection, }; use crate::display_utils::{DisplayCommaSeparated, Indent, NewLine, SpaceOrNewline}; use crate::keywords::Keyword; @@ -2697,6 +2698,14 @@ pub struct CreateTable { /// <https://www.postgresql.org/docs/current/ddl-inherit.html> /// <https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-INHERITS> pub inherits: Option<Vec<ObjectName>>, + /// PostgreSQL `PARTITION OF` clause to create a partition of a parent table. + /// Contains the parent table name. + /// <https://www.postgresql.org/docs/current/sql-createtable.html> + #[cfg_attr(feature = "visitor", visit(with = "visit_relation"))] + pub partition_of: Option<ObjectName>, + /// PostgreSQL partition bound specification for PARTITION OF. + /// <https://www.postgresql.org/docs/current/sql-createtable.html> + pub for_values: Option<ForValues>, /// SQLite "STRICT" clause. /// if the "STRICT" table-option keyword is added to the end, after the closing ")", /// then strict typing rules apply to that table. @@ -2792,6 +2801,9 @@ impl fmt::Display for CreateTable { dynamic = if self.dynamic { "DYNAMIC " } else { "" }, name = self.name, )?; + if let Some(partition_of) = &self.partition_of { + write!(f, " PARTITION OF {partition_of}")?; + } if let Some(on_cluster) = &self.on_cluster { write!(f, " ON CLUSTER {on_cluster}")?; } @@ -2806,12 +2818,19 @@ impl fmt::Display for CreateTable { Indent(DisplayCommaSeparated(&self.constraints)).fmt(f)?; NewLine.fmt(f)?; f.write_str(")")?; - } else if self.query.is_none() && self.like.is_none() && self.clone.is_none() { + } else if self.query.is_none() + && self.like.is_none() + && self.clone.is_none() + && self.partition_of.is_none() + { // PostgreSQL allows `CREATE TABLE t ();`, but requires empty parens f.write_str(" ()")?; } else if let Some(CreateTableLikeKind::Parenthesized(like_in_columns_list)) = &self.like { write!(f, " ({like_in_columns_list})")?; } + if let Some(for_values) = &self.for_values { + write!(f, " {for_values}")?; + } // Hive table comment should be after column definitions, please refer to: // [Hive](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable) @@ -3053,6 +3072,76 @@ impl fmt::Display for CreateTable { } } +/// PostgreSQL partition bound specification for `PARTITION OF`. +/// +/// Specifies partition bounds for a child partition table. +/// +/// See [PostgreSQL](https://www.postgresql.org/docs/current/sql-createtable.html) +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub enum ForValues { + /// `FOR VALUES IN (expr, ...)` + In(Vec<Expr>), + /// `FOR VALUES FROM (expr|MINVALUE|MAXVALUE, ...) TO (expr|MINVALUE|MAXVALUE, ...)` + From { + from: Vec<PartitionBoundValue>, + to: Vec<PartitionBoundValue>, + }, + /// `FOR VALUES WITH (MODULUS n, REMAINDER r)` + With { modulus: u64, remainder: u64 }, + /// `DEFAULT` + Default, +} + +impl fmt::Display for ForValues { + fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result { + match self { + ForValues::In(values) => { + write!(f, "FOR VALUES IN ({})", display_comma_separated(values)) + } + ForValues::From { from, to } => { + write!( + f, + "FOR VALUES FROM ({}) TO ({})", + display_comma_separated(from), + display_comma_separated(to) + ) + } + ForValues::With { modulus, remainder } => { + write!( + f, + "FOR VALUES WITH (MODULUS {modulus}, REMAINDER {remainder})" + ) + } + ForValues::Default => write!(f, "DEFAULT"), + } + } +} + +/// A value in a partition bound specification. +/// +/// Used in RANGE partition bounds where values can be expressions, +/// MINVALUE (negative infinity), or MAXVALUE (positive infinity). +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub enum PartitionBoundValue { + Expr(Expr), + MinValue, + MaxValue, +} + +impl fmt::Display for PartitionBoundValue { + fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result { + match self { + PartitionBoundValue::Expr(expr) => write!(f, "{expr}"), + PartitionBoundValue::MinValue => write!(f, "MINVALUE"), + PartitionBoundValue::MaxValue => write!(f, "MAXVALUE"), + } + } +} + #[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] #[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] #[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] @@ -3138,6 +3227,14 @@ pub struct CreateFunction { /// /// [PostgreSQL](https://www.postgresql.org/docs/current/sql-createfunction.html) pub parallel: Option<FunctionParallel>, + /// SECURITY { DEFINER | INVOKER } + /// + /// [PostgreSQL](https://www.postgresql.org/docs/current/sql-createfunction.html) + pub security: Option<FunctionSecurity>, + /// SET configuration_parameter clauses + /// + /// [PostgreSQL](https://www.postgresql.org/docs/current/sql-createfunction.html) + pub set_params: Vec<FunctionDefinitionSetParam>, /// USING ... (Hive only) pub using: Option<CreateFunctionUsing>, /// Language used in a UDF definition. @@ -3204,6 +3301,12 @@ impl fmt::Display for CreateFunction { if let Some(parallel) = &self.parallel { write!(f, " {parallel}")?; } + if let Some(security) = &self.security { + write!(f, " {security}")?; + } + for set_param in &self.set_params { + write!(f, " {set_param}")?; + } if let Some(remote_connection) = &self.remote_connection { write!(f, " REMOTE WITH CONNECTION {remote_connection}")?; } diff --git a/src/ast/helpers/stmt_create_table.rs b/src/ast/helpers/stmt_create_table.rs index fe950c90..62dbbbcb 100644 --- a/src/ast/helpers/stmt_create_table.rs +++ b/src/ast/helpers/stmt_create_table.rs @@ -26,8 +26,8 @@ use sqlparser_derive::{Visit, VisitMut}; use crate::ast::{ ClusteredBy, ColumnDef, CommentDef, CreateTable, CreateTableLikeKind, CreateTableOptions, Expr, - FileFormat, HiveDistributionStyle, HiveFormat, Ident, InitializeKind, ObjectName, OnCommit, - OneOrManyWithParens, Query, RefreshModeKind, RowAccessPolicy, Statement, + FileFormat, ForValues, HiveDistributionStyle, HiveFormat, Ident, InitializeKind, ObjectName, + OnCommit, OneOrManyWithParens, Query, RefreshModeKind, RowAccessPolicy, Statement, StorageSerializationPolicy, TableConstraint, TableVersion, Tag, WrappedCollection, }; @@ -94,6 +94,8 @@ pub struct CreateTableBuilder { pub cluster_by: Option<WrappedCollection<Vec<Expr>>>, pub clustered_by: Option<ClusteredBy>, pub inherits: Option<Vec<ObjectName>>, + pub partition_of: Option<ObjectName>, + pub for_values: Option<ForValues>, pub strict: bool, pub copy_grants: bool, pub enable_schema_evolution: Option<bool>, @@ -150,6 +152,8 @@ impl CreateTableBuilder { cluster_by: None, clustered_by: None, inherits: None, + partition_of: None, + for_values: None, strict: false, copy_grants: false, enable_schema_evolution: None, @@ -317,6 +321,16 @@ impl CreateTableBuilder { self } + pub fn partition_of(mut self, partition_of: Option<ObjectName>) -> Self { + self.partition_of = partition_of; + self + } + + pub fn for_values(mut self, for_values: Option<ForValues>) -> Self { + self.for_values = for_values; + self + } + pub fn strict(mut self, strict: bool) -> Self { self.strict = strict; self @@ -463,6 +477,8 @@ impl CreateTableBuilder { cluster_by: self.cluster_by, clustered_by: self.clustered_by, inherits: self.inherits, + partition_of: self.partition_of, + for_values: self.for_values, strict: self.strict, copy_grants: self.copy_grants, enable_schema_evolution: self.enable_schema_evolution, @@ -527,6 +543,8 @@ impl TryFrom<Statement> for CreateTableBuilder { cluster_by, clustered_by, inherits, + partition_of, + for_values, strict, copy_grants, enable_schema_evolution, @@ -577,6 +595,8 @@ impl TryFrom<Statement> for CreateTableBuilder { cluster_by, clustered_by, inherits, + partition_of, + for_values, strict, iceberg, copy_grants, diff --git a/src/ast/mod.rs b/src/ast/mod.rs index 46767860..c8d9c6be 100644 --- a/src/ast/mod.rs +++ b/src/ast/mod.rs @@ -69,15 +69,15 @@ pub use self::ddl::{ CreateExtension, CreateFunction, CreateIndex, CreateOperator, CreateOperatorClass, CreateOperatorFamily, CreateTable, CreateTrigger, CreateView, Deduplicate, DeferrableInitial, DropBehavior, DropExtension, DropFunction, DropOperator, DropOperatorClass, DropOperatorFamily, - DropOperatorSignature, DropTrigger, GeneratedAs, GeneratedExpressionMode, IdentityParameters, - IdentityProperty, IdentityPropertyFormatKind, IdentityPropertyKind, IdentityPropertyOrder, - IndexColumn, IndexOption, IndexType, KeyOrIndexDisplay, Msck, NullsDistinctOption, - OperatorArgTypes, OperatorClassItem, OperatorFamilyDropItem, OperatorFamilyItem, - OperatorOption, OperatorPurpose, Owner, Partition, ProcedureParam, ReferentialAction, - RenameTableNameKind, ReplicaIdentity, TagsColumnOption, TriggerObjectKind, Truncate, - UserDefinedTypeCompositeAttributeDef, UserDefinedTypeInternalLength, - UserDefinedTypeRangeOption, UserDefinedTypeRepresentation, UserDefinedTypeSqlDefinitionOption, - UserDefinedTypeStorage, ViewColumnDef, + DropOperatorSignature, DropTrigger, ForValues, GeneratedAs, GeneratedExpressionMode, + IdentityParameters, IdentityProperty, IdentityPropertyFormatKind, IdentityPropertyKind, + IdentityPropertyOrder, IndexColumn, IndexOption, IndexType, KeyOrIndexDisplay, Msck, + NullsDistinctOption, OperatorArgTypes, OperatorClassItem, OperatorFamilyDropItem, + OperatorFamilyItem, OperatorOption, OperatorPurpose, Owner, Partition, PartitionBoundValue, + ProcedureParam, ReferentialAction, RenameTableNameKind, ReplicaIdentity, TagsColumnOption, + TriggerObjectKind, Truncate, UserDefinedTypeCompositeAttributeDef, + UserDefinedTypeInternalLength, UserDefinedTypeRangeOption, UserDefinedTypeRepresentation, + UserDefinedTypeSqlDefinitionOption, UserDefinedTypeStorage, ViewColumnDef, }; pub use self::dml::{ Delete, Insert, Merge, MergeAction, MergeClause, MergeClauseKind, MergeInsertExpr, @@ -8781,6 +8781,62 @@ impl fmt::Display for FunctionBehavior { } } +/// Security attribute for functions: SECURITY DEFINER or SECURITY INVOKER. +/// +/// [PostgreSQL](https://www.postgresql.org/docs/current/sql-createfunction.html) +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub enum FunctionSecurity { + Definer, + Invoker, +} + +impl fmt::Display for FunctionSecurity { + fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result { + match self { + FunctionSecurity::Definer => write!(f, "SECURITY DEFINER"), + FunctionSecurity::Invoker => write!(f, "SECURITY INVOKER"), + } + } +} + +/// Value for a SET configuration parameter in a CREATE FUNCTION statement. +/// +/// [PostgreSQL](https://www.postgresql.org/docs/current/sql-createfunction.html) +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub enum FunctionSetValue { + /// SET param = value1, value2, ... + Values(Vec<Expr>), + /// SET param FROM CURRENT + FromCurrent, +} + +/// A SET configuration_parameter clause in a CREATE FUNCTION statement. +/// +/// [PostgreSQL](https://www.postgresql.org/docs/current/sql-createfunction.html) +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub struct FunctionDefinitionSetParam { + pub name: Ident, + pub value: FunctionSetValue, +} + +impl fmt::Display for FunctionDefinitionSetParam { + fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result { + write!(f, "SET {} ", self.name)?; + match &self.value { + FunctionSetValue::Values(values) => { + write!(f, "= {}", display_comma_separated(values)) + } + FunctionSetValue::FromCurrent => write!(f, "FROM CURRENT"), + } + } +} + /// These attributes describe the behavior of the function when called with a null argument. #[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] #[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] diff --git a/src/ast/spans.rs b/src/ast/spans.rs index d4e84315..f88b3029 100644 --- a/src/ast/spans.rs +++ b/src/ast/spans.rs @@ -34,19 +34,20 @@ use super::{ ColumnOption, ColumnOptionDef, ConditionalStatementBlock, ConditionalStatements, ConflictTarget, ConnectBy, ConstraintCharacteristics, CopySource, CreateIndex, CreateTable, CreateTableOptions, Cte, Delete, DoUpdate, ExceptSelectItem, ExcludeSelectItem, Expr, - ExprWithAlias, Fetch, FromTable, Function, FunctionArg, FunctionArgExpr, + ExprWithAlias, Fetch, ForValues, FromTable, Function, FunctionArg, FunctionArgExpr, FunctionArgumentClause, FunctionArgumentList, FunctionArguments, GroupByExpr, HavingBound, IfStatement, IlikeSelectItem, IndexColumn, Insert, Interpolate, InterpolateExpr, Join, JoinConstraint, JoinOperator, JsonPath, JsonPathElem, LateralView, LimitClause, MatchRecognizePattern, Measure, Merge, MergeAction, MergeClause, MergeInsertExpr, MergeInsertKind, MergeUpdateExpr, NamedParenthesizedList, NamedWindowDefinition, ObjectName, ObjectNamePart, Offset, OnConflict, OnConflictAction, OnInsert, OpenStatement, OrderBy, - OrderByExpr, OrderByKind, OutputClause, Partition, PivotValueSource, ProjectionSelect, Query, - RaiseStatement, RaiseStatementValue, ReferentialAction, RenameSelectItem, ReplaceSelectElement, - ReplaceSelectItem, Select, SelectInto, SelectItem, SetExpr, SqlOption, Statement, Subscript, - SymbolDefinition, TableAlias, TableAliasColumnDef, TableConstraint, TableFactor, TableObject, - TableOptionsClustered, TableWithJoins, Update, UpdateTableFromKind, Use, Value, Values, - ViewColumnDef, WhileStatement, WildcardAdditionalOptions, With, WithFill, + OrderByExpr, OrderByKind, OutputClause, Partition, PartitionBoundValue, PivotValueSource, + ProjectionSelect, Query, RaiseStatement, RaiseStatementValue, ReferentialAction, + RenameSelectItem, ReplaceSelectElement, ReplaceSelectItem, Select, SelectInto, SelectItem, + SetExpr, SqlOption, Statement, Subscript, SymbolDefinition, TableAlias, TableAliasColumnDef, + TableConstraint, TableFactor, TableObject, TableOptionsClustered, TableWithJoins, Update, + UpdateTableFromKind, Use, Value, Values, ViewColumnDef, WhileStatement, + WildcardAdditionalOptions, With, WithFill, }; /// Given an iterator of spans, return the [Span::union] of all spans. @@ -547,13 +548,15 @@ impl Spanned for CreateTable { clone, comment: _, // todo, no span on_commit: _, - on_cluster: _, // todo, clickhouse specific - primary_key: _, // todo, clickhouse specific - order_by: _, // todo, clickhouse specific - partition_by: _, // todo, BigQuery specific - cluster_by: _, // todo, BigQuery specific - clustered_by: _, // todo, Hive specific - inherits: _, // todo, PostgreSQL specific + on_cluster: _, // todo, clickhouse specific + primary_key: _, // todo, clickhouse specific + order_by: _, // todo, clickhouse specific + partition_by: _, // todo, BigQuery specific + cluster_by: _, // todo, BigQuery specific + clustered_by: _, // todo, Hive specific + inherits: _, // todo, PostgreSQL specific + partition_of, + for_values, strict: _, // bool copy_grants: _, // bool enable_schema_evolution: _, // bool @@ -584,7 +587,9 @@ impl Spanned for CreateTable { .chain(columns.iter().map(|i| i.span())) .chain(constraints.iter().map(|i| i.span())) .chain(query.iter().map(|i| i.span())) - .chain(clone.iter().map(|i| i.span())), + .chain(clone.iter().map(|i| i.span())) + .chain(partition_of.iter().map(|i| i.span())) + .chain(for_values.iter().map(|i| i.span())), ) } } @@ -622,6 +627,33 @@ impl Spanned for TableConstraint { } } +impl Spanned for PartitionBoundValue { + fn span(&self) -> Span { + match self { + PartitionBoundValue::Expr(expr) => expr.span(), + // MINVALUE and MAXVALUE are keywords without tracked spans + PartitionBoundValue::MinValue => Span::empty(), + PartitionBoundValue::MaxValue => Span::empty(), + } + } +} + +impl Spanned for ForValues { + fn span(&self) -> Span { + match self { + ForValues::In(exprs) => union_spans(exprs.iter().map(|e| e.span())), + ForValues::From { from, to } => union_spans( + from.iter() + .map(|v| v.span()) + .chain(to.iter().map(|v| v.span())), + ), + // WITH (MODULUS n, REMAINDER r) - u64 values have no spans + ForValues::With { .. } => Span::empty(), + ForValues::Default => Span::empty(), + } + } +} + impl Spanned for CreateIndex { fn span(&self) -> Span { let CreateIndex { diff --git a/src/keywords.rs b/src/keywords.rs index f06842ec..87c77379 100644 --- a/src/keywords.rs +++ b/src/keywords.rs @@ -637,6 +637,7 @@ define_keywords!( MODIFIES, MODIFY, MODULE, + MODULUS, MONITOR, MONTH, MONTHS, @@ -837,6 +838,7 @@ define_keywords!( RELAY, RELEASE, RELEASES, + REMAINDER, REMOTE, REMOVE, REMOVEQUOTES, diff --git a/src/parser/mod.rs b/src/parser/mod.rs index f07e8919..373076f1 100644 --- a/src/parser/mod.rs +++ b/src/parser/mod.rs @@ -5260,8 +5260,10 @@ impl<'a> Parser<'a> { function_body: Option<CreateFunctionBody>, called_on_null: Option<FunctionCalledOnNull>, parallel: Option<FunctionParallel>, + security: Option<FunctionSecurity>, } let mut body = Body::default(); + let mut set_params: Vec<FunctionDefinitionSetParam> = Vec::new(); loop { fn ensure_not_set<T>(field: &Option<T>, name: &str) -> Result<(), ParserError> { if field.is_some() { @@ -5326,6 +5328,27 @@ impl<'a> Parser<'a> { } else { return self.expected("one of UNSAFE | RESTRICTED | SAFE", self.peek_token()); } + } else if self.parse_keyword(Keyword::SECURITY) { + ensure_not_set(&body.security, "SECURITY { DEFINER | INVOKER }")?; + if self.parse_keyword(Keyword::DEFINER) { + body.security = Some(FunctionSecurity::Definer); + } else if self.parse_keyword(Keyword::INVOKER) { + body.security = Some(FunctionSecurity::Invoker); + } else { + return self.expected("DEFINER or INVOKER", self.peek_token()); + } + } else if self.parse_keyword(Keyword::SET) { + let name = self.parse_identifier()?; + let value = if self.parse_keywords(&[Keyword::FROM, Keyword::CURRENT]) { + FunctionSetValue::FromCurrent + } else { + if !self.consume_token(&Token::Eq) && !self.parse_keyword(Keyword::TO) { + return self.expected("= or TO", self.peek_token()); + } + let values = self.parse_comma_separated(Parser::parse_expr)?; + FunctionSetValue::Values(values) + }; + set_params.push(FunctionDefinitionSetParam { name, value }); } else if self.parse_keyword(Keyword::RETURN) { ensure_not_set(&body.function_body, "RETURN")?; body.function_body = Some(CreateFunctionBody::Return(self.parse_expr()?)); @@ -5344,6 +5367,8 @@ impl<'a> Parser<'a> { behavior: body.behavior, called_on_null: body.called_on_null, parallel: body.parallel, + security: body.security, + set_params, language: body.language, function_body: body.function_body, if_not_exists: false, @@ -5381,6 +5406,8 @@ impl<'a> Parser<'a> { behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], language: None, determinism_specifier: None, options: None, @@ -5463,6 +5490,8 @@ impl<'a> Parser<'a> { behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], })) } @@ -5552,6 +5581,8 @@ impl<'a> Parser<'a> { behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], })) } @@ -7887,6 +7918,22 @@ impl<'a> Parser<'a> { let if_not_exists = self.parse_keywords(&[Keyword::IF, Keyword::NOT, Keyword::EXISTS]); let table_name = self.parse_object_name(allow_unquoted_hyphen)?; + // PostgreSQL PARTITION OF for child partition tables + // Note: This is a PostgreSQL-specific feature, but the dialect check was intentionally + // removed to allow GenericDialect and other dialects to parse this syntax. This enables + // multi-dialect SQL tools to work with PostgreSQL-specific DDL statements. + // + // PARTITION OF can be combined with other table definition clauses in the AST, + // though PostgreSQL itself prohibits PARTITION OF with AS SELECT or LIKE clauses. + // The parser accepts these combinations for flexibility; semantic validation + // is left to downstream tools. + // Child partitions can have their own constraints and indexes. + let partition_of = if self.parse_keywords(&[Keyword::PARTITION, Keyword::OF]) { + Some(self.parse_object_name(allow_unquoted_hyphen)?) + } else { + None + }; + // Clickhouse has `ON CLUSTER 'cluster'` syntax for DDLs let on_cluster = self.parse_optional_on_cluster()?; @@ -7911,6 +7958,20 @@ impl<'a> Parser<'a> { None }; + // PostgreSQL PARTITION OF: partition bound specification + let for_values = if partition_of.is_some() { + if self.peek_keyword(Keyword::FOR) || self.peek_keyword(Keyword::DEFAULT) { + Some(self.parse_partition_for_values()?) + } else { + return self.expected( + "FOR VALUES or DEFAULT after PARTITION OF", + self.peek_token(), + ); + } + } else { + None + }; + // SQLite supports `WITHOUT ROWID` at the end of `CREATE TABLE` let without_rowid = self.parse_keywords(&[Keyword::WITHOUT, Keyword::ROWID]); @@ -7988,6 +8049,8 @@ impl<'a> Parser<'a> { .partition_by(create_table_config.partition_by) .cluster_by(create_table_config.cluster_by) .inherits(create_table_config.inherits) + .partition_of(partition_of) + .for_values(for_values) .table_options(create_table_config.table_options) .primary_key(primary_key) .strict(strict) @@ -8047,6 +8110,69 @@ impl<'a> Parser<'a> { } } + /// Parse [ForValues] of a `PARTITION OF` clause. + /// + /// Parses: `FOR VALUES partition_bound_spec | DEFAULT` + /// + /// [PostgreSQL](https://www.postgresql.org/docs/current/sql-createtable.html) + fn parse_partition_for_values(&mut self) -> Result<ForValues, ParserError> { + if self.parse_keyword(Keyword::DEFAULT) { + return Ok(ForValues::Default); + } + + self.expect_keywords(&[Keyword::FOR, Keyword::VALUES])?; + + if self.parse_keyword(Keyword::IN) { + // FOR VALUES IN (expr, ...) + self.expect_token(&Token::LParen)?; + if self.peek_token() == Token::RParen { + return self.expected("at least one value", self.peek_token()); + } + let values = self.parse_comma_separated(Parser::parse_expr)?; + self.expect_token(&Token::RParen)?; + Ok(ForValues::In(values)) + } else if self.parse_keyword(Keyword::FROM) { + // FOR VALUES FROM (...) TO (...) + self.expect_token(&Token::LParen)?; + if self.peek_token() == Token::RParen { + return self.expected("at least one value", self.peek_token()); + } + let from = self.parse_comma_separated(Parser::parse_partition_bound_value)?; + self.expect_token(&Token::RParen)?; + self.expect_keyword(Keyword::TO)?; + self.expect_token(&Token::LParen)?; + if self.peek_token() == Token::RParen { + return self.expected("at least one value", self.peek_token()); + } + let to = self.parse_comma_separated(Parser::parse_partition_bound_value)?; + self.expect_token(&Token::RParen)?; + Ok(ForValues::From { from, to }) + } else if self.parse_keyword(Keyword::WITH) { + // FOR VALUES WITH (MODULUS n, REMAINDER r) + self.expect_token(&Token::LParen)?; + self.expect_keyword(Keyword::MODULUS)?; + let modulus = self.parse_literal_uint()?; + self.expect_token(&Token::Comma)?; + self.expect_keyword(Keyword::REMAINDER)?; + let remainder = self.parse_literal_uint()?; + self.expect_token(&Token::RParen)?; + Ok(ForValues::With { modulus, remainder }) + } else { + self.expected("IN, FROM, or WITH after FOR VALUES", self.peek_token()) + } + } + + /// Parse a single [PartitionBoundValue]. + fn parse_partition_bound_value(&mut self) -> Result<PartitionBoundValue, ParserError> { + if self.parse_keyword(Keyword::MINVALUE) { + Ok(PartitionBoundValue::MinValue) + } else if self.parse_keyword(Keyword::MAXVALUE) { + Ok(PartitionBoundValue::MaxValue) + } else { + Ok(PartitionBoundValue::Expr(self.parse_expr()?)) + } + } + /// Parse configuration like inheritance, partitioning, clustering information during the table creation. /// /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#syntax_2) diff --git a/tests/sqlparser_bigquery.rs b/tests/sqlparser_bigquery.rs index 24b9efca..2bdeba91 100644 --- a/tests/sqlparser_bigquery.rs +++ b/tests/sqlparser_bigquery.rs @@ -2294,6 +2294,8 @@ fn test_bigquery_create_function() { remote_connection: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], }) ); diff --git a/tests/sqlparser_duckdb.rs b/tests/sqlparser_duckdb.rs index 73a1afe2..4a2f29e1 100644 --- a/tests/sqlparser_duckdb.rs +++ b/tests/sqlparser_duckdb.rs @@ -755,6 +755,8 @@ fn test_duckdb_union_datatype() { cluster_by: Default::default(), clustered_by: Default::default(), inherits: Default::default(), + partition_of: Default::default(), + for_values: Default::default(), strict: Default::default(), copy_grants: Default::default(), enable_schema_evolution: Default::default(), diff --git a/tests/sqlparser_mssql.rs b/tests/sqlparser_mssql.rs index 70e0aab4..1927b864 100644 --- a/tests/sqlparser_mssql.rs +++ b/tests/sqlparser_mssql.rs @@ -266,6 +266,8 @@ fn parse_create_function() { behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], using: None, language: None, determinism_specifier: None, @@ -439,6 +441,8 @@ fn parse_create_function_parameter_default_values() { behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], using: None, language: None, determinism_specifier: None, @@ -1897,6 +1901,8 @@ fn parse_create_table_with_valid_options() { cluster_by: None, clustered_by: None, inherits: None, + partition_of: None, + for_values: None, strict: false, iceberg: false, copy_grants: false, @@ -2064,6 +2070,8 @@ fn parse_create_table_with_identity_column() { cluster_by: None, clustered_by: None, inherits: None, + partition_of: None, + for_values: None, strict: false, copy_grants: false, enable_schema_evolution: None, diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs index 70f27a13..24707604 100644 --- a/tests/sqlparser_postgres.rs +++ b/tests/sqlparser_postgres.rs @@ -4294,6 +4294,8 @@ $$"#; behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], function_body: Some(CreateFunctionBody::AsBeforeOptions { body: Expr::Value( (Value::DollarQuotedString(DollarQuotedString {value: "\nBEGIN\n IF str1 <> str2 THEN\n RETURN TRUE;\n ELSE\n RETURN FALSE;\n END IF;\nEND;\n".to_owned(), tag: None})).with_empty_span() @@ -4335,6 +4337,8 @@ $$"#; behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], function_body: Some(CreateFunctionBody::AsBeforeOptions { body: Expr::Value( (Value::DollarQuotedString(DollarQuotedString {value: "\nBEGIN\n IF int1 <> 0 THEN\n RETURN TRUE;\n ELSE\n RETURN FALSE;\n END IF;\nEND;\n".to_owned(), tag: None})).with_empty_span() @@ -4380,6 +4384,8 @@ $$"#; behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], function_body: Some(CreateFunctionBody::AsBeforeOptions { body: Expr::Value( (Value::DollarQuotedString(DollarQuotedString {value: "\nBEGIN\n IF a <> b THEN\n RETURN TRUE;\n ELSE\n RETURN FALSE;\n END IF;\nEND;\n".to_owned(), tag: None})).with_empty_span() @@ -4425,6 +4431,8 @@ $$"#; behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], function_body: Some(CreateFunctionBody::AsBeforeOptions { body: Expr::Value( (Value::DollarQuotedString(DollarQuotedString {value: "\nBEGIN\n IF int1 <> int2 THEN\n RETURN TRUE;\n ELSE\n RETURN FALSE;\n END IF;\nEND;\n".to_owned(), tag: None})).with_empty_span() @@ -4463,6 +4471,8 @@ $$"#; behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], function_body: Some(CreateFunctionBody::AsBeforeOptions { body: Expr::Value( (Value::DollarQuotedString(DollarQuotedString { @@ -4504,6 +4514,8 @@ fn parse_create_function() { behavior: Some(FunctionBehavior::Immutable), called_on_null: Some(FunctionCalledOnNull::Strict), parallel: Some(FunctionParallel::Safe), + security: None, + set_params: vec![], function_body: Some(CreateFunctionBody::AsBeforeOptions { body: Expr::Value( (Value::SingleQuotedString("select $1 + $2;".into())).with_empty_span() @@ -4534,6 +4546,61 @@ fn parse_create_function_detailed() { ); } +#[test] +fn parse_create_function_with_security() { + let sql = + "CREATE FUNCTION test_fn() RETURNS void LANGUAGE sql SECURITY DEFINER AS $$ SELECT 1 $$"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateFunction(CreateFunction { security, .. }) => { + assert_eq!(security, Some(FunctionSecurity::Definer)); + } + _ => panic!("Expected CreateFunction"), + } + + let sql2 = + "CREATE FUNCTION test_fn() RETURNS void LANGUAGE sql SECURITY INVOKER AS $$ SELECT 1 $$"; + match pg_and_generic().verified_stmt(sql2) { + Statement::CreateFunction(CreateFunction { security, .. }) => { + assert_eq!(security, Some(FunctionSecurity::Invoker)); + } + _ => panic!("Expected CreateFunction"), + } +} + +#[test] +fn parse_create_function_with_set_params() { + let sql = + "CREATE FUNCTION test_fn() RETURNS void LANGUAGE sql SET search_path = auth, pg_temp, public AS $$ SELECT 1 $$"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateFunction(CreateFunction { set_params, .. }) => { + assert_eq!(set_params.len(), 1); + assert_eq!(set_params[0].name.to_string(), "search_path"); + } + _ => panic!("Expected CreateFunction"), + } + + // Test multiple SET params + let sql2 = + "CREATE FUNCTION test_fn() RETURNS void LANGUAGE sql SET search_path = public SET statement_timeout = '5s' AS $$ SELECT 1 $$"; + match pg_and_generic().verified_stmt(sql2) { + Statement::CreateFunction(CreateFunction { set_params, .. }) => { + assert_eq!(set_params.len(), 2); + } + _ => panic!("Expected CreateFunction"), + } + + // Test FROM CURRENT + let sql3 = + "CREATE FUNCTION test_fn() RETURNS void LANGUAGE sql SET search_path FROM CURRENT AS $$ SELECT 1 $$"; + match pg_and_generic().verified_stmt(sql3) { + Statement::CreateFunction(CreateFunction { set_params, .. }) => { + assert_eq!(set_params.len(), 1); + assert!(matches!(set_params[0].value, FunctionSetValue::FromCurrent)); + } + _ => panic!("Expected CreateFunction"), + } +} + #[test] fn parse_incorrect_create_function_parallel() { let sql = "CREATE FUNCTION add(INTEGER, INTEGER) RETURNS INTEGER LANGUAGE SQL PARALLEL BLAH AS 'select $1 + $2;'"; @@ -4562,6 +4629,8 @@ fn parse_create_function_c_with_module_pathname() { behavior: Some(FunctionBehavior::Immutable), called_on_null: None, parallel: Some(FunctionParallel::Safe), + security: None, + set_params: vec![], function_body: Some(CreateFunctionBody::AsBeforeOptions { body: Expr::Value( (Value::SingleQuotedString("MODULE_PATHNAME".into())).with_empty_span() @@ -6130,6 +6199,8 @@ fn parse_trigger_related_functions() { cluster_by: None, clustered_by: None, inherits: None, + partition_of: None, + for_values: None, strict: false, copy_grants: false, enable_schema_evolution: None, @@ -6185,6 +6256,8 @@ fn parse_trigger_related_functions() { behavior: None, called_on_null: None, parallel: None, + security: None, + set_params: vec![], using: None, language: Some(Ident::new("plpgsql")), determinism_specifier: None, @@ -7922,3 +7995,257 @@ fn parse_identifiers_semicolon_handling() { let statement = "SHOW search_path; SHOW ALL; SHOW ALL"; pg_and_generic().statements_parse_to(statement, statement); } + +#[test] +fn parse_create_table_partition_of_range() { + // RANGE partition with FROM ... TO + let sql = "CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateTable(create_table) => { + assert_eq!("measurement_y2006m02", create_table.name.to_string()); + assert_eq!( + Some(ObjectName::from(vec![Ident::new("measurement")])), + create_table.partition_of + ); + match create_table.for_values { + Some(ForValues::From { from, to }) => { + assert_eq!(1, from.len()); + assert_eq!(1, to.len()); + match &from[0] { + PartitionBoundValue::Expr(Expr::Value(v)) => { + assert_eq!("'2006-02-01'", v.to_string()); + } + _ => panic!("Expected Expr value in from"), + } + match &to[0] { + PartitionBoundValue::Expr(Expr::Value(v)) => { + assert_eq!("'2006-03-01'", v.to_string()); + } + _ => panic!("Expected Expr value in to"), + } + } + _ => panic!("Expected ForValues::From"), + } + } + _ => panic!("Expected CreateTable"), + } +} + +#[test] +fn parse_create_table_partition_of_range_with_minvalue_maxvalue() { + // RANGE partition with MINVALUE/MAXVALUE + let sql = + "CREATE TABLE orders_old PARTITION OF orders FOR VALUES FROM (MINVALUE) TO ('2020-01-01')"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateTable(create_table) => { + assert_eq!("orders_old", create_table.name.to_string()); + assert_eq!( + Some(ObjectName::from(vec![Ident::new("orders")])), + create_table.partition_of + ); + match create_table.for_values { + Some(ForValues::From { from, to }) => { + assert_eq!(PartitionBoundValue::MinValue, from[0]); + match &to[0] { + PartitionBoundValue::Expr(Expr::Value(v)) => { + assert_eq!("'2020-01-01'", v.to_string()); + } + _ => panic!("Expected Expr value in to"), + } + } + _ => panic!("Expected ForValues::From"), + } + } + _ => panic!("Expected CreateTable"), + } + + // With MAXVALUE + let sql = + "CREATE TABLE orders_new PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO (MAXVALUE)"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateTable(create_table) => match create_table.for_values { + Some(ForValues::From { from, to }) => { + match &from[0] { + PartitionBoundValue::Expr(Expr::Value(v)) => { + assert_eq!("'2024-01-01'", v.to_string()); + } + _ => panic!("Expected Expr value in from"), + } + assert_eq!(PartitionBoundValue::MaxValue, to[0]); + } + _ => panic!("Expected ForValues::From"), + }, + _ => panic!("Expected CreateTable"), + } +} + +#[test] +fn parse_create_table_partition_of_list() { + // LIST partition + let sql = "CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US', 'CA', 'MX')"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateTable(create_table) => { + assert_eq!("orders_us", create_table.name.to_string()); + assert_eq!( + Some(ObjectName::from(vec![Ident::new("orders")])), + create_table.partition_of + ); + match create_table.for_values { + Some(ForValues::In(values)) => { + assert_eq!(3, values.len()); + } + _ => panic!("Expected ForValues::In"), + } + } + _ => panic!("Expected CreateTable"), + } +} + +#[test] +fn parse_create_table_partition_of_hash() { + // HASH partition + let sql = "CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0)"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateTable(create_table) => { + assert_eq!("orders_p0", create_table.name.to_string()); + assert_eq!( + Some(ObjectName::from(vec![Ident::new("orders")])), + create_table.partition_of + ); + match create_table.for_values { + Some(ForValues::With { modulus, remainder }) => { + assert_eq!(4, modulus); + assert_eq!(0, remainder); + } + _ => panic!("Expected ForValues::With"), + } + } + _ => panic!("Expected CreateTable"), + } +} + +#[test] +fn parse_create_table_partition_of_default() { + // DEFAULT partition + let sql = "CREATE TABLE orders_default PARTITION OF orders DEFAULT"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateTable(create_table) => { + assert_eq!("orders_default", create_table.name.to_string()); + assert_eq!( + Some(ObjectName::from(vec![Ident::new("orders")])), + create_table.partition_of + ); + assert_eq!(Some(ForValues::Default), create_table.for_values); + } + _ => panic!("Expected CreateTable"), + } +} + +#[test] +fn parse_create_table_partition_of_multicolumn_range() { + // Multi-column RANGE partition + let sql = "CREATE TABLE sales_2023_q1 PARTITION OF sales FOR VALUES FROM ('2023-01-01', 1) TO ('2023-04-01', 1)"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateTable(create_table) => { + assert_eq!("sales_2023_q1", create_table.name.to_string()); + match create_table.for_values { + Some(ForValues::From { from, to }) => { + assert_eq!(2, from.len()); + assert_eq!(2, to.len()); + } + _ => panic!("Expected ForValues::From"), + } + } + _ => panic!("Expected CreateTable"), + } +} + +#[test] +fn parse_create_table_partition_of_with_constraint() { + // With table constraint (not column constraint which has different syntax in PARTITION OF) + let sql = "CREATE TABLE orders_2023 PARTITION OF orders (\ +CONSTRAINT check_date CHECK (order_date >= '2023-01-01')\ +) FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')"; + match pg_and_generic().verified_stmt(sql) { + Statement::CreateTable(create_table) => { + assert_eq!("orders_2023", create_table.name.to_string()); + assert_eq!( + Some(ObjectName::from(vec![Ident::new("orders")])), + create_table.partition_of + ); + // Check that table constraint was parsed + assert_eq!(1, create_table.constraints.len()); + match create_table.for_values { + Some(ForValues::From { .. }) => {} + _ => panic!("Expected ForValues::From"), + } + } + _ => panic!("Expected CreateTable"), + } +} + +#[test] +fn parse_create_table_partition_of_errors() { + let sql = "CREATE TABLE p PARTITION OF parent"; + let result = pg_and_generic().parse_sql_statements(sql); + assert!(result.is_err()); + let err = result.unwrap_err().to_string(); + assert!( + err.contains("FOR VALUES or DEFAULT"), + "Expected error about FOR VALUES, got: {err}" + ); + + let sql = "CREATE TABLE p PARTITION OF parent WITH (fillfactor = 70)"; + let result = pg_and_generic().parse_sql_statements(sql); + assert!(result.is_err()); + let err = result.unwrap_err().to_string(); + assert!( + err.contains("FOR VALUES or DEFAULT"), + "Expected error about FOR VALUES, got: {err}" + ); + + let sql = "CREATE TABLE p PARTITION OF parent FOR VALUES RANGE (1, 10)"; + let result = pg_and_generic().parse_sql_statements(sql); + assert!(result.is_err()); + let err = result.unwrap_err().to_string(); + assert!( + err.contains("IN, FROM, or WITH"), + "Expected error about invalid keyword after FOR VALUES, got: {err}" + ); + + let sql = "CREATE TABLE p PARTITION OF parent FOR VALUES FROM (1)"; + let result = pg_and_generic().parse_sql_statements(sql); + assert!(result.is_err()); + let err = result.unwrap_err().to_string(); + assert!( + err.contains("TO"), + "Expected error about missing TO clause, got: {err}" + ); + + let sql = "CREATE TABLE p PARTITION OF parent FOR VALUES IN ()"; + let result = pg_and_generic().parse_sql_statements(sql); + assert!(result.is_err()); + let err = result.unwrap_err().to_string(); + assert!( + err.contains("at least one value"), + "Expected error about empty value list in IN clause, got: {err}" + ); + + let sql = "CREATE TABLE p PARTITION OF parent FOR VALUES FROM () TO (10)"; + let result = pg_and_generic().parse_sql_statements(sql); + assert!(result.is_err()); + let err = result.unwrap_err().to_string(); + assert!( + err.contains("at least one value"), + "Expected error about empty FROM list, got: {err}" + ); + + let sql = "CREATE TABLE p PARTITION OF parent FOR VALUES FROM (1) TO ()"; + let result = pg_and_generic().parse_sql_statements(sql); + assert!(result.is_err()); + let err = result.unwrap_err().to_string(); + assert!( + err.contains("at least one value"), + "Expected error about empty TO list, got: {err}" + ); +} --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
