Re: SERIAL type feature request

Hi,

Zoltan Boszormenyi írta:

> Jan Wieck írta:
>
>> On 12/5/2005 1:03 PM, Zoltan Boszormenyi wrote:
>>
>>> Jan Wieck írta:
>>>
>>>> On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote:
>>>>
>>>>> I found this in the SQL2003 draft:
>>>>>
>>>>> "
>>>>> 4.14.7 Identity columns
>>>>>
>>>>> ... An identity column has a start value, an increment, a maximum
>>>>> value, a minimum value,
>>>>> and a cycle option. ...
>>>>> "
>>>>>
>>>>> The exact properties of a sequence. It would be a good idea to be
>>>>> able to provide
>>>>> all these the same way PostgreSQL provides CREATE SEQUENCE.
>>>>
>>>>
>>>>
>>>>
>>>> I think nobody would object to implementing support for the SQL2003
>>>> syntax. Most of that would be providing all the values that will
>>>> get forwarded into the internal sequence generation during CREATE
>>>> TABLE.
>>>>
>>>> The other thing needed is an extension to the default value
>>>> mechanism that overrides any given value to implement GENERATE
>>>> ALLWAYS. Not too hard either.
>>>
>>>
>>>
>>>
>>> Where can I find this syntax? (PDF file name, page#) Thanks.
>>> I think I modify my feature request for the standard behaviour.
>>
>>
>>
>> It's all in the Foundation paper inside this zip:
>>
>> http://www.wiscorp.com/sql/sql_2003_standard.zip
>
>
>
> Thanks, I found it. It's "GENERATED { ALWAYS | BY DEFAULT } AS
> IDENTITY, isn't it?
> If I interpret it correctly, "GENERATED ALWAYS AS IDENTITY" means that
> no matter
> what I give in "INSERT INTO MYTABLE (serial_id, ...) VALUES (N, ...)",
> the sequence
> next value will be inserted into the database. I am all for it, it's
> much stronger than just watching
> for the 0 value and would fit my needs.
>
> The other behaviour is "GENERATED BY DEFAULT AS IDENTITY",
> which is what PostgreSQL currently provides.
>
> Best regards,
> Zoltán Böszörményi

To reiterate it, I would like the following added to PostgreSQL 8.2 TODO
(I may have got the optional parametes wrong...):

- Extend SERIAL type declaration and functionality with the SQL2003
compliant sequence generation options:
SERIAL [ GENERATED { ALWAYS | BY DEFAULT }
[ AS IDENTITY ( [ START WITH startvalue ] [ INCREMENT BY
incrementvalue ]
[ MAXVALUE maxvalue ] [
MINVALUE minvalue ] [ CYCLE | NO CYCLE ] ) ] ]