左联接与从列派生的动态表名称 [英] Left join with dynamic table name derived from column

问题描述

我是PostgreSQL的新手，我想知道是否有可能使用表 tbc 中的 number 左联接中的表名'pa'||数字。因此，例如，如果数字为456887，我想与表pa456887左连接。像这样：

I am new in PostgreSQL and I wonder if it's possible to use number from table tbc as part of the table name in left join 'pa' || number. So for example if number is 456887 I want left join with table pa456887. Something like this:

SELECT tdc.cpa, substring(tdc.ku,'[0-9]+') AS number, paTab.vym 
FROM public."table_data_C" AS tdc
LEFT JOIN concat('pa' || number) AS paTab ON (paTab.cpa = tdc.cpa)

我只想使用PostgreSQL，而不要使用PHP中的其他代码。

And I want to use only PostgreSQL, not additional code in PHP for example.

推荐答案

无论哪种方式，您都需要动态SQL。

Either way, you need dynamic SQL.

CREATE OR REPLACE FUNCTION foo(_number int)
  RETURNS TABLE (cpa int, nr text, vym text) AS  -- adapt to actual data types!
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT t.cpa, substring(t.ku,'[0-9]+'), p.vym 
       FROM   public."table_data_C" t
       LEFT   JOIN %s p USING (cpa)'
     , 'pa' || _number
     );
END
$func$ LANGUAGE plpgsql;

致电：

SELECT * FROM foo(456887)

通常，您可以使用 format（％I）以避免SQL注入。只需使用整数作为动态输入，就没有必要了。此相关答案中的更多详细信息和链接：

在触发函数中使用动态表名称进行插入

Generally, you would sanitize table names with format ( %I ) to avoid SQL injection. With just an integer as dynamic input that's not necessary. More details and links in this related answer:
INSERT with dynamic table name in trigger function

成为数据模型的充分理由。例如分区/分片或单独的特权...

如果您没有这么好的理由，请考虑将具有相同模式的多个表合并为一个并添加 number 作为列。那么您就不需要动态SQL。

There may be good reasons for the data model. Like partitioning / sharding or separate privileges ...
If you don't have such a good reason, consider consolidating multiple tables with identical schema into one and add the number as column. Then you don't need dynamic SQL.

请考虑继承力。然后，您可以在 tableoid 上添加条件，以仅从给定的子表中检索行：

Consider inheritance. Then you can add a condition on tableoid to only retrieve rows from a given child table:

SELECT * FROM parent_table
WHERE  tableoid = 'pa456887'::regclass

请注意继承的限制。 相关答案：

• 在查询行源表的父表时，获取行源表的名称


• 使用Postgres从多个架构中选择（检索）所有记录

• Get the name of a row's source table when querying the parent it inherits from
• Select (retrieve) all records from multiple schemas using Postgres

从第一个表中的值导出联接表的名称会动态地使事情复杂化。

Deriving the name of the join table from values in the first table dynamically complicates things.

左联接在 tableoid 上。每行只有一个匹配项，因此请使用 COALESCE 。

LEFT JOIN each on tableoid. There is only one match per row, so use COALESCE.

SELECT t.*, t.tbl, COALESCE(p1.vym, p2.vym, p3.vym) AS vym
FROM  (
   SELECT cpa, ('pa' || substring(ku,'[0-9]+'))::regclass AS tbl
   FROM   public."table_data_C"
   -- WHERE <some condition>
   ) t
LEFT   JOIN pa456887 p1 ON p1.cpa = t.cpa AND p1.tableoid = t.tbl
LEFT   JOIN pa456888 p2 ON p2.cpa = t.cpa AND p2.tableoid = t.tbl
LEFT   JOIN pa456889 p3 ON p3.cpa = t.cpa AND p3.tableoid = t.tbl

对于许多表

使用动态查询合并循环：

For many tables

Combine a loop with dynamic queries:

CREATE OR REPLACE FUNCTION foo(_number int)
  RETURNS TABLE (cpa int, nr text, vym text) AS
$func$
DECLARE
   _nr text;
BEGIN
FOR _nr IN
   SELECT DISTINCT substring(ku,'[0-9]+')
   FROM   public."table_data_C"
LOOP
   RETURN QUERY EXECUTE format(
      'SELECT t.cpa, _nr, p.vym 
       FROM   public."table_data_C" t
       LEFT   JOIN %I p USING (cpa)
       WHERE  t.ku LIKE (_nr || '%')'
     , 'pa' || _nr
     );
END LOOP;

END
$func$ LANGUAGE plpgsql;

这篇关于左联接与从列派生的动态表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！