Cursor inside SQL query

Question

In Oracle, you can return the cursor inside an SQL query using the cursor keyword, for example:

 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 

Questions:

• Does anyone know where I can find documentation for this?
• Is there a similar PortgreSQL function (or any other open source DBMS)?

Answer 1

It is called CURSOR EXPRESSION, and it is documented in an obvious place, Oracle SQL Reference. Find it here .

As for your second question, the nearest thing PostgreSQL suggests is matching this function with "scalar subqueries." However, as @tbrugz notes, they only return one row and one column, so they are not very similar to cursor expressions. Read about them in the documentation here . MySQL also has scalar subqueries, again limited to one column and one row. The docs are here . Similar to SQL Server and DB2 (not open source, but complete).

This excludes all obvious rivals. Thus, it seems unlikely that any other DBMS offers a jagged result set that we get from the Oracle cursor expression.

Answer 2

Postgres provides cursor expressions, but the syntax is a little less convenient than Oracle.

First you need to create a function to convert to refcursor:

 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 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 request it like this:

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

And process on the 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 specify a string for a specific type. You can use CREATE TYPE to create the necessary types.