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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461