Re: Simulating sequences

>
> 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