在多个动态模式上循环的SQL查询将结果组合在一个联合中

So I have the problem that the PostgreSql database I am using has multiple retailers each as a schema. These retailers are defined in a table in the public schema. So I need a sql script dat can first retrieve all these retailers from the public.tenants table and loop through them to Select the retailer schema with the .programs table:

所以我遇到的问题是我使用的PostgreSql数据库有多个零售商作为架构。这些零售商在公共模式的表中定义。所以我需要一个sql脚本dat可以先从public.tenants表中检索所有这些零售商并循环遍历它们以选择带有.programs表的零售商模式：

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

I have tried some variations, but cannot find a way to figure this one out.

我尝试了一些变化，但无法找到解决这个问题的方法。

The SQL script I have tried:

我试过的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$;

This gave the following output:

这给出了以下输出：

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

How do I make the EXECUTE not take in () around the dynamic schema name? Or is this application not possible at all?

如何使EXECUTE不围绕动态模式名称（？）？或者这个应用程序根本不可能？

Please help me out with this one :)

请帮帮我这一个:)

1 个解决方案

#1

You have multiple errors. The immediate error is that you are referencing the record from the loop, not the single field of that record and the parentheses are part of the default string display of a record.

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

You also should be using %I for placeholder intended for identifiers to properly deal with quoting.

您还应该使用％I作为标识符的占位符来正确处理引用。

So the immediate error is fixed using this:

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

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$;

But this is still not going to work because you can't return a result like that from an anonymous PL/PgSQL block. You need to put that into a function:

但是这仍然不起作用，因为你无法从匿名的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

$$ language plgpgsql;

Then use:

然后使用：

select *
from all_programs();

#1