Sequence usage patch
Enables syntax: NEXT VALUE FOR <seqname> and CURRENT VALUE FOR <seqname>
The 200N spec is based on DB2, not MSSQL, so it is very likely NEXT
VALUE FOR will be the official syntax.
DB2 uses PREVIOUS VALUE FOR <seqname> as their currval() (nothing in
spec about this). I don't see PREVIOUS as a reserved word, but CURRENT
certainly is -- WHERE CURRENT OF for cursors, and several other places.
The attached patch makes CURRENT a reserved word. VALUE is treated as
an IDENT to preserve the ability to use it as a column.
SequenceOp is the node (primnode) used in the executor, and required
splitting up nextval / currval into public and internal interfaces. The
internal interface operates on the sequence OID, and the public
interface on the sequence name as usual.
Dependencies are recorded for the SequenceOp node so now we cannot drop
a sequence used in a default expression.
I've not figured out the best place to record the dependency to prevent
the default expression from being changed for SERIAL columns yet, but
that should be a separate patch. The concept of a serial will need to
be brought in deeper than parser/analyze.c for this to happen.
No documentation changes attached. I want to know whether this would be
applied before I make those.
Tom,
Should I have created another parser node strictly for the gram.y stuff,
then had it copy the info to the primnode within parse_expr.c? As it
is, SequenceOp is left with a hanging RangeVar that is unused past that
point.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
nextvaluefor.patchtext/x-patch; charset=ISO-8859-1; name=nextvaluefor.patchDownload+302-96
Rod Taylor <rbt@rbt.ca> writes:
I don't see PREVIOUS as a reserved word, but CURRENT
certainly is -- WHERE CURRENT OF for cursors, and several other places.
The attached patch makes CURRENT a reserved word.
I do not think it will be necessary to treat CURRENT as a fully-reserved
word in order to support WHERE CURRENT OF, and accordingly I'm not very
happy with reserving it in order to support this ungainly,
not-yet-and-possibly-never-standard syntax. I still think that Oracle's
syntax is nicer, and by any sane estimate it is more of a real-world
standard than an unapproved 2000-something draft.
regards, tom lane
On Tue, 27 May 2003, Tom Lane wrote:
Rod Taylor <rbt@rbt.ca> writes:
I don't see PREVIOUS as a reserved word, but CURRENT
certainly is -- WHERE CURRENT OF for cursors, and several other places.The attached patch makes CURRENT a reserved word.
I do not think it will be necessary to treat CURRENT as a fully-reserved
word in order to support WHERE CURRENT OF, and accordingly I'm not very
happy with reserving it in order to support this ungainly,
not-yet-and-possibly-never-standard syntax. I still think that Oracle's
syntax is nicer, and by any sane estimate it is more of a real-world
standard than an unapproved 2000-something draft.
I have an in development patch to add where current of. CURRENT needs only
be added to the unreserved_keyword list. gram.y compiles fine and usage of
current doesn't cause parse errors:
template1=# create table abc (current text);
CREATE TABLE
template1=# insert into abc values('that');
INSERT 17079 1
template1=# insert into abc values('this');
INSERT 17080 1
template1=# begin;
BEGIN
template1=# declare blah cursor for select * from abc;
DECLARE CURSOR
template1=# fetch blah;
current
---------
that
(1 row)
template1=# update abc set current='that2' where current of blah;
UPDATE 1
template1=# commit;
Gavin
On Tue, 2003-05-27 at 00:21, Tom Lane wrote:
Rod Taylor <rbt@rbt.ca> writes:
I don't see PREVIOUS as a reserved word, but CURRENT
certainly is -- WHERE CURRENT OF for cursors, and several other places.The attached patch makes CURRENT a reserved word.
I do not think it will be necessary to treat CURRENT as a fully-reserved
word in order to support WHERE CURRENT OF, and accordingly I'm not very
Very well.. I'll hold onto the CURRENT portion until the term current
has been reserved (bound to happen eventually if we implement all of
SQL99).
Are you ok with the DB2 and draft-spec syntax of NEXT VALUE FOR (where
value is not a reserved word)? Or should I hold onto that until the
spec has gone through the final draft / release?
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Rod Taylor <rbt@rbt.ca> writes:
Are you ok with the DB2 and draft-spec syntax of NEXT VALUE FOR (where
value is not a reserved word)? Or should I hold onto that until the
spec has gone through the final draft / release?
By that time we'll have done the Oracle-style foo.nextval, and it'll
become kind of a moot point ;-)
regards, tom lane
On Tue, 2003-05-27 at 09:57, Tom Lane wrote:
Rod Taylor <rbt@rbt.ca> writes:
Are you ok with the DB2 and draft-spec syntax of NEXT VALUE FOR (where
value is not a reserved word)? Or should I hold onto that until the
spec has gone through the final draft / release?By that time we'll have done the Oracle-style foo.nextval, and it'll
become kind of a moot point ;-)
Well, not moot for anyone trying to go between PostgreSQL and a
non-Oracle (or SapDB) database, but certainly of less concern.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
(Moved to -hackers)
Rod Taylor <rbt@rbt.ca> writes:
Are you ok with the DB2 and draft-spec syntax of NEXT VALUE FOR (where
value is not a reserved word)? Or should I hold onto that until the
spec has gone through the final draft / release?By that time we'll have done the Oracle-style foo.nextval, and it'll
become kind of a moot point ;-)
I actually like the NEXT VALUE FOR a lot more. The reason is that the
Oracle syntax is very much an 'object.property' lookup, which we do nowhere
else in PostgreSQL. In fact, it's actually a bit bizarre when you start
going database.schema.sequence.nextval, etc.
The NEXT VALUE FOR syntax would be more in keeping with our current sytacies
methinks...
Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
I actually like the NEXT VALUE FOR a lot more. The reason is that the
Oracle syntax is very much an 'object.property' lookup, which we do nowhere
else in PostgreSQL.
I beg to differ. We have supported table.function since day one ---
it's in the original Berkeley code. For example:
regression=# \d int8_tbl
Table "public.int8_tbl"
Column | Type | Modifiers
--------+--------+-----------
q1 | bigint |
q2 | bigint |
regression=# create function mysum(int8_tbl) returns int8 as '
regression'# select $1.q1 + $1.q2' language sql;
CREATE FUNCTION
regression=# select *, mysum(t1), t1.mysum from int8_tbl t1;
q1 | q2 | mysum | mysum
------------------+-------------------+------------------+------------------
123 | 456 | 579 | 579
123 | 4567890123456789 | 4567890123456912 | 4567890123456912
4567890123456789 | 123 | 4567890123456912 | 4567890123456912
4567890123456789 | 4567890123456789 | 9135780246913578 | 9135780246913578
4567890123456789 | -4567890123456789 | 0 | 0
(5 rows)
So syntactically, the Oracle notation is in our direct line of
inheritance from Berkeley. The only reason we can't quite get
foo.nextval to work today is that the system wants to put foo
into the query's FROM list, which we don't want for a sequence
reference.
regards, tom lane
There was a lot of discussion about this patch, and shorter version
posted with just the CURRENT part. However, I think some felt that this
wasn't a standard yet, and therefore weren't ready to implement this.
Is that correct?
---------------------------------------------------------------------------
Rod Taylor wrote:
-- Start of PGP signed section.
Enables syntax: NEXT VALUE FOR <seqname> and CURRENT VALUE FOR <seqname>
The 200N spec is based on DB2, not MSSQL, so it is very likely NEXT
VALUE FOR will be the official syntax.DB2 uses PREVIOUS VALUE FOR <seqname> as their currval() (nothing in
spec about this). I don't see PREVIOUS as a reserved word, but CURRENT
certainly is -- WHERE CURRENT OF for cursors, and several other places.The attached patch makes CURRENT a reserved word. VALUE is treated as
an IDENT to preserve the ability to use it as a column.SequenceOp is the node (primnode) used in the executor, and required
splitting up nextval / currval into public and internal interfaces. The
internal interface operates on the sequence OID, and the public
interface on the sequence name as usual.Dependencies are recorded for the SequenceOp node so now we cannot drop
a sequence used in a default expression.I've not figured out the best place to record the dependency to prevent
the default expression from being changed for SERIAL columns yet, but
that should be a separate patch. The concept of a serial will need to
be brought in deeper than parser/analyze.c for this to happen.No documentation changes attached. I want to know whether this would be
applied before I make those.Tom,
Should I have created another parser node strictly for the gram.y stuff,
then had it copy the info to the primnode within parse_expr.c? As it
is, SequenceOp is left with a hanging RangeVar that is unused past that
point.
--
Rod Taylor <rbt@rbt.ca>PGP Key: http://www.rbt.ca/rbtpub.asc
[ Attachment, skipping... ]
-- End of PGP section, PGP failed!
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Fri, 2003-06-06 at 11:17, Bruce Momjian wrote:
There was a lot of discussion about this patch, and shorter version
posted with just the CURRENT part. However, I think some felt that this
wasn't a standard yet, and therefore weren't ready to implement this.
Is that correct?
Thats about what it boils down to.
People seem to prefer Oracle compatibility over DB2 compatibility, so
I'm going to hold it until the next spec is released.
Rod Taylor wrote:
-- Start of PGP signed section.Enables syntax: NEXT VALUE FOR <seqname> and CURRENT VALUE FOR <seqname>
The 200N spec is based on DB2, not MSSQL, so it is very likely NEXT
VALUE FOR will be the official syntax.DB2 uses PREVIOUS VALUE FOR <seqname> as their currval() (nothing in
spec about this). I don't see PREVIOUS as a reserved word, but CURRENT
certainly is -- WHERE CURRENT OF for cursors, and several other places.The attached patch makes CURRENT a reserved word. VALUE is treated as
an IDENT to preserve the ability to use it as a column.SequenceOp is the node (primnode) used in the executor, and required
splitting up nextval / currval into public and internal interfaces. The
internal interface operates on the sequence OID, and the public
interface on the sequence name as usual.Dependencies are recorded for the SequenceOp node so now we cannot drop
a sequence used in a default expression.I've not figured out the best place to record the dependency to prevent
the default expression from being changed for SERIAL columns yet, but
that should be a separate patch. The concept of a serial will need to
be brought in deeper than parser/analyze.c for this to happen.No documentation changes attached. I want to know whether this would be
applied before I make those.Tom,
Should I have created another parser node strictly for the gram.y stuff,
then had it copy the info to the primnode within parse_expr.c? As it
is, SequenceOp is left with a hanging RangeVar that is unused past that
point.
--
Rod Taylor <rbt@rbt.ca>PGP Key: http://www.rbt.ca/rbtpub.asc
[ Attachment, skipping... ]
-- End of PGP section, PGP failed!
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc