How to add a udf who's parameter is a table name

Question

I want to add a udf to calcite. The udf's parameter is a table name, and it will return a varchar value. Are there any sample for this? Thanks.

My test sql is:

SELECT MYTEST22(a), MYTEST(a), MYTEST1(a), COUNT(*)
FROM alisis.table1 
WHERE a=max_dt(alisis.table1) 
GROUP BY MYTEST22(a),MYTEST(a),MYTEST1(a)

max_dt is the function I want to add.

Please [edit] your question to show [the code you have so far](http://whathaveyoutried.com). You should include at least an outline (but preferably a [mcve]) of the code that you are having problems with, then we can try to help with the specific problem. You should also read [ask]. — Toby Speight, Jul 05 '17 at 10:50

inferno · Accepted Answer · 2017-07-05T16:20:30.237

I don't know what your want to do, could you make a clear explanation?

No function can accept table as parameter, the parameters must have certain type, String, Int or others. So constants and columns can be used as parameters. If your meaning is the table name as a parameter, the sql should be as follows:

SELECT MYTEST22(a), MYTEST(a), MYTEST1(a), COUNT(*)
FROM alisis.table1 
WHERE a=max_dt('alisis.table1') 
GROUP BY MYTEST22(a),MYTEST(a),MYTEST1(a)

As I known, all functions Calcite (version is 1.11) supported are defined at SqlStdOperatorTable. If you want to support other functions, you can implement a class that extends SqlStdOperatorTable, for example:

public class TestOperatorTable extends SqlStdOperatorTable {
  private static volatile TestOperatorTable instance;
  public static TestOperatorTable instance() {
    if (instance == null) {
      synchronized(TestOperatorTable.class) {
        if (instance == null) {
          instance = new TestOperatorTable();
          // Use reflection to register the expressions stored in public fields.
          instance.init();
        }
      }
    }
    return instance;
  }

  /** Follow add your functions */
  public static SqlFunction LTRIM = new SqlTrimFunction("LTRIM");
}

SqlTrimFunction as follows:

public class SqlTrimFunction extends SqlFunction {
  public SqlTrimFunction(String funcName) {
    super(
      funcName,
      SqlKind.OTHER_FUNCTION,
      ReturnTypes.VARCHAR_2000,  // return type is String
      InferTypes.FIRST_KNOWN,
      OperandTypes.STRING,       // parameter type is String
      SqlFunctionCategory.STRING
    ); 
  }
}

When you create a SqlValidator, for example SqlValidatorImpl, you should use TestOperatorTable as its first parameter.

Yes, the function I want to support has the table name as a parameter. My test sql has syntax error . I will try to implement the class . Thank you very mach! — fei.chen, Jul 06 '17 at 03:44
@fei.chen https://stackoverflow.com/a/44962144/1314077 this may be helpful to add UDF, do not extend SqlStdOperatorTable. — inferno, Jul 18 '17 at 06:18

How to add a udf who's parameter is a table name

1 Answers1