Clarify unnest example in FunctionElement.alias documentation

[Sebastian Bank]

Using func.unnest to unpack a postgres array:

from sqlalchemy import Column, Integer, Text, select, column, func
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.ext.declarative import declarative_base

class Post(declarative_base()):

    __tablename__ = 'post'

    id = Column(Integer, primary_key=True)
    title = Column(Text, nullable=False)
    tags = Column(ARRAY(Text), nullable=False)

query = select([Post.id, column('tag')])\
    .select_from(func.unnest(Post.tags).alias('tag'))

print(query)

As wanted, this gives SELECT post.id, tag FROM post, unnest(post.tags) AS tag (which is postgres-specific syntax, I guess).

I found the example given in the documentation of FunctionElement.alias somewhat confusing because the SELECT-clause does not actually use the unpacked data:

stmt = select([column('data')]).select_from(
    func.unnest(Table.data).alias('data_view'))

Wouldn't it make more sense like this (following the example above)?

stmt = select([column('item')]).select_from(
    func.unnest(Table.data).alias('item'))

Or maybe this is not the right use of FunctionElement.alias?

[Mike Bayer]

ummm...it is. These PG functions are an ongoing crapshow and I've been capturing similar issues in #3566, and your posting this issue made me aware that part of that issue is already implemented here as part of #3137. the documented usage isn't accepted by Postgresql so we'll change it.

[Mike Bayer]

• use same colname as the alias we give to the PG function here, fixes #3652

→ <<cset e310a8e423d9>>

[Mike Bayer]

• use same colname as the alias we give to the PG function here, fixes #3652

(cherry picked from commit e310a8e423d9a4eeb511b7b84dbeccc90c234a1f)

→ <<cset aec9bb16e62f>>

[Mike Bayer]

• use same colname as the alias we give to the PG function here, fixes #3652

(cherry picked from commit e310a8e423d9a4eeb511b7b84dbeccc90c234a1f)

→ <<cset 397890293deb>>

[Sebastian Bank]

Oh, I didn't realize that postgres rejects the (formerly) documented usage.

Then there might still be an issue/todo here (the FROM posts, is required, but sqlalchemy only renders it when also selecting something else). Contiuing from above:

import random

from sqlalchemy import create_engine, insert

engine = create_engine('postgresql://postgres@/spam', echo=True)

Post.metadata.drop_all(engine)
Post.metadata.create_all(engine)

tagnames = ['tag-%d' % i for i in range(1, 11)]

insert(Post, bind=engine).execute([
    {'title': 'Post %d' % i, 'tags': random.sample(tagnames, 3)}
    for i in range(1, 10)])

# works
select([Post.id, column('tag')], bind=engine)\
    .select_from(func.unnest(Post.tags).alias('tag'))\
    .limit(5).execute().fetchall()

# raises psycopg2.ProgrammingError
select([column('tag')], bind=engine)\
    .select_from(func.unnest(Post.tags).alias('tag'))\
    .limit(5).execute().fetchall()

[Mike Bayer]

Well this is postgresql and their "SQL". I hardly know where they're going with this but here is the only SQL that works:

SELECT tag 
FROM post, unnest(post.tags) AS tag 
 LIMIT %(param_1)s

if you just change the order of the FROM clauses, it fails, nothing in SQL is like this:

SELECT tag 
FROM unnest(post.tags) AS tag, post 
 LIMIT %(param_1)s

so, I don't really understand PG's interpretation of functions in the FROM, let me know what SQL it wants. You can get both of these just by calling two select_from()'s, the order is taken into account:

select([column('tag')], bind=engine)\
    .select_from(Post).select_from(func.unnest(Post.tags).alias('tag'))\
    .limit(5).execute().fetchall()

[Sebastian Bank]

Ah, thanks, I did not know one can chain select_from this way (I tried a subselect).

So the example can just be changed to this, right?

stmt = select([column('data_view')]).select_from(Table).select_from(
    func.unnest(Table.data).alias('data_view')
)

[Mike Bayer]

Sure I could have swore I tried that SQL out but I guess I had the other column stuck in there

[Mike Bayer]

see c97aa63789036fc145503f03123275253ae02d2c and backports