CODEWITHSUNDEEP
postgresqlplpgsqlmultiple-tablessupabase-database
Lostbuttrying
Lostbuttrying

Reputation: 1

Plpgsql, how to insert a new row in multiple tables?

I have a supabase plpgsql data base with 10 tables. I also have the auth.users. I'm trying to write a function which will insert a new row in each of the 10 public.tables when a new user signs up. I have used security definer and have a function which works properly, but it will only allow me to insert into public.table1 , If I try to add other public tables to the function then it fails.

I have all RLS set to allow all, I have also tried 2 separate functions where both use a trigger from the auth.user table, with one writing to public.table1 while the other trigger and funtion write to public.table2 , however nothing seems to work. I'm not that familiar with plpgsql, so if someone could show me the correct way to write the code, it would be greatly appreciated. What I have now that works, but only for one public table insert is as follows

    Begin
      Insert into    public.tableone(id)
Values (new.id);
Returns new;

I'm sure there is another way to do this, such as a CTE, but I have not been able to write the code correctly to work. I'm a beginner, so please take it easy on me guys. Lol

Upvotes: 0

Views: 270

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658562

If all you insert into those other tables is the new ID, you don't need a CTE.

-- trigger function
CREATE OR REPLACE FUNCTION public.trg_users_insert_after()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO public.tableone(id) VALUES (NEW.id);
   INSERT INTO public.tabletwo(id) VALUES (NEW.id);
-- more?   

   RETURN NULL;  -- note correct syntax!
END
$func$;

-- trigger
CREATE TRIGGER my_trigger
AFTER INSERT ON public.users
FOR EACH ROW EXECUTE FUNCTION public.trg_users_insert_after();

In older versions before Postgres 11 use the old (misleading) syntax:

...
FOR EACH ROW EXECUTE PROCEDURE ...

Related:

Upvotes: 0

Related Questions