Search code examples
sqlpostgresqloracle-databaseplsqldatabase-cursor

Cursor inside SQL query

In Oracle, it's possible to return a cursor inside a SQL query, using the cursor keyword, like this:

select owner, table_name,
    cursor (select column_name 
        from all_tab_columns
        where owner = allt.owner 
        and table_name = allt.table_name) as columns
from all_tables allt

The questions are:

  1. Does anyone know where can I find documentation for this?
  2. Does PortgreSQL (or any other open source DBMS) have a similar feature?
Solution

  • It's called a CURSOR EXPRESSION, and it is documented in the obvious place, the Oracle SQL Reference. Find it here.

    As for your second question, the closest thing PostgreSQL offers to match this functionality is "scalar sub-queries". However, as @tbrugz points out, these only return one row and one column, so they aren't much like Cursor Expressions. Read about them in the documentation here. MySQL also has Scalar Sub-queries, again limited to one column and one row. Docs here. Likewise SQL Server and DB2 (not open source but for completeness).

    That rules out all the obvious contenders. So, it seems unlikely any other DBMS offers the jagged result set we get from Oracle's cursor expression.