Coditional join of query using PostgreSQL

On Mon, May 15, 2017 at 10:02 AM, Nick Dro <postgresql@walla.co.il> wrote:

Hi,
I'm new to postgresql and couldn't find answer to this situation anywhere.
I asked this here:
http://stackoverflow.com/questions/43984208/coditional-
join-of-query-using-postgresql

I hope there is a better solution rather than creating two separated
functions :(

​Generate the SQL itself in a string then execute the string. Its called
"Dynamic SQL"​.

https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

David J.

This is a join in a middle of query.
How can I do such thing?

--
View this message in context: http://www.postgresql-archive.org/Coditional-join-of-query-using-PostgreSQL-tp5961718p5961726.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

<div><div dir="rtl"><div>This is a join in a middle of query.<br><br><br></div>
<div>How can I use dynamic SQL in the middle of query?</div></div><section class="cust_msg_end"></section><blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0;"><br>ב מאי 15, 2017 20:26, David G. Johnston כתב:<blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0"><div dir="ltr"><div style="font-family:arial,helvetica,sans-serif"><span style="font-family:arial,sans-serif">On Mon, May 15, 2017 at 10:02 AM, Nick Dro </span><span dir="ltr" style="font-family:arial,sans-serif">&lt;<a href="mailto:postgresql@walla.co.il" target="_blank">postgresql@walla.co.il</a>&gt;</span><span style="font-family:arial,sans-serif"> wrote:</span><br></div><div><div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="rtl"><div dir="rtl">
<div dir="ltr">Hi, <br>I'm new to postgresql and couldn't find answer to this situation anywhere. <br>I asked this here: <br><a href="http://stackoverflow.com/questions/43984208/coditional-join-of-query-using-postgresql&quot; rel="nofollow" target="_blank">http://stackoverflow.com/&lt;wbr&gt;questions/43984208/coditional-&lt;wbr&gt;join-of-query-using-postgresql&lt;/a&gt;&lt;br&gt;&lt;br&gt;I hope there is a better solution rather than creating two separated functions :(</div>
</div></div></blockquote></div><br></div><div><div style="font-family:arial,helvetica,sans-serif">​Generate the SQL itself in a string then execute the string.&nbsp; Its called "Dynamic SQL"​.</div><div style="font-family:arial,helvetica,sans-serif"><br></div><div><font face="arial, helvetica, sans-serif"><a href="https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN&quot;&gt;https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN&lt;/a&gt;&lt;/font&gt;&lt;br&gt;&lt;/div&gt;&lt;div&gt;&lt;font face="arial, helvetica, sans-serif"><br></font></div><div><font face="arial, helvetica, sans-serif">David J.</font></div><br></div></div>

<br></blockquote></blockquote><br></div>

On 15 May 2017, at 19:02, Nick Dro <postgresql@walla.co.il> wrote:

Hi,
I'm new to postgresql and couldn't find answer to this situation anywhere.
I asked this here:
http://stackoverflow.com/questions/43984208/coditional-join-of-query-using-postgresql

I hope there is a better solution rather than creating two separated functions :(

You can use your boolean parameter inside the join condition:
[…] on (tfquery.a = main.a and ((type_f and tfquery.d = main.d) or not type_f))

Beware that you don't also have a column named type_f in that join somewhere.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general