CREATE FUNCTION syntax

[rongrong]

SQL Spec 2011 Section 11.59 defines <SQL-invoked routine> , which is a much more powerful tool. We propose to use part of the syntax as the base for SQL function support.

Syntax

<schema function> ::= CREATE <SQL-invoked function>

<SQL-invoked function> ::= <function specification> <routine body>

<function specification> ::= FUNCTION <schema qualified routine name> <SQL parameter declaration list>
<returns clause> <routine characteristics>

<SQL parameter declaration list> ::= <left paren>
[ <SQL parameter declaration> [ { <comma> <SQL parameter declaration> }... ] ] <right paren>

<SQL parameter declaration> ::= [ <SQL parameter name> ] <parameter type>

<schema qualified routine name> ::= <schema qualified name>

<schema qualified name> ::= [ <schema name> <period> ] <qualified identifier>

<SQL parameter name> ::= <identifier>

<parameter type> ::= <predefined type> | <row type> | <collection type>

<parameter default> ::= <value expression>

<returns clause> ::= RETURNS <returns data type>

<routine characteristics> ::= [ <routine characteristic>... ]

<routine characteristic> ::=
<language clause>
| <deterministic characteristic>
| <null-call clause>

<language clause> ::= LANGUAGE <language name>

<language name> ::= SQL

<deterministic characteristic> ::= DETERMINISTIC | NOT DETERMINISTIC

<null-call clause> ::= RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

<routine body> ::= RETURN <expression>

Example

CREATE OR REPLACE FUNCTION tan (input double)
RETURNS double
DETERMINISTIC
-- LANGUAGE SQL (this is optional as it is the default)
-- RETURNS NULL ON NULL INPUT (this is optional as it is the default)
RETURN sin(input) / cos(input)

Reference

• PostgreSQL's CREATE FUNCTION syntax
• DB2 Scalar function syntax
• Oracle's CREATE FUNCTION syntax
• Google UDF syntax

[caithagoras]

Here is the redacted version of SQL-invoked regular function we're going to support in the first iteration.

<schema function> ::=
  CREATE <SQL-invoked function>

<SQL-invoked function> ::=
  <function specification> <routine body>

<function specification> ::=
  FUNCTION <schema qualified routine name> <SQL parameter declaration list>
    <returns clause>
    <routine characteristics>

##### SQL parameter declaration list #####

<SQL parameter declaration list> ::=
  <left paren> [ <SQL parameter declaration>
    [ { <comma> <SQL parameter declaration> }... ] ] <right paren>

<SQL parameter declaration> ::=
    [ <SQL parameter name> ]
    <parameter type>

<parameter type> ::= 
    <data type>

##### return clause #####

<returns clause> ::=
  RETURNS <returns type>

<returns type> ::=
    <returns data type>

##### routine characteristics #####

<routine characteristics> ::=
  [ <routine characteristic>... ]

<routine characteristic> ::=
    <language clause>
  | <deterministic characteristic>
  | <SQL-data access indication>
  | <null-call clause>

<language clause> ::=
  LANGUAGE <language name>

<language name> ::=
  SQL

<deterministic characteristic> ::=
    DETERMINISTIC
  | NOT DETERMINISTIC

<SQL-data access indication> ::=
    CONTAINS SQL

<null-call clause> ::=
    RETURNS NULL ON NULL INPUT
  | CALLED ON NULL INPUT

##### routine body #####

<routine body> ::=
    <SQL routine spec>

<SQL routine spec> ::=
  <SQL routine body>

<SQL routine body> ::=
  <SQL procedure statement>

[rongrong]

A general question: This new version has more components that SQL expression function requires. Do we have a good enough understanding about them to make sure we would be able to implement them properly? Otherwise I'd suggest to strip it down to the minimum we need to implement SLQ function so there's no "dead code" involved.

Some more specific questions:
What is<contextually typed value specification>, <locator indication>, <generic table parameter type>, <descriptor parameter type>? What are you planning to do for <parameter style>, and <SQL-data access indication>?