add_="tvf"

<do action_="sql" add_="tvf"> adds a new table-valued function to a specified schema.

Description

add_="tvf" adds a new table-valued function (TVF) named [FUNCTION] with type signature [SIGNATURE] to a specified schema. A given function may be added to a schema multiple times with distinct signatures.

You can specify the definition of the table-valued function for a given signature with add_="tvf". The definition must be written in MDB macro language. However, you can use <code language_="python">, <code language_="sql">, or <code language_="r"> within macro language to incorporate other languages into the TVF. <code language_="macro"> is the default language and may be omitted.

The macro language for the TVF is specified by including it wrapped in <quote> in the body of <do action_="sql">, or by specifying it as a list of XML values in the src_ argument. The macro language may refer to @params_.[NAME], which contains a literal representation of each argument, or to @sqlvalues_.[NAME], which contains an MDB expression evaluating to the argument. In addition, @sqltypes_.[NAME] contains a string representing the actual SQL type of the passed argument. See System block variables for more information about @params_, @sqlvalues_, and @sqltypes_.

Syntax

<dynamic>
    <do action_="sql" add_="tvf"
     schema_="[SCHEMA]"
     objname_="[FUNCTION]"
     signature_="[SIGNATURE]"
     priority_="[first|last]"
     params_="[PARAMNAMES]"
     names_="[names|labels]"/>
</dynamic>

Attributes for add_="tvf"

schema_
The schema where the table-valued function is to be added.
objname_
The name of the function to be added.
signature_
The arguments of the function, supplied as a list of type names separated by either commas or semicolons, such as "{'text','integer','integer'}".

Possible type names are as follows:

  • integer
  • double
  • bigint
  • boolean
  • text
  • varchar
  • date
  • timestamp
  • time
  • intervalytm (integer months)
  • intervaldts (fractional days)
An empty string in this argument means that the SQL type can be any type. It is also possible to supply a list of type names for each argument. For example, signature_="{{'integer','bigint'},{'integer','bigint'}}" means that the function takes two arguments, and each argument can be either an integer or a bigint.

A given function may be added to the schema multiple times with distinct signatures. By default, signatures are matched sequentially when the function is called, unless you use the optional priority_ argument. If no signature matches the function's arguments, the table-valued function returns an error.

priority_
An optional argument assigning a priority to the signature for the function, in the case where there is more than one table-valued function in a schema with the same name but distinct signatures. Possible values are "first" and "last" ("last" is the default value).
params_
Specifies the parameter names to be referred to in the definition. It must be a list the same length as the signature containing valid variable names.
names_
An optional argument that may be used to specify that the result of the MDB query should be imported into SQL using MDB column names instead of column labels ("labels" is the default value).

Example

The following example shows how to add a simple table-valued function "foo" to a schema. The TVF has two signatures: the first signature accepts one argument, a string, and the second signature accepts three arguments, two strings and a third argument of any data type. Since it has two signatures, the TVF accepts either an MDB table path ("path") or an MDB table path ("path") and a column name ("col") and value ("val") to select in that column. For each signature, the result of the MDB query is imported into SQL using MDB column names. The use of <quote> prevents premature expansion of the macro code. The table-valued function code uses @params_.path and @params_.col because these strings must appear in their raw (unquoted) form in the expanded MDB query. The TVF also uses @sqlvalues_.val because an MDB expression is required here.

<do action_="sql" add_="tvf" schema_="myschema" objname_="foo" signature_="string" 
      params_="path" names_="names">
   <quote> 
     <base table="{@params_.path}"/>
   </quote>
</do>
<do action_="sql" add_="tvf" schema_="myschema" objname_="foo" signature_="string;string;" 
      params_="path;col;val" names_="names">
   <quote>
    <base table="{@params_.path}"/>
    <sel value="{@params_.col}={@sqlvalues_.val}"/>
   </quote>
</do>

The TVF "foo" may then be used in the FROM or JOIN clauses of queries, as follows: 

SELECT * FROM myschema.foo('pub.demo.weather.stations');

SELECT * FROM myschema.foo('pub.demo.weather.stations','state','NY');

SELECT name||', '||state as "City", AVG(temp) as "Avg noon temp"
FROM myschema.foo('pub.demo.weather.stations','tz',-5)
JOIN myschema.foo('pub.demo.weather.hourly90','hour',12) USING (id)
GROUP BY 1;
Note: The schema needs to be reloaded into the session with either the Reload into session button in the SQL Metadata Tool or <do action_="sql" reload_="catalog"/> if you want to test the TVF in the same session.