Re: Simulating sequences

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