使用从列派生的动态表名进行左连接

Question

使用从列派生的动态表名进行左连接

sqlpostgresqldynamicdynamic-sqltablename

8

我是PostgreSQL的新手，我想知道是否可以在左连接中使用表中的number作为表名的一部分，例如'pa' || number。所以如果number是456887，我想要与表pa456887进行左连接。类似这样：

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中添加额外的代码。

- Lubos K.

5

听起来这个数据模型很糟糕，你应该改进它。 - user330315

@a_horse_with_no_name 我同意，但是如果你有这样的需求，如何解决？ - chrismarx

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

无论哪种方式，您都需要动态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 注入。但如果只有一个作为动态输入的integer，这是不必要的。更多细节和相关链接请参见此答案：
INSERT with dynamic table name in trigger function

数据模型

可能存在数据模型的良好原因，例如分区/分片或单独的特权...
如果您没有这样一个很好的原因，请考虑将具有相同模式的多个表合并为一个表，并添加number作为列。然后您就不需要动态 SQL。

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

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

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

根据第一张表中的值获取第二张表的名称

从第一张表中的值动态派生联接表的名称会使事情变得复杂。

对于只有几个表

LEFT JOIN每个表到tableoid上。每行只有一个匹配项，因此使用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

对于许多表格

将循环与动态查询结合使用：

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;