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
WITHstartvalue ] [ INCREMENT BY 
 
incrementvalue ]                                           [ MAXVALUE maxvalue ] [ 
MINVALUE minvalue ] [ CYCLE | NO CYCLE ] ) ] ]