Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Emulate the PostgreSQL ROWS FROM () clause for "ordinary tables" and other SQL dialects #4310

Open
lukaseder opened this issue May 27, 2015 · 2 comments
Open

Emulate the PostgreSQL ROWS FROM () clause for "ordinary tables" and other SQL dialects #4310

lukaseder opened this issue May 27, 2015 · 2 comments
Labels
C: Functionality P: Medium T: Enhancement
Milestone
Version X: Long-t...

Comments

@lukaseder
Copy link
Member

The recently implemented #4305 adds support for the ROWS FROM () clause, which is supported only in PostgreSQL and only for table valued functions. It allows for "merging" the rows of several functions in to a single table.

The following two expressions are equivalent:

PostgreSQL / table-valued functions:

SELECT * FROM ROWS FROM (f1(), f2())

Other DBs / other tables:

SELECT *
FROM (
    SELECT f1.*, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn FROM f1()
) a
FULL OUTER JOIN (
    SELECT f2.*, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn FROM f2()
) b
USING (rn)

This could be quite useful in some situations
@blmille1
Copy link

I thank you for posting this. I saw "ROWS FROM" in the Table Functions section of postgresql documentation, but couldn't find an actual usage of it until I finally found this post.
Something this useful should be more prominently displayed, in my opinion.

@lukaseder
Copy link
Member Author

Thanks for your comment, @blmille1.

Something this useful should be more prominently displayed, in my opinion.

SQL has a ton of very useful features. It is hard to assess, which one is the most useful one. Surely, this doesn't appear to be something that's worth explaining on the jOOQ home page, though. :)

Personally, I'm not convinced this ROWS FROM feature should be used too often. Joining two tables by ordinality seems rather weird, and not very SQL / relational algebra idiomatic, so I'm not sure if we need to document this any more prominently.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
C: Functionality P: Medium T: Enhancement
Projects
None yet
Milestone
Version X: Long-term goals and ideas
Development

No branches or pull requests
2 participants
@lukaseder @blmille1