使用PostgreSQL进行查询的Coditional连接(Conditional join of query using PostgreSQL)
我有一个情况,我需要在每个函数变量的连接中运行不同的查询
目前我有一个功能:
CREATE OR REPLACE FUNCTION a(type_f boolean) RETURNS SETOF rowtypes AS $$ declare row rowtypes ; begin .... select... from.. left join (select pp.a, avg(ppp.priceusd) as avgpricetf from pp join p on (p.b=pp.b) join (...) as ppp on (pp.c=p.c) group by pp.a) tfquery on (tfquery.a=main.a) .... end; $$ LANGUAGE plpgsql VOLATILE这工作正常。
我想修改它,所以当type_f=True这个查询将运行在原始连接的instad中:
left join (select pp.a,p.d, avg(ppp.priceusd) as avgpricetf from pp join p on (p.b=pp.b) join (...) as ppp on (pp.c=p.c) group by pp.a,p.d) tfquery on (tfquery.a=main.a and tfquery.d=main.d)正如你所看到的,查询和连接本身的条件一样。
基本上 :
当type_f=False时:
left join (select pp.a, avg(ppp.priceusd) as avgpricetf from pp join p on (p.b=pp.b) join (...) as ppp on (pp.c=p.c) group by pp.a) tfquery on (tfquery.a=main.a)当: type_f=True时:
left join (select pp.a,p.d, avg(ppp.priceusd) as avgpricetf from pp join p on (p.b=pp.b) join (...) as ppp on (pp.c=p.c) group by pp.a,p.d) tfquery on (tfquery.a=main.a and tfquery.d=main.d)我怎样才能做到这一点?
I have a case where I need to run different query in join per function variable
Currently I have a function as:
CREATE OR REPLACE FUNCTION a(type_f boolean) RETURNS SETOF rowtypes AS $$ declare row rowtypes ; begin .... select... from.. left join (select pp.a, avg(ppp.priceusd) as avgpricetf from pp join p on (p.b=pp.b) join (...) as ppp on (pp.c=p.c) group by pp.a) tfquery on (tfquery.a=main.a) .... end; $$ LANGUAGE plpgsql VOLATILEThis works fine.
I want to modify it so when type_f=True this query will run in the join instad of the original one:
left join (select pp.a,p.d, avg(ppp.priceusd) as avgpricetf from pp join p on (p.b=pp.b) join (...) as ppp on (pp.c=p.c) group by pp.a,p.d) tfquery on (tfquery.a=main.a and tfquery.d=main.d)As you can see the query is changed as well as the condition of the join itself.
Basicly:
When type_f=False do:
left join (select pp.a, avg(ppp.priceusd) as avgpricetf from pp join p on (p.b=pp.b) join (...) as ppp on (pp.c=p.c) group by pp.a) tfquery on (tfquery.a=main.a)When: type_f=True do:
left join (select pp.a,p.d, avg(ppp.priceusd) as avgpricetf from pp join p on (p.b=pp.b) join (...) as ppp on (pp.c=p.c) group by pp.a,p.d) tfquery on (tfquery.a=main.a and tfquery.d=main.d)How can I do that?
最满意答案
您可以使用这样的逻辑来杀死所有可能的查询,并获得预期的结果:
Select * from A left outer join ( select * from B where type_f union all select *, A.field from C where not type_f ) X on X.n = A.n and X.field = A.field ....You can use logic like this to kill all possible readability to your query and get expected results:
Select * from A left outer join ( select * from B where type_f union all select *, A.field from C where not type_f ) X on X.n = A.n and X.field = A.field ....更多推荐
发布评论