support parsing sequence related statements

[tharwan]

Is your feature request related to a problem? Please describe.
When using fakesnow for unit testing we discovered that CREATE SEQUENCE seq1 is currently not supported by sqlglot. As is accessing sequences.

Describe the solution you'd like
It would be terrific it sqlglot could also transpile sequence creation, access and deletion.

Describe alternatives you've considered
We have currently mocked the parts of our code that relied on the sequences being present. Since the sequence is only created implicitly via SQLalchemy, we also considered changing the table definitions during testing.

Additional context
at least between duckdb and snowflake there are bigger differences in how to access a sequence vs creating one:
duckdb: select nextval(seq1)
snowflake: select seq1.nextval

[georgesittas]

Hey, can you provide some more information on this? What's exactly the scope of this request? Docs would also be helpful.

[georgesittas]

I guess the motivation behind this was tekumara/fakesnow#44, i.e. transpiling Snowflake to DuckDB.

The thing is that Snowflake's syntax can't be mapped 1-1 to DuckDB without additional context, because e.g. it's perfectly valid to have a table t with a column nextval, so converting t.nextval into nextval('t') would be incorrect in this case. The additional context needed is whether t is a sequence object or not.

Another way to make this work could be through having metadata in comments:

SELECT t.nextval /* sequence */ FROM t

Not sure this is the correct way to address this, though. Due to the above I'll go ahead and close this as not planned for the time being. Happy to continue the discussion though, either here or in Slack.

Will take a look at the DDL syntax and see what it'd entail to fix the transpilation there, at least.

[tharwan]

Thanks for looking into this @georgesittas! I think it would be great if sqlglot would have have some docs about what things are not within the scope.

[georgesittas]

Sure thing! May update the FAQ section later about this, but what's within scope is kinda fluid, e.g. it changes depending on our priorities. The rule of thumb is that if something's too complicated and relatively niche, it's unlikely that we'll prioritize it.