Re: Enforcing serial uniqueness?

On Mar 23, 2006, at 3:33 AM, Steven Brown wrote:
> -- On INSERT, fill id from the sequence - creator has UPDATE
> permission.
> -- Block attempts to force the id.
> CREATE OR REPLACE FUNCTION foo_id_insert_procedure() RETURNS
> trigger SECURITY DEFINER AS '
>   BEGIN
>     IF NEW.id != 0 THEN
>       RAISE EXCEPTION ''Setting id to a non-default is not allowed'';
>     ELSE
>       NEW.id := nextval(''foo_id_seq'');

BTW, with some clever use of TG_RELNAME you could probably make that
function generic, so that you could use it with any table; ie:
NEW.id := nextval( TG_RELNAME || ''_id_seq'');

Same holds true for the update function. You might also want to
create a function that does all the legwork of defining the sequence
and triggers for you.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461