Обсуждение: How to enforce the use of the sequence for serial columns ?

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 ?

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

How to enforce the use of the sequence for serial columns ?There are many
ways, here are a couple to think about:
1)
Revoke all access to the table and then create a VIEW to access the table
where by you simply don't use column "id" in the VIEWs insert / update
statements.

2)
Manually retrieve the serial number from a trigger function and modify
column "id" in the function.


Regards
Donald Fraser

----- Original Message -----
From: Marc Mamin

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 ?
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

"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

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


Many thanks,
This seems to be the simplest solution,

Marc