在多个动态模式上循环Sql查询，同时将结果合并

Question

因此，我遇到的问题是，我使用的PostgreSql数据库有多个零售商，每个零售商都是一个模式。这些零售商是在公共模式的表中定义的。因此，我需要一个script，dat可以首先从public.tenants表中检索所有这些零售商，然后循环使用.programs表来选择零售商模式：

SELECT * FROM a.programs;
SELECT * FROM b.programs;

我试过了一些变体，但找不到解决这个问题的方法。

我尝试过的SQL脚本：

DO
$do$
DECLARE
    tables CURSOR FOR
        SELECT tenantid
        FROM public.tenants;
BEGIN 
FOR scheme_name IN tables LOOP
    EXECUTE format('SELECT * FROM %s.%s',scheme_name, 'programs');
END LOOP;
END
$do$;

这提供了以下输出：

ERROR:  syntax error at or near ")"
LINE 1: SELECT * FROM (a).programs
                          ^
QUERY:  SELECT * FROM (a).programs
CONTEXT:  PL/pgSQL function inline_code_block line 8 at EXECUTE
********** Error **********

ERROR: syntax error at or near ")"
SQL state: 42601
Context: PL/pgSQL function inline_code_block line 8 at EXECUTE

如何使EXECUTE不以in ()作为动态模式名称？或者这个应用程序根本不可能？

请帮我解决这个问题:)

Answer 1

你有多个错误。当前的错误是，您引用的是循环中的记录，而不是该记录的单个字段，而括号是记录的默认字符串显示的一部分。

您还应该使用%I作为用于标识符的占位符，以正确处理引用。

因此，使用以下方法修复了即时错误：

DO
$do$
DECLARE
    tables CURSOR FOR
        SELECT tenantid
        FROM public.tenants;
BEGIN 
FOR rec IN tables LOOP
    EXECUTE format('SELECT * FROM %I.%I', rec.tenantid, 'programs');
END LOOP;
END
$do$;

但是，由于无法从匿名PL/PgSQL块返回这样的结果，这仍然无法工作。你需要把它放到一个函数中：

create or replace function all_programs()
  returns setof a.programs -- or returns table (...)
as
$$
declare
    tables CURSOR FOR
        SELECT tenantid
        FROM public.tenants;
BEGIN 
  FOR rec IN tables LOOP
    return query EXECUTE format('SELECT * FROM %I.%I', rec.tenantid, 'programs');
  END LOOP;
END

$$语言plgpgsql；

然后使用：

select *
from all_programs();