Cursor inside SQL query

Question

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 1

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.

Solution 2

Postgres provides cursor expressions but the syntax is a bit less handy than Oracle's.

First you need to create function for array to refcursor conversion:

create or replace function arr2crs(arr anyarray) returns refcursor as $$
declare crs refcursor;
begin
    open crs for select * from unnest(arr);
    return crs;
end;
$$ language plpgsql volatile;

Now let's create some test data

create table dep as 
select 1 depid, 'Sales' depname
union all
select 2 depid, 'IT' depname;

create table emp as
select 1 empid, 1 depid, 'John' empname union all
select 2 empid, 1 depid, 'James' empname union all
select 3 empid, 2 depid, 'Rob';

You can query it like this

select 
    dep.*,
    arr2crs(array(
        select row(emp.*)::emp from emp 
        where emp.depid = dep.depid
    )) emps
from dep

And process in on client side like this (Java)

public static List Rs2List(ResultSet rs) throws SQLException{
    List result = new ArrayList();
    ResultSetMetaData meta = rs.getMetaData();
    while(rs.next()){
        Map row = new HashMap();
        for (int i = 1; i <= meta.getColumnCount(); i++){
            Object o = rs.getObject(i);
            row.put(
                    meta.getColumnName(i),
                    (o instanceof ResultSet)?Rs2List((ResultSet)o):o);
        }
        result.add(row);
    }
    return result;
}

Note that you must explicitly cast row to particular type. You can use CREATE TYPE to create necessary types.

Comments

[tbrugz]

Nice references, thanks. But PostgreSQL "scalar subqueries" aren't the same as "cursor expressions": cursor expressions can return multiple rows and columns, while scalar subqueries can return only one row (or none) and one column