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

"Marc Mamin" <M.Mamin@intershop.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