Slight enhancement of NEXTVAL() and CURRVAL() functions

[Lukas Eder]

Hello,

H2 uses a very similar syntax like PostgreSQL to fetch NEXTVAL() and CURRVAL() values from sequences. However, H2 doesn't really support explicitly specifying case-senstive literals using quotes. In PostgreSQL, I can write:

nextval('foo')      operates on sequence foo
nextval('FOO')      operates on sequence foo
nextval('"Foo"')    operates on sequence Foo

The sequence name can be schema-qualified if necessary:

nextval('myschema.foo')     operates on myschema.foo
nextval('"myschema".foo')   same as above
nextval('foo')              searches search path for foo

As documented here:

http://www.postgresql.org/docs/9.3/static/functions-sequence.html

I think it would be useful to also support those quotes in sequence names. What do you think?

Cheers

Lukas

[Noel Grandin]

Why not just used the SQL standard for quoting identifiers:
NEXTVAL("Foo")
?

[Lukas Eder]

This was also my first thought. However, the SQL standard specifies the following (no support for CURRVAL):

<next value expression> ::=

    NEXT VALUE FOR <sequence generator name>

For backwards-compatibility reasons (and interoperability with PostgreSQL), I guess identifiers wrapped in string literals will still need to be supported.

Here's how other databases implement things:

DB2, Ingres, Oracle

"SCHEMA"."SEQUENCE".nextval

Sybase SQL Anywhere

[SCHEMA].[SEQUENCE].nextval

H2, PostgreSQL

nextval('SCHEMA.SEQUENCE')

Derby, Firebird, HSQLDB

next value for "SCHEMA"."SEQUENCE"

SQL Server

next value for [SCHEMA].[SEQUENCE]

CUBRID

"SEQUENCE".next_value

Cheers,

Lukas

?

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/DlFc-BFsuE0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Noel Grandin]

Hmmm.

Our current support is fairly ... extensive :-)

We support
nextval('SCHEMA.SEQUENCE')
nextval('SCHEMA', 'SEQUENCE')
and in both these cases we will do a case-sensitive match, and if that fails, a case-insensitive match.

We also support:
sequence.NEXTVAL
schema.sequence.NEXTVAL
"sequence".NEXTVAL -- for case-sensitive identifiers

I don't see why we can't add PostgreSQL's peculiar variant to that, given how much variation we already support :-)

On 2014-08-12 04:19 PM, Lukas Eder wrote:
> This was also my first thought. However, the SQL standard specifies the following (no support for CURRVAL):
>
> <next value expression> ::=
> NEXT VALUE FOR <sequence generator name>
>
>
> For backwards-compatibility reasons (and interoperability with PostgreSQL), I guess identifiers wrapped in string
> literals will still need to be supported.
>
> Here's how other databases implement things:
>

> *DB2, Ingres, Oracle*
>
> "SCHEMA"."SEQUENCE".nextval
>
>
> *Sybase SQL Anywhere*
>
> [SCHEMA].[SEQUENCE].nextval
>
>
> *H2, PostgreSQL*
>
> nextval('SCHEMA.SEQUENCE')
>
>
> *Derby, Firebird, HSQLDB*

>
> next value for "SCHEMA"."SEQUENCE"
>
>

> *SQL Server*
>
> next value for [SCHEMA].[SEQUENCE]
>
>
> *CUBRID*
>
> "SEQUENCE".next_value
>
>
> Cheers,
> Lukas
>
> 2014-08-12 16:01 GMT+02:00 Noel Grandin <noelg...@gmail.com <mailto:noelg...@gmail.com>>:

>
>
> Why not just used the SQL standard for quoting identifiers:
> NEXTVAL("Foo")
>
> ?
>
> --
> You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.

> To unsubscribe from this topic, visit https://groups.google.com/d/__topic/h2-database/DlFc-BFsuE0/__unsubscribe
> <https://groups.google.com/d/topic/h2-database/DlFc-BFsuE0/unsubscribe>.
> To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@__googlegroups.com
> <mailto:h2-database%2Bunsu...@googlegroups.com>.
> To post to this group, send email to h2-da...@googlegroups.com <mailto:h2-da...@googlegroups.com>.
> Visit this group at http://groups.google.com/__group/h2-database <http://groups.google.com/group/h2-database>.
> For more options, visit https://groups.google.com/d/__optout <https://groups.google.com/d/optout>.
>
>
> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to
> h2-database...@googlegroups.com <mailto:h2-database...@googlegroups.com>.
> To post to this group, send email to h2-da...@googlegroups.com <mailto:h2-da...@googlegroups.com>.

[Lukas Eder]

2014-08-12 16:41 GMT+02:00 Noel Grandin <noelg...@gmail.com>:

Hmmm.

Our current support is fairly ... extensive :-)

We support
  nextval('SCHEMA.SEQUENCE')
  nextval('SCHEMA', 'SEQUENCE')
and in both these cases we will do a case-sensitive match, and if that fails, a case-insensitive match.

Interesting, I wasn't aware of this algorithm :-)

 

We also support:
  sequence.NEXTVAL
  schema.sequence.NEXTVAL
  "sequence".NEXTVAL -- for case-sensitive identifiers

I wasn't aware of this either! Thanks for pointing it out.

 

I don't see why we can't add PostgreSQL's peculiar variant to that, given how much variation we already support :-)

H2 is the dump of all the world's SQL syntax, I guess ;-)