[
https://issues.apache.org/jira/browse/CALCITE-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jing Zhang updated CALCITE-4864:
--------------------------------
Attachment: (was: SQL_2017_part7_polymorphic_table_functions.zip)
> Supports Polymorphic Table function
> -----------------------------------
>
> Key: CALCITE-4864
> URL: https://issues.apache.org/jira/browse/CALCITE-4864
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Reporter: Jing Zhang
> Priority: Major
>
> A Polymorphic Table Function (abbreviated PTF) is a function that returns a
> table whose row type is not declared when the function is created. It is
> introduced in SQL 2017 standard.
> PTF is the evolution of the table function. It has the following features
> compare with normal table function:
> # may have generic table parameters (i.e., no row type of table parameters
> declared when the PTF is created)
> # returns a table whose row type is not declared when the function is
> created, it may depend on the function arguments.
> There are many interesting examples of PTF in SQL standard. Here I only give
> one case.
> We often need to read a CSV file, generally, the first line of the file
> contains a list of column names, and subsequent lines of the file contain
> data. The data in general can be treated as a large VARCHAR. However, some of
> the fields may be numeric or datetime.
> A PTF named CSVreader is designed to read a file of comma-separated values
> and interpret this file as a table.
> CSVreader function has three parameters:
> # The first parameter, File, is the name of a file on the query author's
> system. This file must contain the comma-separated values that are to be
> converted to a table. The first line of the file contains the names of the
> resulting columns. Succeeding lines contain the data. Each line after the
> first will result in one row of output, with column names as determined by
> the first line of the input.
> # Floats is a PTF descriptor area, which should provide a list of the column
> names that are to be interpreted numerically. These columns will be output
> with the data type FLOAT.
> # Dates is a PTF descriptor area, which provides a list of the column names
> that are to be interpreted as datetimes. These columns will be output with
> the data type DATE.
> How to use this PTF in query?
> For example, suppose that the contents of abc.csv are
> {code:java}
> docno,name,due_date,principle,interest
> 123,Mary,01/01/2014,234.56,345.67
> 234,Edgar,01/01/2014,654.32,543.21 {code}
>
> the query author may write a query such as the following:
> {code:java}
> SELECT *
> FROM TABLE (
> CSVreader ( File => 'abc.csv',
> Floats => DESCRIPTOR ("principle", "interest")
> Dates => DESCRIPTOR ("due_date") ) ) AS S {code}
>
> The result will be
> ||docno||name||due_date||principle||interest||
> |123|Mary|01/01/2014|234.56|345.67|
> |234|Edgar|01/01/2014|654.32|543.21|
> [^SQL_2017_part7_polymorphic_table_functions.zip]
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
