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

发布时间：2020-12-13 18:09:32 所属栏目：百科来源：网络整理

导读：我是Postgres的新手,拥有一个具有相同结构的多个表的数据库.我需要从每个表中选择符合特定条件的数据. 我可以用一堆UNION查询做到这一点,但是我需要搜索的表的数量会随着时间的推移而改变,所以我不想像那样硬编码.我一直在尝试开发一个循环遍历特定表的函数(

我是Postgres的新手,拥有一个具有相同结构的多个表的数据库.我需要从每个表中选择符合特定条件的数据.

我可以用一堆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;

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

SELECT * From internalid_formaltable_name_lookup();

知道我哪里错了吗？

CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
  RETURNS TABLE(natural_id 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;

主要观点：

>您必须使用RETURN QUERY EXECUTE返回每组行.
EXECUTE,然后是RETURN NEXT,并不能完成您的预期.
>您需要清理标识符.我在这里使用quote_ident().或者您的查询将破坏非标准标识符并允许SQL注入！
>将col IN(子选择)转换为更高效的JOIN.
>这与使用一堆UNION查询略有不同.它不会删除重复的行,实际上就像UNION ALL一样.

就个人而言,我宁愿在系统目录pg_class上构建它.细节：

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

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

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!