Re: How to enforce the use of the sequence for serial columns ?

"Marc Mamin" <M(dot)Mamin(at)intershop(dot)de> writes:

> I'd like to ensure that nobody provide the ID in an insert statement
> when the id is linked to a sequence.
> I tried it with a trigger, but the id value is fed before the "BEFORE
> INSERT" test is performed (see below)...
>
>
> Any Idea ?

Trigger based solution where same trig can be used for any number of
tables by changing the parameter.

Will throw one of 2 exceptions on failure to use sequence for the insert.

create table foo (a serial);
psql:q:2: NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for serial column "foo.a"
CREATE TABLE
create function footrig()
returns trigger
as $$
begin
-- may throw currval not defined exception
if new.a = currval(tg_argv[0]) then
-- currval defined and equal new col value
return new;
end if;

-- currval is defined but not used for this insert
raise exception 'Not using default sequence';
end
$$ language plpgsql;
CREATE FUNCTION
create trigger footrig
before insert on foo
for each row
execute procedure footrig('foo_a_seq');
CREATE TRIGGER
You are now connected to database "jerry".
insert into foo values (1000);
psql:q:25: ERROR: currval of sequence "foo_a_seq" is not yet defined in this session
CONTEXT: PL/pgSQL function "footrig" line 3 at if
insert into foo values (default);
INSERT 0 1
insert into foo values (1000);
psql:q:27: ERROR: Not using default sequence
select * from foo;
a
---
1
(1 row)

> Cheers,
>
> Marc
>
>
>
> CREATE FUNCTION serialtest() RETURNS trigger AS $serialtest$
> BEGIN
> -- Check that the id is provided
> IF NEW.id IS NOT NULL THEN
> RAISE EXCEPTION 'id will be set from a sequence; do not
> provide it!';
> END IF;
>
> RETURN NEW;
> END;
> $serialtest$ LANGUAGE plpgsql;
>
>
> CREATE TABLE test_table
> (
> id serial primary key,
> foo int
> );
>
>
> CREATE TRIGGER test BEFORE INSERT OR UPDATE ON test_table
> FOR EACH ROW EXECUTE PROCEDURE serialtest();
>
>
> insert into test_table(foo)values(1);
>
> ERROR: id will be set from a sequence; do not provide it!
> SQL state: P0001

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant