使用另一个查询的输出动态执行查询 [英] Dynamically execute query using the output of another query
问题描述
我的查询是这样的:
select max(rundate)as rundate,branch
from t_index_of_imported_files
group by branch
,结果如下:
rundate; branch
2014-03-13; branch1
2014-03-12; branch2
2014-03-10; branch3
2014-03-13; branch4
,我需要的是该函数运行这样的东西
select generate_table '2014-03-13'; 'BRANCH1');
select generate_table('2014-03-12';'branch2');
select generate_table('2014-03-10';'branch3');
select generate_table('2014-03-13';'branch4'); 200新200新新旗新新新旗新新新旗新新200新旗新新旗旗新200 200 200 200 CE 200 200 200 X-新新200新新新新旗新新200新新旗新新旗新新旗新200旗新新旗新200旗新新旗新200旗新新旗新200旗新新旗新200旗新新旗新200旗新新旗新200新新旗旗新新旗新200新新新旗新200新新旗新新旗新200新新新旗新200新新新旗新200新新新旗新200新新旗新新旗新200新新新旗新200新新旗新新旗新200新新新旗新200新新旗新旗新新旗新200新新新旗新200新新新旗新200新新新旗新200新新新旗新200新新新旗新200新新新旗新200新新新旗新200新新新旗200新新新200 200 200 X-正常。
有关如何做到这一点的建议?
解决方案您可以使用新的 GROUP BY分支
)t
,generate_table(t.rundate,t.branch)g; - LATERAL隐含在这里
LATERAL
也可以在函数调用FROM
项之前,但在
的情况下它是一个噪音字,因为在任何情况下,函数表达式可以引用
以前的FROM
项。
在旧版本中,通过在 SELECT
列表中展开设置返回函数的行可以使用旧的版本,但使用 LATERAL
更清洁。无论如何,对于Postgres 9.2或更旧版本:
SELECT generate_table(max(rundate),branch)
FROM t_index_of_imported_files
GROUP BY分支;
I have a function called generate_table, that takes 2 input parameters (rundate::date
and branch::varchar
)
Now I am trying to work on a second function, using PLPGSQL, that will get a list of all branches and the newest date for each branch and pass this as a parameter to the generate_table function.
The query that I have is this:
select max(rundate) as rundate, branch
from t_index_of_imported_files
group by branch
and it results on this:
rundate;branch
2014-03-13;branch1
2014-03-12;branch2
2014-03-10;branch3
2014-03-13;branch4
and what I need is that the function run something like this
select generate_table('2014-03-13';'branch1');
select generate_table('2014-03-12';'branch2');
select generate_table('2014-03-10';'branch3');
select generate_table('2014-03-13';'branch4');
I've been reading a lot about PLPGSQL but so far I can only say that I barely know the basics.
I read that one could use a concatenation in order to get all the values together and then use a EXECUTE within the function, but I couldn't make it work properly.
Any suggestions on how to do this?
You can do this with a plain SELECT query using the new LATERAL JOIN
in Postgres 9.3+
SELECT *
FROM (
SELECT max(rundate) AS rundate, branch
FROM t_index_of_imported_files
GROUP BY branch
) t
, generate_table(t.rundate, t.branch) g; -- LATERAL is implicit here
LATERAL
can also precede a function-callFROM
item, but in this case it is a noise word, because the function expression can refer to earlierFROM
items in any case.
The same is possible in older versions by expanding rows for set-returning functions in the SELECT
list, but the new syntax with LATERAL
is much cleaner. Anyway, for Postgres 9.2 or older:
SELECT generate_table(max(rundate), branch)
FROM t_index_of_imported_files
GROUP BY branch;
这篇关于使用另一个查询的输出动态执行查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!