Thread: Sequence functions

Sequence functions

From
Rod Taylor
Date:
We need to track sequence usage in things like defaults.  nextval()
function calls don't easily allow this to happen.

The 200N spec has NEXT VALUE FOR <seqname> as the equivalent to
nextval(), which would allow this to happen.

I can make VALUE an IDENT in gram.y to prevent it from becoming a
reserved keyword.


I would also like to add CURRENT VALUE FOR <seqname> for an equivalent
to currval().  CURRENT would need to become a reserved word for this to
happen.

The SQL spec has several reason why CURRENT should be reserved including
several cursor manipulation items (WHERE CURRENT OF), a windowing
function (is this based on cursors?), DISCONNECT, etc.

Any objections to making CURRENT a reserved word?


BTW, VALUE also seems to be used for a special form of unique
constraint. UNIQUE(VALUE) which is equivalent to UNIQUE(SELECT * FROM
<table>).

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: Sequence functions

From
Tom Lane
Date:
Rod Taylor <rbt@rbt.ca> writes:
> We need to track sequence usage in things like defaults.  nextval()
> function calls don't easily allow this to happen.

Yeah.  The Oracle syntax is seqname.nextval(); AFAIK our syntax
nextval('seqname') was a quick-and-dirty approximation to that.

> The 200N spec has NEXT VALUE FOR <seqname> as the equivalent to
> nextval(), which would allow this to happen.

Urgh.  These people still think they're writing Cobol, don't they?
Reserved Words 'R Us.  This is one of the few places where I like
Oracle's syntax better than the spec's.

I have looked at implementing Oracle's syntax, and it's almost doable
--- the problem is to keep the sequence from getting added to the
query's rangetable (or at least keep it out of the jointree).  There
is some speculation about this in the archives, starting at
http://archives.postgresql.org/pgsql-hackers/2001-08/msg00589.php
The regclass-datatype part of this is in the system now, but I didn't
get any further than that.
        regards, tom lane


Re: Sequence functions

From
Rod Taylor
Date:
On Sun, 2003-05-25 at 00:34, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > We need to track sequence usage in things like defaults.  nextval()
> > function calls don't easily allow this to happen.
>
> Yeah.  The Oracle syntax is seqname.nextval(); AFAIK our syntax
> nextval('seqname') was a quick-and-dirty approximation to that.

I doubt it'll be switched to the Oracle syntax.  Most of the new
IDENTITY stuff seems to be based on MSSQL. Some of it is quite nice.
Sequences based on things other than integers would be useful (time
based with an interval incremental).

Perhaps we should just wait it out to see what actually comes through.

Feel free to file your thoughts on NEXT VALUE FOR though!  I think there
was someone reading this list that was on the spec team at one point.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: Sequence functions

From
Rod Taylor
Date:
Incase someone is interested, here is a patch without the CURRENT VALUE
FOR portion.

On Sat, 2003-05-24 at 23:39, Rod Taylor wrote:
> We need to track sequence usage in things like defaults.  nextval()
> function calls don't easily allow this to happen.
>
> The 200N spec has NEXT VALUE FOR <seqname> as the equivalent to
> nextval(), which would allow this to happen.
>
> I can make VALUE an IDENT in gram.y to prevent it from becoming a
> reserved keyword.
>
>
> I would also like to add CURRENT VALUE FOR <seqname> for an equivalent
> to currval().  CURRENT would need to become a reserved word for this to
> happen.
>
> The SQL spec has several reason why CURRENT should be reserved including
> several cursor manipulation items (WHERE CURRENT OF), a windowing
> function (is this based on cursors?), DISCONNECT, etc.
>
> Any objections to making CURRENT a reserved word?
>
>
> BTW, VALUE also seems to be used for a special form of unique
> constraint. UNIQUE(VALUE) which is equivalent to UNIQUE(SELECT * FROM
> <table>).
--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachment