I have a database that uses postgresql schemas for multi-tenancy purposes. It has a table in the public schema called customers with an id and tenant column. The value for tenant is a string, and there's a corresponding postgresql schema with tables in it that match.
It looks like this:
# public.customers # first.users # second.users
| id | tenant | | id | name | | id | name |
|----|--------| |----|--------| |----|--------|
| 1 | first | | 1 | bob | | 1 | jen |
| 2 | second | | 2 | jess | | 2 | mike |
I'm wondering how I could make a single query to fetch values from a table in the schema, just given a customer id.
So if I have a customer_id of 1, how can I select * from first.users in a single query.
I'm guessing this might have to be a function written in pgpsql, but I don't have a lot of experience with that. Something like:
select * from tenant_table(1, 'users');
?
create or replace function f(_id int)
returns table (id int, name text) as $f$
declare _tenant text;
begin;
select tenant into _tenant
from public.customers
where id = _id;
return query execute format($e$
select *
from %I.users
$e$, _tenant);
end;
$f$ language plpgsql;
You cannot do that with a single query.
You'll have to use one query that selects the schema name, then construct a second query and run that.
Of course you can define a PL/pgSQL function that does both for you and executes the dynamic query with EXECUTE.