如何在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屋!