Author: jhyde
Date: Mon Nov 2 20:47:06 2015
New Revision: 1712151
URL: http://svn.apache.org/viewvc?rev=1712151&view=rev
Log:
In SQL reference, describe how to define a table function and table macro
Modified:
calcite/site/docs/reference.html
Modified: calcite/site/docs/reference.html
URL:
http://svn.apache.org/viewvc/calcite/site/docs/reference.html?rev=1712151&r1=1712150&r2=1712151&view=diff
==============================================================================
--- calcite/site/docs/reference.html (original)
+++ calcite/site/docs/reference.html Mon Nov 2 20:47:06 2015
@@ -811,7 +811,7 @@ form.</p>
<span class="o">|</span> <span class="s1">'('</span> <span
class="n">query</span> <span class="s1">')'</span>
<span class="o">|</span> <span class="k">values</span>
<span class="o">|</span> <span class="k">UNNEST</span> <span
class="s1">'('</span> <span class="n">expression</span> <span
class="s1">')'</span>
- <span class="o">|</span> <span class="s1">'('</span> <span
class="k">TABLE</span> <span class="n">expression</span> <span
class="s1">')'</span>
+ <span class="o">|</span> <span class="k">TABLE</span> <span
class="s1">'('</span> <span class="p">[</span> <span
class="k">SPECIFIC</span> <span class="p">]</span> <span
class="n">functionName</span> <span class="s1">'('</span> <span
class="n">expression</span> <span class="p">[,</span> <span
class="n">expression</span> <span class="p">]</span><span class="o">*</span>
<span class="s1">')'</span> <span class="s1">')'</span>
<span class="k">values</span><span class="p">:</span>
<span class="k">VALUES</span> <span class="n">expression</span> <span
class="p">[,</span> <span class="n">expression</span> <span
class="p">]</span><span class="o">*</span>
@@ -1494,7 +1494,7 @@ the user or application to supply a time
<td style="text-align: left">Returns NULL if the values are the same.<br
/><br />For example, <code>NULLIF(5, 5)</code> returns NULL; <code>NULLIF(5,
0)</code> returns 5.</td>
</tr>
<tr>
- <td style="text-align: left">COALESCE(value, value [, value]* )</td>
+ <td style="text-align: left">COALESCE(value, value [, value ]* )</td>
<td style="text-align: left">Provides a value if the first value is
null.<br /><br />For example, <code>COALESCE(NULL, 5)</code> returns 5.</td>
</tr>
</tbody>
@@ -1536,19 +1536,19 @@ the user or application to supply a time
<td style="text-align: left">Creates a row from a list of values.</td>
</tr>
<tr>
- <td style="text-align: left">map [ key ]</td>
+ <td style="text-align: left">map â[â key â]â</td>
<td style="text-align: left">Returns the element of a map with a
particular key.</td>
</tr>
<tr>
- <td style="text-align: left">array [ index ]</td>
+ <td style="text-align: left">array â[â index â]â</td>
<td style="text-align: left">Returns the element at a particular
location in an array.</td>
</tr>
<tr>
- <td style="text-align: left">ARRAY [ value [, value ]* ]</td>
+ <td style="text-align: left">ARRAY â[â value [, value ]* â]â</td>
<td style="text-align: left">Creates an array from a list of values.</td>
</tr>
<tr>
- <td style="text-align: left">MAP [ key, value [, key, value ]* ]</td>
+ <td style="text-align: left">MAP â[â key, value [, key, value ]*
â]â</td>
<td style="text-align: left">Creates a map from a list of key-value
pairs.</td>
</tr>
</tbody>
@@ -1919,20 +1919,23 @@ varying from convenient to efficient.</p
<p>To implement a <em>scalar function</em>, there are 3 options:</p>
<ul>
- <li>Create a class with a public static <code>eval</code> method. and
register the class;</li>
- <li>Create a class with a public non-static <code>eval</code> method. and a
public
-constructor with no arguments, and register the class;</li>
- <li>Create a class with one or more public static methods, and register
-each class and method.</li>
+ <li>Create a class with a public static <code>eval</code> method,
+and register the class;</li>
+ <li>Create a class with a public non-static <code>eval</code> method,
+and a public constructor with no arguments,
+and register the class;</li>
+ <li>Create a class with one or more public static methods,
+and register each class/method combination.</li>
</ul>
-<p>To implement an <em>aggregate function</em>:</p>
+<p>To implement an <em>aggregate function</em>, there are 2 options:</p>
<ul>
- <li>Create a class with public static <code>init</code>, <code>add</code>
and <code>result</code> methods, and
-register the class;</li>
- <li>Create a class with public non-static <code>init</code>,
<code>add</code> and <code>result</code> methods, and
-a public constructor with no arguments, and register the class.</li>
+ <li>Create a class with public static <code>init</code>, <code>add</code>
and <code>result</code> methods,
+and register the class;</li>
+ <li>Create a class with public non-static <code>init</code>,
<code>add</code> and <code>result</code> methods,
+and a public constructor with no arguments,
+and register the class.</li>
</ul>
<p>Optionally, add a public <code>merge</code> method to the class; this
allows Calcite to
@@ -1943,19 +1946,38 @@ generate code that merges sub-totals.</p
interface; this allows Calcite to decompose the function across several stages
of aggregation, roll up from summary tables, and push it through joins.</p>
-<p>To implement a <em>table function</em>:</p>
+<p>To implement a <em>table function</em>, there are 3 options:</p>
<ul>
<li>Create a class with a static <code>eval</code> method that returns
-<a
href="/apidocs/org/apache/calcite/schema/TranslatableTable.html">TranslatableTable</a>
+<a
href="/apidocs/org/apache/calcite/schema/ScannableTable.html">ScannableTable</a>
or
<a
href="/apidocs/org/apache/calcite/schema/QueryableTable.html">QueryableTable</a>,
and register the class;</li>
<li>Create a class with a non-static <code>eval</code> method that returns
-<a
href="/apidocs/org/apache/calcite/schema/TranslatableTable.html">TranslatableTable</a>
+<a
href="/apidocs/org/apache/calcite/schema/ScannableTable.html">ScannableTable</a>
or
<a
href="/apidocs/org/apache/calcite/schema/QueryableTable.html">QueryableTable</a>,
-and register the class.</li>
+and register the class;</li>
+ <li>Create a class with one or more public static methods that return
+<a
href="/apidocs/org/apache/calcite/schema/ScannableTable.html">ScannableTable</a>
+or
+<a
href="/apidocs/org/apache/calcite/schema/QueryableTable.html">QueryableTable</a>,
+and register each class/method combination.</li>
+</ul>
+
+<p>To implement a <em>table macro</em>, there are 3 options:</p>
+
+<ul>
+ <li>Create a class with a static <code>eval</code> method that returns
+<a
href="/apidocs/org/apache/calcite/schema/TranslatableTable.html">TranslatableTable</a>,
+and register the class;</li>
+ <li>Create a class with a non-static <code>eval</code> method that returns
+<a
href="/apidocs/org/apache/calcite/schema/TranslatableTable.html">TranslatableTable</a>,
+and register the class;</li>
+ <li>Create a class with one or more public static methods that return
+<a
href="/apidocs/org/apache/calcite/schema/TranslatableTable.html">TranslatableTable</a>,
+and register each class/method combination.</li>
</ul>
<p>Calcite deduces the parameter types and result type of a function from the
@@ -1967,8 +1989,12 @@ annotation.</p>
<h3 id="calling-functions-with-named-and-optional-parameters">Calling
functions with named and optional parameters</h3>
<p>Usually when you call a function, you need to specify all of its parameters,
-in order. But if the function has been defined with named and optional
-parameters </p>
+in order. But that can be a problem if a function has a lot of parameters,
+and especially if you want to add more parameters over time.</p>
+
+<p>To solve this problem, the SQL standard allows you to pass parameters by
name,
+and to define parameters which are optional (that is, have a default value
+that is used if they are not specified).</p>
<p>Suppose you have a function <code>f</code>, declared as in the following
pseudo syntax:</p>
@@ -1979,13 +2005,14 @@ parameters </p>
INTEGER d DEFAULT NULL,
INTEGER e DEFAULT NULL) RETURNS INTEGER</code></p>
-<p>Note that all parameters have names, and parameters <code>b</code>,
<code>d</code> and <code>e</code>
+<p>All of the functionâs parameters have names, and parameters
<code>b</code>, <code>d</code> and <code>e</code>
have a default value of <code>NULL</code> and are therefore optional.
(In Calcite, <code>NULL</code> is the only allowable default value for
optional parameters;
this may change
<a href="https://issues.apache.org/jira/browse/CALCITE-947";>in future</a>.)</p>
-<p>You can omit optional arguments at the end of the list, or use the
<code>DEFAULT</code>
+<p>When calling a function with optional parameters,
+you can omit optional arguments at the end of the list, or use the
<code>DEFAULT</code>
keyword for any optional arguments.
Here are some examples:</p>
