如何在Postgres中使用动态标识符联接表? [英] How to join table with dynamic identifier in postgres?

问题描述

我有一个表名table，其中包含两列foreign_table_name和foreign_key.

I have a table name table containing two columns foreign_table_name, and foreign_key.

是否可以编写一个SELECT查询，该查询将对该表的值以及在foreign_table_name列中指定名称的表的值JOIN?

Is it possible to write a SELECT query that would JOIN values of this table and the table which name is specified in the column foreign_table_name ?

例如，如果我们知道所有可能的目标外部表都有一个name字段，那么我想知道是否可以编写以下内容:

For instance, if we know that all possible targetted foreign tables have a name field, I would like to know if I could write something that would:

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的任何解决方案都是可以接受的.

Any solution using PlpgSQL is of course accepted.

这是一个简单的内容:

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

我添加了完整的表格示例，以期变得更加清晰.

I added the full table example in an attempt to be clearer.

推荐答案

在客户端执行这种操作通常更容易，但是如果您希望PL/PgSQL可以实现，例如

It's usually way easier to do this sort of thing on the client side, but if you want it's possible with PL/PgSQL, e.g.

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文档.

For more information, see the PL/PgSQL documentation.

这篇关于如何在Postgres中使用动态标识符联接表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！