<p dir="auto"><strong>Migrated issue, originally created by Alex Fraser (<a href="https://github.com/z0u">@z0u</a>)</strong></p>
<p dir="auto">I want to use Postgres' <code class="notranslate">unnest</code> function in a <code class="notranslate">JOIN</code> clause. If I use <code class="notranslate">func.unnest()</code>, I can't use the <code class="notranslate">WITH ORDINALITY</code> feature. If I use <code class="notranslate">text()</code>, the expression gets wrapped in parentheses which is illegal. If I use <code class="notranslate">select()</code>, the query fails because it needs the <code class="notranslate">LATERAL</code> keyword, which is apparently unsupported (<a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384628825" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/2857" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/2857/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/2857">#2857</a>).</p>
<p dir="auto">Attached is a script that demonstrates the problem. I would like the final query to produce SQL like this:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT a.id AS a_id, a.refs AS a_refs, unnest AS unnest, ordinality AS ordinality, b.id AS b_id, b.ref AS b_ref 
FROM a
    LEFT OUTER JOIN unnest(a.refs) WITH ORDINALITY ON TRUE
    LEFT OUTER JOIN b ON unnest = b.ref
ORDER BY a.id, ordinality"><pre class="notranslate"><span class="pl-k">SELECT</span> <span class="pl-c1">a</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> a_id, <span class="pl-c1">a</span>.<span class="pl-c1">refs</span> <span class="pl-k">AS</span> a_refs, unnest <span class="pl-k">AS</span> unnest, ordinality <span class="pl-k">AS</span> ordinality, <span class="pl-c1">b</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> b_id, <span class="pl-c1">b</span>.<span class="pl-c1">ref</span> <span class="pl-k">AS</span> b_ref 
<span class="pl-k">FROM</span> a
    <span class="pl-k">LEFT OUTER JOIN</span> unnest(<span class="pl-c1">a</span>.<span class="pl-c1">refs</span>) WITH ORDINALITY <span class="pl-k">ON</span> TRUE
    <span class="pl-k">LEFT OUTER JOIN</span> b <span class="pl-k">ON</span> unnest <span class="pl-k">=</span> <span class="pl-c1">b</span>.<span class="pl-c1">ref</span>
<span class="pl-k">ORDER BY</span> <span class="pl-c1">a</span>.<span class="pl-c1">id</span>, ordinality</pre></div>
<p dir="auto">But actually it produces this:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT a.id AS a_id, a.refs AS a_refs, unnest AS unnest, ordinality AS ordinality, b.id AS b_id, b.ref AS b_ref 
FROM a
    LEFT OUTER JOIN (unnest(a.refs) WITH ORDINALITY) ON TRUE
    LEFT OUTER JOIN b ON unnest = b.ref
ORDER BY a.id, ordinality"><pre class="notranslate"><span class="pl-k">SELECT</span> <span class="pl-c1">a</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> a_id, <span class="pl-c1">a</span>.<span class="pl-c1">refs</span> <span class="pl-k">AS</span> a_refs, unnest <span class="pl-k">AS</span> unnest, ordinality <span class="pl-k">AS</span> ordinality, <span class="pl-c1">b</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> b_id, <span class="pl-c1">b</span>.<span class="pl-c1">ref</span> <span class="pl-k">AS</span> b_ref 
<span class="pl-k">FROM</span> a
    <span class="pl-k">LEFT OUTER JOIN</span> (unnest(<span class="pl-c1">a</span>.<span class="pl-c1">refs</span>) WITH ORDINALITY) <span class="pl-k">ON</span> TRUE
    <span class="pl-k">LEFT OUTER JOIN</span> b <span class="pl-k">ON</span> unnest <span class="pl-k">=</span> <span class="pl-c1">b</span>.<span class="pl-c1">ref</span>
<span class="pl-k">ORDER BY</span> <span class="pl-c1">a</span>.<span class="pl-c1">id</span>, ordinality</pre></div>
<p dir="auto">Which gives this error:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near &quot;)&quot;
LINE 2: ... a LEFT OUTER JOIN (unnest(a.refs) WITH ORDINALITY) ON TRUE ...
                                                             ^"><pre class="notranslate"><span class="pl-c1">sqlalchemy</span>.<span class="pl-c1">exc</span>.ProgrammingError: (<span class="pl-c1">psycopg2</span>.<span class="pl-c1">ProgrammingError</span>) syntax error at <span class="pl-k">or</span> near <span class="pl-s"><span class="pl-pds">"</span>)<span class="pl-pds">"</span></span>
