Hello, I'm a relatively new Calcite user, and am working on configuring Calcite to handle queries that include Postgres system functions <https://www.postgresql.org/docs/current/functions-info.html>. I'm running into three issues and wondering if anyone could help educate me or provide examples:
1. Certain functions work correctly "out of the box", like current_user and session_user, though I don't understand how they produce their results. When I run SELECT current_user, the ResultSet contains a single row with a value of "sa". This looks like it's expected in some Calcite tests <https://github.com/apache/calcite/blob/master/babel/src/test/resources/sql/redshift.iq#L2112>, but I'd like to modify this value to be something application-specific. Is there some way to override the implementation with my own? - For example, I'd like to be able to provide the logged in user's username. 2. Certain functions appear to be parsed and validated correctly but run into runtime exceptions, like current_schema. Here's the exception <https://gist.github.com/anyweez/7ee86345c663e313e91c7842f3ffb70c> I am getting when I evaluate "SELECT current_schema", but I don't understand why an error is occurring for this function but not others, or how I go about fixing it. I've tried implementing a ScalarFunction named current_schema, but it doesn't change the outcome. 3. Certain functions may be niladic, like current_schema, but parsing fails when the version with parentheses is used even when I'm using Babel compliance level. My understanding is that using Babel conformance <https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java#L280> should allow parentheses to be optional but supported -- any idea how to make "SELECT current_schema()" work like "SELECT current_schema" does? - From the docs <https://calcite.apache.org/docs/reference.html>: "Calls to niladic functions such as CURRENT_DATE do not accept parentheses in standard SQL. Calls with parentheses, such as CURRENT_DATE() are accepted in certain conformance levels." Is there another step beyond setting the conformance level? Here's the core of the Calcite configuration I'm using, note that I'm including the Postgres operator table and using Babel conformance level: public class CustomQueryRunner { // ... certain fields and methods excluded for brevity ... private void configurePlanner() { SqlParser.Config parserConfig = SqlParser.config() .withConformance(SqlConformanceEnum.BABEL) .withCaseSensitive(false); // List of operators that we want to support (organized into 'operator tables') SqlOperatorTable operatorTable = SqlOperatorTables.chain(List.of( SqlStdOperatorTable.instance(), SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable( SqlLibrary.POSTGRESQL ) )); FrameworkConfig fwc = Frameworks.newConfigBuilder() .parserConfig(parserConfig) .operatorTable(operatorTable) .defaultSchema(this.rootSchema.getSubSchema("pg_catalog", false).plus()) .build(); this.planner = Frameworks.getPlanner(fwc); } public SqlResultSet execute(String query) { // Parse the query, identify syntax errors and throw SqlParseException if // any are found. Query is syntactically valid if no exception occurs. SqlNode sqlNode = planner.parse(query); // Semantically validate the query; this ensures that all references are // to entities that exist (schemas, tables, functions, operators, etc). If // not, a ValidationException will be thrown. SqlNode validated = planner.validate(sqlNode); RelRoot relRoot = planner.rel(validated); // Run the on the rootSchema and iterate over results PreparedStatement run = RelRunners.run(relRoot.project()); ResultSet resultSet = run.executeQuery(); // ... method continues ... } } Thanks in advance! I'm loving Calcite - thank you all for your efforts.
