Using text() in a join
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
Comments (5)
-
repo owner -
repo owner - changed status to duplicate
Duplicate of #3566.
-
reporter Wow! The example code you gave on #3566 is great. Thank you!
-
reporter 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.
-
repo owner 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.
- Log in to comment
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.