Good day! I tried to form a trigger by automatically adding a key (incremental) field to the table. Give a true example of how to use a sequence to make a trigger with an autoincrement number.

My incorrect (erroneous) option is below:

CREATE FUNCTION trigger_s_before_lns () RETURNS trigger AS BEGIN NEW.ns=nvl(NEW.ns,to_char(nextval('s_seq'),'99999')) return NEW; END; LANGUAGE plpgsql;

Where s_seq is a sequence.

    1 answer 1

    To use sequences in postgres, you don’t need to create a trigger at all; it’s enough to specify a sequence in the default value:

     CREATE TABLE some_table( incremented_field integer DEFAULT nextval('s_seq'), /* ... */ );

    A PL / SQL trigger would look something like this:

     CREATE FUNCTION trigger_function() RETURNS trigger AS $$ BEGIN NEW.ns := nextval('s_seq'); RETURN NEW; END; $$ LANGUAGE plpgsql;
    • I know, but I would like to see the implementation through the trigger. I haven’t found material on this topic on the Internet yet - IntegralAL
    • Updated the answer. - Ilya Pirogov