How to dynamically SELECT from manually partitioned table

Question

Suppose I have table of tenants like so;

CREATE TABLE tenants (
  name varchar(50)
)

And for each tenant, I have a corresponding table called {tenants.name}_entities, so for example for tenant_a I would have the following table.

CREATE TABLE tenant_a_entities {
  id uuid,
  last_updated timestamp
}

Is there a way I can create a query with the following structure? (using create table syntax to show what I'm looking for)

CREATE TABLE all_tenant_entities {
  tenant_name varchar(50),
  id uuid,
  last_updated timestamp
}

--

I do understand this is a strange DB layout, I'm playing around with foreign data in Postgres to federate foreign databases.

Answer 1

Did you consider declarative partitioning for your relational design? List partitioning for your case, with PARTITION BY LIST ...

---

To answer the question at hand:

You don't need the table tenants for the query at all, just the detail tables. And one way or another you'll end up with UNION ALL to stitch them together.

SELECT 'a' AS tenant_name, id, last_updated FROM tenant_a_entities 
UNION ALL SELECT 'b', id, last_updated FROM tenant_b_entities 
...

You can add the name dynamically, like:

SELECT tableoid::regclass::text, id, last_updated FROM tenant_a_entities 
UNION ALL SELECT tableoid::regclass::text, id, last_updated FROM tenant_a_entities
...

See:

• Get the name of a row's source table when querying the parent it inherits from

But it's cheaper to add a constant name while building the query dynamically in your case (the first code example) - like this, for example:

SELECT string_agg(format('SELECT %L AS tenant_name, id, last_updated FROM %I' 
                        , split_part(tablename, '_', 2)
                        , tablename)
                  , E'\nUNION ALL '
                 ORDER BY tablename)  -- optional order
FROM   pg_catalog.pg_tables
WHERE  schemaname = 'public'  -- actual schema name
AND    tablename LIKE 'tenant\_%\_entities';

Tenant names cannot contain _, or you have to do more.

Related:

• Table name as a PostgreSQL function parameter
• How to check if a table exists in a given schema

You can wrap it in a custom function to make it completely dynamic:

CREATE OR REPLACE FUNCTION public.f_all_tenant_entities()
  RETURNS TABLE(tenant_name text, id uuid, last_updated timestamp)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   (
   SELECT string_agg(format('SELECT %L AS tn, id, last_updated FROM %I' 
                           , split_part(tablename, '_', 2)
                           , tablename)
                     , E'\nUNION ALL '
                    ORDER BY tablename)  -- optional order
   FROM   pg_tables
   WHERE  schemaname = 'public'  -- your schema name here
   AND    tablename LIKE 'tenant\_%\_entities'
   );
END
$func$;

Call:

SELECT * FROM public.f_all_tenant_entities();

You can use this set-returning function (a.k.a "table-function") just like a table in most contexts in SQL.

Related:

• How to UNION a list of tables retrieved from another table with a single query?
• Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
• Function to loop through and select data from multiple tables

Note that RETIRN QUERY does not allow parallel queriies before Postgres 14. The release notes:

Allow plpgsql's RETURN QUERY to execute its query using parallelism (Tom Lane)