postgresql - postgres 在所有模式中选择

Question

假设我有 3 个模式:foo、bar、baz。所以我可以这样做:

select * from foo.table, bar.table, baz.table;

如果我不想指定模式名称并且我想选择所有模式或全部由某个用户拥有怎么办？

Answer 1

如果您的表有不同的名称(在大多数情况下应该是规则)设置 search path :

select * from foo.table1, bar.table2, baz.table3;

set search_path = foo, bar, baz;
select * from table1, table2, table3;

重要提示:使用显式连接而不是在 from 子句中列出表。

select * 
    from table1
    join table2 on ...
    join table3 on ...

---

如果您在多个模式中有同名表，您可以在函数中引用它们。您需要来自系统目录的信息:

• pg_class - 包含有关数据库中所有表(和其他关系)的信息的目录，
• pg_namespace - 包含有关数据库中所有模式的信息的目录。

此查询列出数据库中所有具有 given_table_name 的表:

select 
    n.nspname, c.relname
from 
    pg_class c
join 
    pg_namespace n on n.oid = c.relnamespace
where 
    c.relkind = 'r' 
    and c.relname = 'given_table_name';

下面的函数:

• 在 pg_class 中找到所有具有给定名称的表，
• 查找 pg_namespace 以找到上面找到的表的模式名称，
• 对具有给定名称的所有表执行一个函数，添加模式名称作为前缀。

假设您之前定义了函数do_something_with_this_table(tablename regclass):

create function do_something_with_all_these_tables(tablename text)
returns void language plpgsql 
as $$
declare
    schemaname text;
begin
    for schemaname in
        select n.nspname
        from pg_class c
        join pg_namespace n on n.oid = c.relnamespace
        where c.relkind = 'r' and c.relname = tablename
    loop
        execute format(
            'select do_something_with_this_table(''%s.%s'')',
            schemaname, tablename);
    end loop;
end $$;

select do_something_with_all_these_tables('given_table_name');

阅读更多:

• about plpgsql language
• about format() function