Can I select data across multiple schemas within the same SQL database?-CodePudding

I have one database with multiple schemas. I would like to run SELECT * FROM info, across all schemas that starts with "team".

The schemas are not fixed, meaning that schemas are added and dropped continuously, so I can't hardcode the schemas in the query. But I'm only interested in schemas that starts with "team". How do I do this?

CodePudding user response：

If all tables have an identical structure, you can write a PL/pgSQL function that does this:

create function get_info(p_schema_prefix text)
  returns table (... column definitions go here ...)
as
$$
declare
  l_rec record;
  l_sql text;
begin
  for l_rec in select table_schema, table_name
               from information_schema.tables
               where table_name = 'info'
                 and table_schema like p_schema_prefix||'%'
  loop 
    l_sql := format('select id, data from %I.%I', l_rec.table_schema, l_rec.table_name);
    return query execute l_sql;
  end loop;
end;
$$
language plpgsql;

The use it like this:

select *
from get_info('team')