Using text() in a join

[sqlalchemy-bot]

Migrated issue, originally created by Alex Fraser (@z0u)

I want to use Postgres' unnest function in a JOIN clause. If I use func.unnest(), I can't use the WITH ORDINALITY feature. If I use text(), the expression gets wrapped in parentheses which is illegal. If I use select(), the query fails because it needs the LATERAL keyword, which is apparently unsupported (#2857).

Attached is a script that demonstrates the problem. I would like the final query to produce SQL like this:

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

But actually it produces this:

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

Which gives this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near ")"
LINE 2: ... a LEFT OUTER JOIN (unnest(a.refs) WITH ORDINALITY) ON TRUE ...
                                                             ^

I'd really to give an alias to the set, so that unnest could be used multiple times:

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

But x.alias('x(unnest, ordinality)') results in constructs like AS "x(unnest, ordinality)" and "x(unnest, ordinality)".unnest = b.ref.

So, is there a way to use text 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.

SA version 1.0.9, Python 3.4.3, Postgres 9.4.3

---

Attachments: join_text.py

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Fortunately this fits into the recipe at #3566 which is the current catchall for the many varieties of Postgresql functions-in-the-FROM-that-become-columns and other syntaxes, so using the compiler recipe I provided in https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs#comment-23564706 these syntaxes, or good enough equivalents, should be possible. as far as how to make patterns like these built-in and intuitive somehow, the work of mapping out all the varieties of PG function syntaxes, all of which are entirely non-SQL inventions of Postgresql, just keeps getting bigger and more complicated.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Duplicate of #3566.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: duplicate
• changed status to closed

[sqlalchemy-bot]

Alex Fraser (@z0u) wrote:

Wow! The example code you gave on #3566 is great. Thank you!

[sqlalchemy-bot]

Alex Fraser (@z0u) wrote:

By the way, I asked a related question on Stack Overflow. I'd gladly accept an answer that simply pointed to this issue, if you're interested. Otherwise I'll answer it myself.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

ah well that's great! thought it would be frustrating that all I have for this use case is spare parts that have to be hacked together. This whole thing needs to be built for real so...feel free to point your SO question to that ticket but that is only an interim solution until an API that meets all the cases we're collecting can be worked out.