PostgreSQL trigger to populate a NULL non-PK field with a sequence value

Question

I want to be able to track the history of related records in a table. On an Oracle system, I accomplished this by creating an insert trigger that would populate a field with the original record's PK, if known, or with the new PK if the original PK wasn't specified.

In other words, given the following code, the INS_Nomination trigger would populate a new Nomination.parent_nomination_id field with the incoming parent_nomination_id, or with the value generated for the table's PK (nomination_id) if the parent_nomination_id is NULL:

/*create nomination tables*/
    CREATE TABLE Nomination (
        nomination_id NUMBER(12,0) NOT NULL ENABLE, 
        parent_nomination_id NUMBER(12,0) NOT NULL, 
        whatever VARCHAR2(400 BYTE), 
        created_by NUMBER(12,0) NOT NULL ENABLE, 
        created_date DATE DEFAULT SYSDATE NOT NULL ENABLE, 
        active_ind NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, 
        CONSTRAINT Nomination_PK PRIMARY KEY (nomination_id) ENABLE
    );

    CREATE INDEX Nomination_INDEX1 ON Nomination (parent_nomination_id, active_ind DESC) ;

    CREATE UNIQUE INDEX Nomination_PK ON Nomination (nomination_id);

    CREATE SEQUENCE Nomination_SEQ;

    CREATE OR REPLACE TRIGGER INS_Nomination BEFORE INSERT ON Nomination FOR EACH ROW 
    BEGIN   
        SELECT Nomination_SEQ.nextval
        INTO :new.nomination_id 
        FROM Dual;

        SELECT NVL(:NEW.parent_nomination_id, :NEW.nomination_id)
        INTO :NEW.parent_nomination_id
        FROM Dual;
    END;

    /
    ALTER TRIGGER INS_Nomination ENABLE;

How can I accomplish this same functionality with Postgres?

Why don't use a serial column? And what have you tried so far? Did you see the examples in the Postgres manual? The pretty much show you how this can be done: postgresql.org/docs/current/static/plpgsql-trigger.html — user330315
– user330315, Commented Sep 13, 2013 at 6:03
@a_horse_with_no_name: the above code is Oracle; the PostgreSQL code I drafted includes a serial declaration on the PK. I didn't provide a snippet of my PostgreSQL because it was different table/field names than the Oracle example I had handy from a prior project. And it was 3am when I asked, so my Googling superpower was apparently diminished. All things considered, I'm fairly proud of how coherent the question comes across. — Jeromy French
– Jeromy French, Commented Sep 13, 2013 at 16:49

Jeromy French · Accepted Answer · 2013-09-13 16:44:31Z

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE nomination 
      ( nomination_id BIGSERIAL NOT NULL PRIMARY KEY
      , parent_nomination_id BIGINT NOT NULL
         REFERENCES nomination(nomination_id)
      , whatever VARCHAR
      , created_by INTEGER NOT NULL DEFAULT 0
      , created_date DATE NOT NULL DEFAULT CURRENT_DATE
      , active_ind boolean NOT NULL DEFAULT true
    );

CREATE OR REPLACE FUNCTION nomination_fix_parent() RETURNS TRIGGER
AS
$func$
        BEGIN
         NEW.parent_nomination_id := NEW.nomination_id ;
         RETURN NEW;
        END
$func$ LANGUAGE plpgsql ; 

CREATE TRIGGER nomination_fix_parent
    BEFORE INSERT ON nomination
    FOR EACH ROW
    WHEN (NEW.parent_nomination_id IS NULL)
    EXECUTE PROCEDURE nomination_fix_parent()
        ; 

-- Test it ...
INSERT INTO nomination(whatever) VALUES('First?' );
INSERT INTO nomination(parent_nomination_id, whatever) VALUES(1,'Second?' );
INSERT INTO nomination(nomination_id, parent_nomination_id, whatever) VALUES(5,1,'Five?' );
SELECT * FROM nomination;

-- after entering the third record with id=5, the sequence is out of sequence ...
SELECT currval('nomination_nomination_id_seq');

-- Re-adjust the value for the sequence
SELECT setval('nomination_nomination_id_seq', (SELECT MAX(nomination_id) FROM nomination) );

INSERT INTO nomination(whatever) VALUES('Fourth?' );
SELECT * FROM nomination;

Notes:

PRIMARY KEY and REFERENCES clauses automatically create indices for you
SERIAL and BIGSERIAL automatically create a sequence, and use its nextval() as a default
so the trigger in the above fragment is only needed to set up a DEFAULT for the parent_nomination_id, which is not based on the sequence, but on the nomination_id for the same row (which is, maybe, based on the sequence).
manually inserting a value into a SERIAL/bigserial column does not bump the sequence; so you will have to resync that explicitly afterwards (using setval('nomination_id_seq', select max() from ...)).

Collectives™ on Stack Overflow

PostgreSQL trigger to populate a NULL non-PK field with a sequence value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related