Create rows in multiple tables on new user
How can I create the following in a single function?
- a row in table users with the auth.users.id,
- a row in table org with a unique id and name My first org,
- a row in org_users with a unique id, user_id from row created in users, and org_id from the row created in org.
When using this function I get
- a row in table users with the auth.users.id,
- a row in table org with a unique id and name My first org,
- a row in org_users with a unique id, user_id from row created in users, and org_id from the row created in org.
begin
WITH users AS (
insert into public.users (id) values (new.id) returning new
),
org AS (
insert into public.org ( name) values (name ->> 'My first Org') returning name
)
insert into public.org_users (user_id, org_id)
select users.id, org.id from public.users, public.org;
return NULL;
end;
When using this function I get
There is a column named "name" in table "org", but it cannot be referenced from this part of the query.
