postgres update NEW variable before INSERT in a TRIGGER

Question

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?

Laurenz Albe · Accepted Answer · 2017-03-09 09:01:12Z

1

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.

edited Mar 9, 2017 at 9:01

answered Mar 9, 2017 at 8:34

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

julesbou Over a year ago

Thanks for the reply, I'm using postgrest.com/en/v0.4, don't know if you're familiar with it, your solution even if correct, won't really work using postgrest, that's why i would like to use a trigger.

Laurenz Albe Over a year ago

I'd say that you should use something different if it keeps you from using the database properly, but it might not b your choice. I'll add a different idea, maybe that will help.

Collectives™ on Stack Overflow

postgres update NEW variable before INSERT in a TRIGGER

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related