postgresql 循环遍历并从多个表中选择数据的函数

Function to loop through and select data from multiple tables

提问by user3813773

I'm new to Postgres and have a database with multiple tables of the same structure. I need to select data from each table that matches certain criteria.

我是 Postgres 的新手，并且有一个包含多个相同结构表的数据库。我需要从每个表中选择符合特定条件的数据。

I could do this with a bunch of UNIONqueries, but the number of tables I need to search can change over time, so I don't want to hard code it like that. I've been trying to develop a function that will loop through specific tables (they have a common naming convention) and return a table of records, but I'm not getting any results when I query the function. Function code is below:

我可以用一堆UNION查询来做到这一点，但是我需要搜索的表的数量会随着时间的推移而改变，所以我不想像那样硬编码。我一直在尝试开发一个函数，该函数将循环遍历特定的表（它们有一个共同的命名约定）并返回一个记录表，但是当我查询该函数时我没有得到任何结果。功能代码如下：

CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
  RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
$BODY$
DECLARE
    formal_table text;
begin
  FOR formal_table IN
    select table_name from information_schema.tables
    where table_schema = 'public' and table_name like 'formaltable%'
  LOOP
    EXECUTE 'SELECT natural_id, name, natural_id_numeric
             FROM ' || formal_table || 
           ' WHERE natural_id_numeric IN (
                select natural_id_numeric from internal_idlookup
                where internal_id = ''7166571'')';
    RETURN NEXT;
 END LOOP;
 Return;
END;
$BODY$
  LANGUAGE plpgsql;

I am not getting any errors when I try to use the function, but it's not returning any rows:

当我尝试使用该函数时，我没有收到任何错误，但它没有返回任何行：

SELECT * From internalid_formaltable_name_lookup();

Any idea where I went wrong?

知道我哪里出错了吗？

回答by Erwin Brandstetter

CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
  RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
$func$
DECLARE
   formal_table text;
BEGIN
   FOR formal_table IN
      SELECT quote_ident(table_name)
      FROM   information_schema.tables
      WHERE  table_schema = 'public'
      AND    table_name LIKE 'formaltable%'
   LOOP
      RETURN QUERY EXECUTE
      'SELECT t.natural_id, t.name, t.natural_id_numeric
       FROM   internal_idlookup i 
       JOIN   public.' || formal_table || ' t USING (natural_id_numeric)
       WHERE  i.internal_id = 7166571';   -- assuming internal_id is numeric
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Major points:

要点：

• You have to use RETURN QUERY EXECUTEto return each set of rows.
  EXECUTE, followed by RETURN NEXT, does not do what you seem to expect at all.

• You need to sanitize identifiers. I am using quote_ident()here. Or your query will break with non-standard identifiers and allow SQL injection!

• Converted your col IN (sub-select)to a more efficient JOIN.

• This is subtly different from using a bunch of UNION queries. It does notremove duplicate rows, and actually works like UNION ALL.

• 您必须使用RETURN QUERY EXECUTE来返回每组行。
  EXECUTE，其次RETURN NEXT，不会做你似乎会发生什么，在所有。

• 您需要清理标识符。我在quote_ident()这里使用。否则您的查询将因非标准标识符而中断并允许 SQL 注入！

• 将您转换col IN (sub-select)为更高效的JOIN.

• 这与使用a bunch of UNION queries. 它并不能去除重复行，和实际工作一样UNION ALL。

Personally, I'd rather build this on the system catalog pg_class. Details:

就个人而言，我更愿意在系统目录上构建它pg_class。细节：

• How to check if a table exists in a given schema

• 如何检查给定模式中是否存在表

Then you can work with the pg_class.oid::regclassto escape and schema-qualify table names automatically. Details:

然后您可以使用pg_class.oid::regclass自动转义和模式限定表名。细节：

• Table name as a PostgreSQL function parameter
• Search across multiple tables and also display table name in resulting rows

• 表名作为 PostgreSQL 函数参数
• 在多个表中搜索并在结果行中显示表名

But that depends on the details of your requirements and ... taste.

但这取决于您的要求和......品味的细节。