How to force new records to have column values ONLY from a sequence in PostgreSQL?

Question

Please see answer below and Edit#1. However, the trigger still fails under PetaPoco/Npgsql.

I am missing something basic here. I need to ensure that any record added to the table has an orderno given only by the sequence. This should include even when the orderno is being supplied by the insert statement itself. That is,

insert into returntooffice (chart_recid, returndate, torder, **orderno**) values (14982,'2016-11-09','2017-12-4 00:21:42.553508', **0**);

and

insert into returntooffice (chart_recid, returndate, torder) values (14982,'2016-11-09','2017-12-4 00:21:42.553508');

should both result in the next orderno from the sequence and not an orderno of 0. That is, what is actually happening is that the supplied orderno (of 0) is being inserted--not the next sequence value (of 8000). I am using a trigger here since the actual composed insert is by an ORM that does not respect postgreSQL DEFAULT clauses on columns.

Here are the details:

CREATE TABLE returntooffice
(
  recid serial NOT NULL,
  orderno integer NOT NULL,
  chart_recid integer NOT NULL,
  returndate date,
  torder timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone DEFAULT now(),
  return_as_needed boolean,
  is_deferred boolean,
  CONSTRAINT returntooffice_pk PRIMARY KEY (recid),
  CONSTRAINT returntooffice_chart_fk FOREIGN KEY (chart_recid)
      REFERENCES charts (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT returntooffice_order_unqiue UNIQUE (orderno),
  CONSTRAINT returntooffice_unqiue UNIQUE (chart_recid, torder)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE returntooffice
  OWNER TO postgres;

CREATE TRIGGER get_next_order_number_trigger
  BEFORE INSERT
  ON returntooffice
  FOR EACH ROW
  EXECUTE PROCEDURE getnextorderno();

CREATE TRIGGER update_modified
  BEFORE UPDATE
  ON returntooffice
  FOR EACH ROW
  EXECUTE PROCEDURE update_modified();

CREATE SEQUENCE order_number_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 6558
  CACHE 1;

ALTER TABLE order_number_seq
  OWNER TO postgres;

CREATE OR REPLACE FUNCTION getnextorderno()
  RETURNS trigger AS
$BODY$

BEGIN
   NEW.orderno := nextval('order_number_seq');
   Return NEW; 
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION getnextorderno()
  OWNER TO postgres;

Edit#1: Renaming the trigger as suggested below, allows everything to work correctly under pgAdmin, but still fails on PetaPoco Insert. Any ideas why?

CREATE TRIGGER zzz_get_next_order_number_trigger
  BEFORE INSERT
  ON returntooffice
  FOR EACH ROW
  EXECUTE PROCEDURE getnextorderno();

Answer 1

Two alternatives to messy triggers. The first is to manipulate role privileges.

create table t (i serial, s text);

Revoke all privileges on the table from the pertinent roles:

revoke all on t from test;

Grant only select

grant select on t to test;

Grant all on all columns but the sequence one:

grant all (s) on t to test;

Grant usage only on the sequence:

revoke all on t_i_seq from test;
grant usage on t_i_seq to test;

Now that role can not insert into the sequence column:

insert into t (s) values ('a');
INSERT 0 1
insert into t (i,s) values (10,'a');
ERROR:  permission denied for relation t

The second alternative is simpler. Grant only select to the role:

revoke all on t from test;
grant select on t to test;

As the table owner create an insert function with 'security definer':

create function f(_s text)
returns t as $$

    insert into t (s) values (_s)
    returning *;

$$ language sql security definer;

The role will only be able to insert using the function.

Answer 2

You may have a different issue such as another trigger on that table which also has the behaviour of BEFORE INSERT and executes after this trigger. Remember that triggers order of execution for the same behaviour is chosen alphabetically by their names.

---

I've tested your case. (Yes, on Postgres 9.5.4, since you claim that it doesn't work in this version.) Everything works fine. Input value gets overwritten every time with a next value from order_number_seq.

We're starting with a value of 6558 of the sequence, and so:

postgres=# insert into returntooffice (orderno, chart_recid) values (0, 1);
INSERT 0 1
postgres=# insert into returntooffice (chart_recid) values (2);
INSERT 0 1
postgres=# insert into returntooffice (orderno, chart_recid) values 
           (nextval('order_number_seq'::regclass), 3);
INSERT 0 1

And the output as expected is:

postgres=# select recid, orderno, chart_recid from returntooffice;
 recid | orderno | chart_recid
-------+---------+-------------
     1 |    6558 |           1
     2 |    6559 |           2
     3 |    6561 |           3

---

To reproduce the "issue" I had to modify create scripts by dropping constraints, replacing order of execution for different statements and removing unnecessary parts. Here it is:

CREATE TABLE returntooffice
(
  recid serial NOT NULL,
  orderno integer NOT NULL,
  chart_recid integer NOT NULL,
  returndate date,
  torder timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone DEFAULT now(),
  return_as_needed boolean,
  is_deferred boolean
)
WITH (
  OIDS=FALSE
);

CREATE OR REPLACE FUNCTION getnextorderno()
  RETURNS trigger AS
$BODY$

BEGIN
   NEW.orderno := nextval('order_number_seq');
   Return NEW; 
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- Trigger: get_next_order_number_trigger on returntooffice

-- DROP TRIGGER get_next_order_number_trigger ON returntooffice;

CREATE TRIGGER get_next_order_number_trigger
  BEFORE INSERT
  ON returntooffice
  FOR EACH ROW
  EXECUTE PROCEDURE getnextorderno();


CREATE SEQUENCE order_number_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 6558
  CACHE 1;