<span class="pl-k">LINE</span> <span class="pl-c1">2</span>: ... a <span class="pl-k">LEFT OUTER JOIN</span> (unnest(<span class="pl-c1">a</span>.<span class="pl-c1">refs</span>) WITH ORDINALITY) <span class="pl-k">ON</span> TRUE ...
                                                             ^</pre></div>
<p dir="auto">I'd really to give an alias to the set, so that <code class="notranslate">unnest</code> could be used multiple times:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT a.id AS a_id, a.refs AS a_refs, x.unnest AS x_unnest, x.ordinality AS x_ordinality, b.id AS b_id, b.ref AS b_ref 
FROM a
    LEFT OUTER JOIN unnest(a.refs) WITH ORDINALITY AS x(unnest, ordinality) ON TRUE
    LEFT OUTER JOIN b ON x.unnest = b.ref
ORDER BY a.id, x.ordinality"><pre class="notranslate"><span class="pl-k">SELECT</span> <span class="pl-c1">a</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> a_id, <span class="pl-c1">a</span>.<span class="pl-c1">refs</span> <span class="pl-k">AS</span> a_refs, <span class="pl-c1">x</span>.<span class="pl-c1">unnest</span> <span class="pl-k">AS</span> x_unnest, <span class="pl-c1">x</span>.<span class="pl-c1">ordinality</span> <span class="pl-k">AS</span> x_ordinality, <span class="pl-c1">b</span>.<span class="pl-c1">id</span> <span class="pl-k">AS</span> b_id, <span class="pl-c1">b</span>.<span class="pl-c1">ref</span> <span class="pl-k">AS</span> b_ref 
<span class="pl-k">FROM</span> a
    <span class="pl-k">LEFT OUTER JOIN</span> unnest(<span class="pl-c1">a</span>.<span class="pl-c1">refs</span>) WITH ORDINALITY <span class="pl-k">AS</span> x(unnest, ordinality) <span class="pl-k">ON</span> TRUE
    <span class="pl-k">LEFT OUTER JOIN</span> b <span class="pl-k">ON</span> <span class="pl-c1">x</span>.<span class="pl-c1">unnest</span> <span class="pl-k">=</span> <span class="pl-c1">b</span>.<span class="pl-c1">ref</span>
<span class="pl-k">ORDER BY</span> <span class="pl-c1">a</span>.<span class="pl-c1">id</span>, <span class="pl-c1">x</span>.<span class="pl-c1">ordinality</span></pre></div>
<p dir="auto">But <code class="notranslate">x.alias('x(unnest, ordinality)')</code> results in constructs like <code class="notranslate">AS "x(unnest, ordinality)"</code> and <code class="notranslate">"x(unnest, ordinality)".unnest = b.ref</code>.</p>
<p dir="auto">So, is there a way to use <code class="notranslate">text</code> in a join clause without it being put in parentheses? Or is there something else I'm missing? I would be happy to concede that I'm going about this the wrong way.</p>
<p dir="auto">SA version 1.0.9, Python 3.4.3, Postgres 9.4.3</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3594/join_text.py">join_text.py</a></p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">that is, all the functions in <a href="http://www.postgresql.org/docs/9.4/static/functions-json.html" rel="nofollow">http://www.postgresql.org/docs/9.4/static/functions-json.html</a> need to be possible without building subclasses of functions.</p>