[
https://issues.apache.org/jira/browse/CALCITE-115?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-115.
---------------------------------
Resolution: Duplicate
> User-defined table functions
> ----------------------------
>
> Key: CALCITE-115
> URL: https://issues.apache.org/jira/browse/CALCITE-115
> Project: Calcite
> Issue Type: Improvement
> Reporter: GitHub Import
> Labels: github-import
>
> Add user-defined table functions.
> Table functions are defined using two methods in the Schema SPI:
> getTableFunctionNames() and getTableFunctions(String name). (This request
> does not provide a way to define them in a model.json file.)
> One particular instance of the Schema SPI: ReflectiveSchema should use
> reflection to find candidate methods and expose them as table functions.
> Methods need to be public and return Table (or a sub-class). For example,
> {code}
> public static class HrSchema {
> public final Employee[] emps = {
> new Employee(100, 10, "Bill", 10000, 1000),
> new Employee(200, 20, "Eric", 8000, 500),
> new Employee(150, 10, "Sebastian", 7000, null),
> new Employee(110, 10, "Theodore", 11500, 250),
> };
> public static Table generateStrings(final int count) {
> return new AbstractQueryableTable(IntString.class) {
> public RelDataType getRowType(RelDataTypeFactory typeFactory) {
> return typeFactory.createJavaType(IntString.class);
> }
> public <T> Queryable<T> asQueryable(QueryProvider queryProvider,
> SchemaPlus schema, String tableName) {
> BaseQueryable<IntString> queryable =
> new BaseQueryable<IntString>(null, IntString.class, null) {
> public Enumerator<IntString> enumerator() {
> return new Enumerator<IntString>() {
> static final String z = "abcdefghijklm";
> int i = -1;
> IntString o;
> public IntString current() {
> return o;
> }
> public boolean moveNext() {
> if (i < count - 1) {
> o = new IntString(i, z.substring(0, i % z.length()));
> ++i;
> return true;
> } else {
> return false;
> }
> }
> public void reset() {
> i = -1;
> }
> public void close() {
> }
> };
> }
> };
> //noinspection unchecked
> return (Queryable<T>) queryable;
> }
> };
> }
> }
> public static class IntString {
> public final int n;
> public final String s;
> public IntString(int n, String s) {
> this.n = n;
> this.s = s;
> }
> public String toString() {
> return "{n=" + n + ", s=" + s + "}";
> }
> }
> {code}
> Then the user can write
> {code}
> select * from table(s.GenerateStrings(5))
> {code}
> and receive a data set with columns (n int, s string). The table-function is
> implemented by generating a call to the method to get the `Table`, then
> calling the table's `asQueryable` method, then calling the queryable's
> `enumerator()` method.
> Optiq invokes method that returns a Table at prepare time, with dummy
> argument values (constants if the arguments are constant, null if the
> arguments are nullable, otherwise the default value (0, false, etc.), to
> deduce the names and types of the output columns. And of course it invokes it
> again at run-time with the real values.
> A table function may have parameters that are of type `Table`. For each such
> parameter, the user must supply a query when writing SQL.
> ---------------- Imported from GitHub ----------------
> Url: https://github.com/julianhyde/optiq/issues/115
> Created by: [julianhyde|https://github.com/julianhyde]
> Labels: enhancement,
> Created at: Fri Jan 17 20:39:32 CET 2014
> State: open
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
