PostgreSQL: How to access the new value generated by a serial type column, in an insert command?

Question

I would like to access the newly created records ID(PM) in the insert command.

I have a table like this:

CREATE TABLE item_list."groups" (
    id serial4 NOT NULL,
    "name" varchar NOT NULL,
    hierarchical_id varchar NOT NULL,
    parent_id int4 NOT NULL
)

When I insert to the table, a new value for the id is generated automatically, because of the serial type.

However, I would like to fill the hierarchical_id so its the concatenation of the groups parents hierarchical_id + the newly created groups id.

EG.:

• The parents hierarchical id is: 0-12
• I insert a new group, which gets 20 for it's id.
• The new groups hierarchical_id should be 0-12-20.

Can I do this in the same query? If so, how should the insert command look like, to access the newly generated id?

The parents hieararchical_id is known, it doesnt need to be queried.

Answer 1

The best solution is probably a BEFORE INSERT trigger like this:

CREATE FUNCTION ident_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   NEW.hierarchical_id := concat(NEW.hierarchical_id, '-', NEW.id);
   RETURN NEW;
END;$$;

CREATE TRIGGER ident_trig BEFORE INSERT ON item_list."groups"
   FOR EACH ROW EXECUTE FUNCTION ident_trig();

Then you insert the parent's hierarchical_id, and the trigger will modify it correctly.