使用PostgreSQL进行查询的Coditional连接(Conditional join of query using PostgreSQL)

使用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 VOLATILE

This 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 ....