how do you do : AS x(unnest, ordinality) ?

[Benoit Barthelet]

I'm trying to "translate" this query in sqlalchemy core.

s="""
select l.field3, l.field2, l.field4, l.field1
from lake l
join (
  select *
  from unnest(array['ticker3','ticker1', 'ticker2']) with ordinality
) as x (id, ordering) on l.ticker = x.id
where time = '2019-06-28'
order by x.ordering;
"""

I managed to get quite close thanks to recipes from https://github.com/sqlalchemy/sqlalchemy/issues/3566 

However I fail at getting a proper alias for the left part of the join. The issue mentions:

To get the form with "AS x(unnest, ordinality)" you'd need to add another construct like a subclass of Alias or something, however it seems like the "unnest" construct can be aliased in the tradtional way and the column names become "(function name, ordinality)". Again the amount of special functions and behaviors that would be part of a standard feature keep on growing, making this a difficult feature to add as "done" without missing one of PG's weird non-SQL things.

I've not been able to write the AS part of the query as described above.

Here's where I'm at (see below for full code)

I chncked the query in several parts and ultimately I'd like to join s1 with s3, s1 works fine, s3 too, but s3 miss the alias to be able to perform the join

• s1 (SELECT field3, field2, field4, field1 FROM lake) 
• s3 (SELECT * FROM unnest(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) WITH ORDINALITY AS anon_1), 

from sqlalchemy import Table, Column, Integer, String, MetaData, Date, Text, Float, func, alias
from sqlalchemy.dialects.postgresql import array
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import column
from sqlalchemy.sql import select

from sqlalchemy import create_engine

from utils import ColumnFunction

engine = create_engine('postgresql://postgres:postgres@localhost:5433/sfj', echo=True)

metadata = MetaData()
lake = Table('lake', metadata,
Column('time', Date, primary_key=True),
Column('ticker', Text, primary_key=True),
Column('field1', Integer),
Column('field2', String),
Column('field3', Float),
Column('field4', String),
)
conn = engine.connect()


s="""
select l.field3, l.field2, l.field4, l.field1
from lake l
join (
  select *
  from unnest(array['ticker3','ticker1', 'ticker2']) with ordinality
) as x (id, ordering) on l.ticker = x.id
where time = '2019-06-28'
order by x.ordering;
"""

fields_asked = ['field3', 'field2', 'field4', 'field1']
columns_asked = [column(fa) for fa in fields_asked]
s1 = select(columns_asked, from_obj=lake)
result = conn.execute(s1)
print('s1')
# seems ok
# SELECT field3, field2, field4, field1 FROM lake
print(result.fetchall())

ticker_arr= array(['ticker3', 'ticker1', 'ticker2'])
s2 = select(['*']).select_from(alias(func.unnest(ticker_arr)))
result = conn.execute(s2)
print('s2')
print(result.fetchall())


class unnest_func(ColumnFunction):
    name = 'unnest'
    column_names = ['unnest', 'ordinality']


@compiles(unnest_func)
def _compile_unnest_func(element, compiler, **kw):
    return compiler.visit_function(element, **kw) + " WITH ORDINALITY"


s3 = select(['*']).select_from(alias(unnest_func(ticker_arr)))
result = conn.execute(s3)
print('s3')
# seems ok
# SELECT * FROM unnest(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) WITH ORDINALITY AS anon_1
print(result.fetchall())


# s4 = s1.join(s3)
# result = conn.execute(s4)
# print('s4')
# print(result.fetchall())


conn.close()

[Benoit Barthelet]

ok solved it :) it seems postgresql needs alias everywhere !

from sqlalchemy import Table, Column, Integer, String, MetaData, Date, Text, Float, alias
from sqlalchemy.dialects import postgresql
from sqlalchemy.dialects.postgresql import array

from sqlalchemy.sql import column
from sqlalchemy.sql import select

from sqlalchemy import create_engine

from utils import unnest_func

engine = create_engine('postgresql://postgres:postgres@localhost:5433/sfj', echo=True)

metadata = MetaData()
lake = Table('lake', metadata,
             Column('time', Date, primary_key=True),
             Column('ticker', Text, primary_key=True),
             Column('field1', Integer),
             Column('field2', String),
             Column('field3', Float),
             Column('field4', String),
             )
conn = engine.connect()

s = """

select l.field3, l.field2, l.field4, l.field1
from lake l
         join (

    select x.id, x.ordering

    from unnest(array ['ticker3','ticker1', 'ticker2']) with ordinality

             as x (id, ordering)) as r on l.ticker = r.id
where time = '2019-06-28'
order by r.ordering;
"""

result = conn.execute(s)
print('s')
print(result.fetchall())

l = alias(lake)

fields_asked = ['field3', 'field2', 'field4', 'field1']
columns_asked = [column(fa) for fa in fields_asked]

ticker_arr = array(['ticker3', 'ticker1', 'ticker2'])
x = unnest_func(ticker_arr).alias('x')
r = select([x.c.unnest, x.c.ordinality]).select_from(x).alias('r')

stmt = select(columns_asked).select_from(l.join(r, l.c.ticker == r.c.unnest)).where(
    l.c.time == '2019-06-28').order_by(r.c.ordinality)
print('stmt')
print(str(stmt.compile(dialect=postgresql.dialect())))
result = conn.execute(stmt)
print(result.fetchall())

conn.close()