Can we do a join with a table in a schema that is not public?
Unfortunately not, but there is a work around
are there any work arounds?
Yes.
For my
Supachat example project I had to control a private.auth column.
The solution was to create a public.user table and fill it together with a database function when a new user creates an account.
Example:
create table
public.users (
id uuid not null,
username text null,
constraint users_pkey primary key (id)
) tablespace pg_default;
Create a new database function in the supabase dashboard.
In my example i have a json string in the raw_user_meta_data column with the username of the user. You can choose a other column.
begin
insert into public.users (id, username)
values (new.id, new.raw_user_meta_data ->> 'username');
return new;
end;
Set the "Show advanced settings" to true and scroll to the bottom.
On the section "Type of security" select the "SECURITY DEFINER"
Read more on
#4
Now we need to create a database trigger to call this function:
create trigger on_auth_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();
Now every time a new user registers he should write to the public.users table and get the user name from the auth table.
And now you can join your new public.users table.