左联接与从列派生的动态表名称 [英] 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
请注意继承的限制。 相关答案:
- 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屋!