如何在Postgres中使用动态标识符连接表？

Question

我有一个名为table的表，包含两列foreign_table_name和foreign_key。

是否可以编写一个SELECT查询来JOIN此表的值和在foreign_table_name列中指定的表的值？

例如，如果我们知道所有可能的目标外键表都有一个name字段，我想知道是否可以编写一些内容来实现：

SELECT table.foo, table.bar, foreign_table.name 
FROM table
  JOIN $foreign_table AS foreign_table 
       ON (foreign_table.id = table.foreign_key
           $foreign_table = table.foreign_table);

任何使用PlpgSQL的解决方案都被接受。

以下是一个简单的内容：

Table ``table``
------------------------------------------------
| foo | bar | foreign_table_name | foreign_key |
------------------------------------------------
|  A  |  1  | fruits             | 8           |
|  B  |  2  | vegetable          | 5           |
------------------------------------------------

Table ``fruit``
---------------
| id  | name  |
---------------
| 8   | apple |
---------------

Table ``vegetable``
----------------
| id  | name   |
----------------
| 5   | carrot |
----------------

预期的结果表应该是：

----------------------
| foo | bar | name   |
----------------------
|  A  |  1  | apple  |
|  B  |  2  | carrot |
----------------------

编辑：我添加了完整的表格示例，以便更清晰地说明。

- vaab

---

你需要使用动态SQL/预处理语句来完成这个任务。在基本的SQL查询中，你必须有一个明确的表。 - Gordon Linoff

请查看PL/PgSQL中的EXECUTE命令。或者从您的客户端生成查询。 - Craig Ringer

@CraigRinger 我已经看过“EXECUTE”语句了，但即使这样我也没有找到解决方案。所以如果你有什么想法，请分享一下。 - vaab

Answer 1

通常情况下，在客户端执行这种操作要容易得多，但如果你想的话，使用PL/PgSQL也是可以的，例如：

CREATE OR REPLACE FUNCTION dynamic_call(tblname text)
RETURNS TABLE (foo int, bar text, fname text)
AS $$
BEGIN
  RETURN QUERY EXECUTE format('
    SELECT t.foo, table.bar, f."name"
    FROM mytable t
    JOIN %I AS f ON (f.id = t.foreign_key);', tblname);
END;
$$ LANGUAGE plpgsql;

如需更多信息，请参阅PL/PgSQL文档。

- Craig Ringer

---

我在陈述我的问题时可能没有表达清楚，所以我编辑了一下并添加了一个实际的例子。您的答案对我没有帮助，因为：1-这是一个函数，而我要求的是一个表格；2-表格名称必须来自基本表列“foreign_table_name”。然而，在阅读您的建议时我学到了一些plpgsql，尤其是“RETURNS TABLE”，这是我之前遗漏的。有了这个，我可能能找到一个解决方案。谢谢。 - vaab

@vaab，您不能在不使用动态SQL的情况下完成此操作，因此必须使用PL/PgSQL函数或客户端生成的SQL。 - Craig Ringer