postgresql - SELECT 可以使用查询的结果来形成 PostgreSQL 中的表表达式吗？

Question

我有一个表，其中包含我数据库中其他表的索引。

之前，我遍历该索引以对索引表执行查询，但正如您可能想象的那样，这对于大量表来说非常慢。

我想知道是否可以根据对我的索引表的查询结果形成一个表表达式，然后使用查询的 list of tables result 将我的查询写成一个带有 FROM 的语句在我的索引表上。

预计到达时间:我正在使用 PostgreSQL v9.0。我通过一次迭代一个查询来查询具有相同查询的多个表(这很慢)。我想加快速度，如果有一种方法可以使用一个表表达式查询所有表，该表表达式是从索引表中选择的所有表的列表。

我的(简化的)表是“表数据”，其中包含相同模式表 A、B、C、D、E、F、G 的列表和每个表的 bool 值“已启用”。我想查询所有 'enabled' 为 true 的字母表；最好使用一个查询，而不是一次迭代一个查询。基本上查询由以下方式返回的那些表:

SELECT tablename FROM tabledata WHERE enabled = TRUE`

ETA2:我在查询工具中输入如下:

CREATE FUNCTION f_all_tables()
  RETURNS SETOF A AS 
$body$
DECLARE 
    _tbl oid;
BEGIN 
FOR _tbl IN 
    SELECT tablename::regclass
    FROM   tabledata 
    WHERE  enabled 
LOOP 
    RETURN QUERY EXECUTE  '
    SELECT * FROM ' || _tbl; 
END LOOP; 
END;

$body$ language plpgsql; 

SELECT * FROM f_all_tables();

并得到一个错误:

ERROR:  invalid input syntax for type oid: "A"
CONTEXT:  PL/pgSQL function "f_all_tables" line 6 at FOR over SELECT rows

其中 A 是 tabledata 中的第一个表名。

预计到达时间 3:

我已经实现了下面的第二个解决方案并且有效。谢谢示例代码:

CREATE TABLE tabledata2 
(
  tablename character(64) NOT NULL,
  enabled boolean
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tabledata2
  OWNER TO postgres;

CREATE TABLE AAAAAA
(
  enabled boolean
)
WITH (
  OIDS=FALSE
);
ALTER TABLE AAAAAA
  OWNER TO postgres;
CREATE TABLE AAAAAB
(
  enabled boolean
)
WITH (
  OIDS=FALSE
);
ALTER TABLE AAAAAB
  OWNER TO postgres;

INSERT INTO tabledata2 VALUES ('AAAAAA', 'true');
INSERT INTO tabledata2 VALUES ('AAAAAB', 'true');
INSERT INTO AAAAAA VALUES ('true');
INSERT INTO AAAAAB VALUES ('false');

CREATE FUNCTION f_all_tables() 
  RETURNS SETOF AAAAAA AS  
$body$ 
DECLARE  
    _tbl regclass; 
BEGIN  
FOR _tbl IN  
    SELECT tablename::regclass 
    FROM   tabledata2  
    WHERE  enabled
LOOP  
    RETURN QUERY EXECUTE  ' 
    SELECT * FROM ' || _tbl;  
END LOOP;  
END; 

$body$ language plpgsql;  

SELECT * FROM f_all_tables(); 

Answer 1

我从您的评论中了解到，您所有的候选表都使用相同的布局。

您自己已经知道，正确的方法是拥有一个包含所有行的表，可能还有一个额外的列来标记它们的类型。

从一个表中检索行

CREATE FUNCTION f_table(int)
  RETURNS SETOF A AS
$body$
BEGIN

    RETURN QUERY EXECUTE '
    SELECT * FROM ' || tablename::regclass FROM mytables WHERE id = $1;

END;
$body$ language plpgsql;

调用:

SELECT * FROM f_table(42);

要点:

• 您可以像查询表一样查询“表函数”(函数返回 SETOF 值)。

• 您的表格使用相同的布局。因此，您可以定义返回类型，而不必在每次调用时都键入列定义列表。

• 你一定知道PostgreSQL中的每张表都有一个同名的复合类型。这就是为什么您可以使用其中一个表的名称(在我的示例中为 A)作为返回类型并且它会起作用的原因。

• 我使用 cast to regclass对于表名而不是 quote_ident(tablename)。它同样可以防止 SQL 注入(inject)，它也适用于模式限定的表名，如 myschma.mytable。 quote_ident()会是个好主意，但在这种情况下会失败。

• 带有动态 SQL 的函数永远不能定义为 STABLE。 More about creating functions in the manual.

---

从所有表中检索行

(都是相同的基本类型。)
首先:在纯 SQL 中无法使用动态表列表执行此操作。

CREATE FUNCTION f_all_tables()
  RETURNS SETOF A AS
$body$
DECLARE
    _tbl regclass;
BEGIN

FOR _tbl IN
    SELECT tablename::regclass
    FROM   tabledata
    WHERE  enabled
LOOP
    RETURN QUERY EXECUTE '
    SELECT * FROM ' || _tbl;
END LOOP;

END;
$body$ language plpgsql;

调用:

SELECT * FROM f_all_tables();

如果这组表的成员不变，你可以写一个大丑UNION SELECT :

SELECT * FROM A
UNION ALL
SELECT * FROM B
...

UNION ALL 以保留结果中的所有重复项。