CODEWITHSUNDEEP
postgresqltriggers
julesbou
julesbou

Reputation: 5780

postgres update NEW variable before INSERT in a TRIGGER

I've two tables accounts and projects:

create table accounts (
  id          bigserial primary key,
  slug        text unique
);


create table projects (
  id          bigserial primary key,
  account_id  bigint not null references accounts (id),
  name        text
);

I want to be able to insert a new row into projects by specifying only account.slug (not account.id). What I'm trying to achieve is something like:

INSERT into projects (account_slug, name) values ('account_slug', 'project_name');

I thought about using a trigger (unfortunately it doesn't work):

create or replace function trigger_projects_insert() returns trigger as $$
begin
  if TG_OP = 'INSERT' AND NEW.account_slug then

    select id as account_id
      from accounts as account
      where account.slug = NEW.account_slug;

    NEW.account_id = account_id;

    -- we should also remove NEW.account_slug but don't know how

  end if;
  return NEW;
end;
$$ LANGUAGE plpgsql;

create trigger trigger_projects_insert before insert on projects
  for each row execute procedure trigger_projects_insert();

What is the best way to achieve what I'm trying to do?

Is a trigger a good idea? Is there any other solution?

Upvotes: 2

Views: 685

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248225

WITH newacc AS (
   INSERT INTO accounts (slug)
      VALUES ('account_slug')
      RETURNING id
)
INSERT INTO projects (account_id, name)
   SELECT id, 'project_name'
      FROM newacct;

If you are limited in the SQL you can use, another idea might be to define a view over both tables and create an INSTEAD OF INSERT trigger on the view that performs the two INSERTs on the underlying tables. Then an INSERT statement like the one in your question would work.

Upvotes: 1

Related Questions