0001 ---
0002 layout: global
0003 title: Table-valued Functions (TVF)
0004 displayTitle: Table-valued Functions (TVF)
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 A table-valued function (TVF) is a function that returns a relation or a set of rows.
0025
0026 ### Syntax
0027
0028 ```sql
0029 function_name ( expression [ , ... ] ) [ table_alias ]
0030 ```
0031
0032 ### Parameters
0033
0034 * **expression**
0035
0036 Specifies a combination of one or more values, operators and SQL functions that results in a value.
0037
0038 * **table_alias**
0039
0040 Specifies a temporary name with an optional column name list.
0041
0042 **Syntax:** `[ AS ] table_name [ ( column_name [ , ... ] ) ]`
0043
0044 ### Supported Table-valued Functions
0045
0046 |Function|Argument Type(s)|Description|
0047 |--------|----------------|-----------|
0048 |**range** ( *end* )|Long|Creates a table with a single *LongType* column named *id*, <br/> containing rows in a range from 0 to *end* (exclusive) with step value 1.|
0049 |**range** ( *start, end* )|Long, Long|Creates a table with a single *LongType* column named *id*, <br/> containing rows in a range from *start* to *end* (exclusive) with step value 1.|
0050 |**range** ( *start, end, step* )|Long, Long, Long|Creates a table with a single *LongType* column named *id*, <br/> containing rows in a range from *start* to *end* (exclusive) with *step* value.|
0051 |**range** ( *start, end, step, numPartitions* )|Long, Long, Long, Int|Creates a table with a single *LongType* column named *id*, <br/> containing rows in a range from *start* to *end* (exclusive) with *step* value, with partition number *numPartitions* specified.|
0052
0053 ### Examples
0054
0055 ```sql
0056 -- range call with end
0057 SELECT * FROM range(6 + cos(3));
0058 +---+
0059 | id|
0060 +---+
0061 | 0|
0062 | 1|
0063 | 2|
0064 | 3|
0065 | 4|
0066 +---+
0067
0068 -- range call with start and end
0069 SELECT * FROM range(5, 10);
0070 +---+
0071 | id|
0072 +---+
0073 | 5|
0074 | 6|
0075 | 7|
0076 | 8|
0077 | 9|
0078 +---+
0079
0080 -- range call with numPartitions
0081 SELECT * FROM range(0, 10, 2, 200);
0082 +---+
0083 | id|
0084 +---+
0085 | 0|
0086 | 2|
0087 | 4|
0088 | 6|
0089 | 8|
0090 +---+
0091
0092 -- range call with a table alias
0093 SELECT * FROM range(5, 8) AS test;
0094 +---+
0095 | id|
0096 +---+
0097 | 5|
0098 | 6|
0099 | 7|
0100 +---+
0101 ```
0102
0103 ### Related Statements
0104
0105 * [SELECT](sql-ref-syntax-qry-select.html)