Table Function with cursor

[Jiunn Jye Ng <ji...@gmail.com>]

Hi,
I am trying to create a "view" which read expand the results of a query
into multiple row.
for example,
Source Table
| name | pages |
| aa |  0-3 |

Output View
| name | pages |
| aa |  0 |
| aa |  1 |
| aa |  2 |
| aa |  3 |

I looked at TableFunction examples. The processCursor in JdbcTest is quite
close to what I need. A cursor which return me the raw data and I can
expand the data.

However, testTableFunctionCursorInputs is not working (currently ignored).
Error (when enabled) is No match found for function signature
process(<NUMERIC>, <CURSOR>) even when the function is created correctly.
it seems it is not able to support CURSOR parameter type.

If cursor is not working, is there any other way that I can get the
Connection within the TableFunction to retrieve the raw data from table ?

Thank you.

Rgds,
jay

Re: Table Function with cursor

[Julian Hyde <ju...@gmail.com>]

Would TableMacroFunction work for your purposes? It would expand the input to an input in the RelNode tree, then you could optimize it as usual.

I mention it because you use the word “view”. Traditional SQL views are implemented as table macros with 0 arguments, and parameterized views would a generalization to allow those views to have parameters that are either constants or queries.

Julian

 

On Feb 17, 2015, at 8:10 PM, Jiunn Jye Ng <ji...@gmail.com> wrote:

> Hi,
> I am trying to create a "view" which read expand the results of a query
> into multiple row.
> for example,
> Source Table
> | name | pages |
> | aa |  0-3 |
> 
> Output View
> | name | pages |
> | aa |  0 |
> | aa |  1 |
> | aa |  2 |
> | aa |  3 |
> 
> I looked at TableFunction examples. The processCursor in JdbcTest is quite
> close to what I need. A cursor which return me the raw data and I can
> expand the data.
> 
> However, testTableFunctionCursorInputs is not working (currently ignored).
> Error (when enabled) is No match found for function signature
> process(<NUMERIC>, <CURSOR>) even when the function is created correctly.
> it seems it is not able to support CURSOR parameter type.
> 
> If cursor is not working, is there any other way that I can get the
> Connection within the TableFunction to retrieve the raw data from table ?
> 
> Thank you.
> 
> Rgds,
> jay