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)