Re: Simulating sequences

so has the final implemention become:

A/ A column value per employee
B/ A trigger to implement the incrementing of the value
C/ A row/record lock to enforce atomicity

btober@seaworthysys.com wrote:

>>On Monday, Aug 18, 2003, at 09:01 US/Pacific, <btober@seaworthysys.com>
>> wrote:
>>
>>
>>>With those items in mind, your function could become:
>>>
>>>CREATE FUNCTION key_generation(integer, varchar(20)) RETURNS integer
>>>AS'
>>>DECLARE
>>>  the_department ALIAS FOR $1;
>>>  the_table_name ALIAS FOR $2;
>>>BEGIN
>>>  IF NOT EXISTS(SELECT 1 FROM cnfg_key_generation
>>>    WHERE the_department = department AND the_table_name =
>>>table_name)
>>>THEN
>>>   INSERT INTO cnfg_key_generation VALUES
>>>(the_department,the_table_name,0);
>>>  END IF;
>>
>>    I would get the insert out of there, too.  If it doesn't exist, throw
>>an exception.  I don't believe sequences should automatically create
>>themselves (the tables and columns don't).
>>
>
>
> I agree. In my own case I need a sequence for each employee, and the
> simulated sequence is defined as a column in the employee table, so I'm
> guaranteed to have a a place to do the incrementing when the need arises.
> Also, I used a "DEFAULT 0" clause on the column definition for the
> sequence value, rather than explicitly inserting a zero. I left the
> insert statement in place for compatibility with the original inquirer's
> definition.
>
> ~Berend Tober
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>