CODEWITHSUNDEEP
postgresql
codec
codec

Reputation: 8836

How to use the auto-generated primary key in the same row postrges

I am trying to make an insert statement into a table with the following schema

CREATE TABLE auth(
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    role                VARCHAR(64)
);

I am using pgcrypto extension to generate uuids. Is it possible to append id to the role field while inserting a row in this table?

I am inserting in this table using 

insert into auth (role) values ('admin');

I want to append the id generated to admin so that the role would look something like admin_12234-3453-3453-345-34534.

Upvotes: 0

Views: 165

Answers (2)

Patrick
Patrick

Reputation: 32326

You need an insert trigger to do this:

CREATE FUNCTION admin_uuid() RETURNS trigger AS $$
BEGIN
    NEW.role := NEW.role || NEW.id::text;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE TRIGGER set_admin_uuid
BEFORE INSERT ON auth
FOR EACH ROW EXECUTE PROCEDURE admin_uuid();

Upvotes: 1

clemens
clemens

Reputation: 17712

I think there is no direct way to achieve this. But postgres allows defining rules on tables and views (see https://www.postgresql.org/docs/9.2/static/sql-createrule.html). You can create a new rule for insertion which creates the id and the role as desired.

Upvotes: 0

Related Questions