dialect: postgres, `CREATE AGGREGATE` and `CREATE EXTENSION` treated as invalid syntax, unsure if expected or bug.

[EdgyEdgemond]

CREATE EXTENSION pgcrypto' contains unsupported syntax. Falling back to parsing as a 'Command'
CREATE AGGREGATE IF NOT EXISTS my_agg (numeric) ( SFUNC = numeric_mul, STYPE=numeric )' contains unsupported syntax. Falling back to parsing as a 'Command'

https://www.postgresql.org/docs/16/sql-createextension.html https://www.postgresql.org/docs/16/sql-createaggregate.html

[EdgyEdgemond]

DROP behaves the same way.

[VaggelisD]

Hey @EdgyEdgemond,

The warning message you're getting is regarding SQLGlot, not Postgres. This means that the syntax specified is not supported yet by us, so as a fallback SQLGlot will instead "parse" it as a command i.e it will store the query in a single exp.Command AST node. The same will go for DROP since these 2 DDLs share parsing logic.

As for CREATE AGGREGATE & CREATE EXTENSION they're probably not high priority for us but I'll see if they can be supported out of the box. Will let the other contributors chime in as well.

[EdgyEdgemond]

Thanks, I figured that was the case, I'll see if I can handle these cases with the command object. Or keep an eye out for an update.

[VaggelisD]

Hey @EdgyEdgemond,

Thanks for reporting this once again. I'll go ahead and close the issue as not planned for now; I did a pass over both statements and reached the following conclusion:

• CREATE AGGREGATE is quite complex to parse and sadly not a high priority
• CREATE EXTENSION seems like a low-lift addition so I'll backlog it and revisit it once I have the bandwidth

I hope we'll at least support CREATE EXTENSION soon if that helps.

[EdgyEdgemond]

No worries, im currently looking at using sqlglot to validate and fallback to sqlparse to do a best effort where sqlglot returns a Command. One extra dependency but better than no validation :)

I'll keep an eye out for updates.

Cheers for the prompt replies

[VaggelisD]

Thanks for understanding. Just a heads up, SQLGlot is very lenient on what it can parse and thus should not be used as a validator; This is also briefly explained on the README:

Syntax errors are highlighted and dialect incompatibilities can warn or raise depending on configurations. However, SQLGlot does not aim to be a SQL validator, so it may fail to detect certain syntax errors.

[EdgyEdgemond]

Yeah, using it as more validation than 0 validation is a net positive. Catching things like keyword as table name etc.