PostgreSQL insert triggers and many-to-many tables

user_3909890 0
Viewed 244

I have two tables in a postgres database articles and users. These two tables are connected through a many-to-many relation, so there's a third table called articles_users that contains foreign key relations to both articles and users.

What I'd like to do, is create a trigger that runs when a new entry is inserted into the articles table and connects that article to each user in my database through the articles_users table. Is this possible with triggers and if so, how should I write it?

I have a rough understanding of how triggers work and have some pseudosql that looks like this:

create function public.connect_articles_to_users() 
returns trigger as $$
begin
-- Here's some pseudocode to describe what I'd like to happen
-- Not sure if temprow is the right structure here
  for temprow in
    select * from users
      loop
        insert into articles_users (id, users.id) values (new.id, users.id)
      end loop
  return new
end;
$$ language plpgsql security definer;

-- trigger the function every time a and article is created
create trigger on_article_created
  after insert on articles
  for each row execute procedure public.connect_articles_to_users();
postgresql sql
2 Answers

Your trigger is already executes the function for each row of inserted article and you don't need a loop for users, you can insert many rows in one insert:

CREATE OR REPLACE FUNCTION connect_articles_to_users()
 RETURNS trigger AS
$$
BEGIN
 INSERT INTO articles_users (article_id, user_id)
  SELECT NEW.id, id FROM users
 ;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
user_3331530

Provided the articles_users table has columns article_id and user_id insert a row for every user

    insert into articles_users (article_id, user_id) 
    select new.id, users.id
    from users;
user_62199790
Related