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