Re: Simulating sequences
От | |
---|---|
Тема | Re: Simulating sequences |
Дата | |
Msg-id | 51399.12.111.55.140.1061313005.squirrel@$HOSTNAME обсуждение исходный текст |
Ответ на | Re: Simulating sequences (Dennis Gearon <gearond@cvc.net>) |
Список | pgsql-general |
> so has the final implemention become: > A/ A column value per employee Almost. More precisely, it is, as you would expect, one ROW per employee (in the employee table). The employee table includes a COLUMN defined to store the most-recently-issued number in the sequence for that paricular employee. > B/ A trigger to implement the incrementing of the value Yes. When an expense report is created for an employee, i.e., a row is inserted in the expense_report table, that trigger fires, updating the employee's sequence column value and using that new value in the expense_report table. > C/ A row/record lock to enforce atomicity The lock is not explicite. I'm told from good sources that the UPDATE statement creates a lock implicitely in the updated row, and that there is an implicite transaction around the trigger as part of the originating INSERT statement. > > 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
В списке pgsql-general по дате отправления: