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(at)seaworthysys(dot)com wrote:

>>On Monday, Aug 18, 2003, at 09:01 US/Pacific, <btober(at)seaworthysys(dot)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
>