Re: Knowing when it is safe to call currval()

Hi Bruno,

Thanks for your assistance.

>> I have a bunch of SECURITY DEFINER functions that are called from
>> TRIGGERS which force the primary key of tables to the next value in a
>> sequence. Normal users can't call nextval() or setval() on any
>> sequences. The triggers basically do:
>
> One way out of this using a different approach might be to make
> security
> definer functions that will let normal users call nextval for the
> sequences. You could then use these as the default for the appropiate
> columns.
>
> Along the way you are doing things, instead of checking to see if
> the row is not equal to currval, perhaps check to see if it is null.
> Then you could supply a value of null when you want it to be rewritten.
>

I've decided to do something similar to this. I'm now only doing the
currval() check if the user-supplied value is not null.

IF NEW.a_id IS NOT NULL THEN
IF NEW.a_id <> currval( 'a_id_seq' ) THEN
RAISE EXCEPTION 'a_id must be NULL or the current a_id_seq value.';
END IF;
ELSE
NEW.a_id := nextval( 'a_id_seq'::text );
END IF;

It is a bit of a kludge, but I painted myself into the corner in the
first place!

Cheers

Matthew.