15.00 - General Limitations and Restrictions on Table Functions - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

General Limitations and Restrictions on Table Functions

These general restrictions apply to table functions:

  • Table functions cannot execute against fallback data when an AMP is down. Once the AMP returns to service, the query can complete as normal.
  • If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single‑byte session character set to create a new UDF, UDT, method, or Java stored procedure object even if the new object name contains only single‑byte characters. Otherwise, Teradata Database aborts the request and returns an error to the requestor. Instead, use a multibyte session character set.
  • You should always run in protected mode if a table function accesses data external to Teradata Database or causes the OS to consume system resources. This includes anything that causes the OS to allocate system context, including open files, pipes, semaphores, tokens, threads (processes), and so on.
  • You can expand the number of protected mode servers for table functions from the default value of 2 to a maximum of 20 per AMP or PE vprocs. For details, see the cufconfig utility in Utilities: Volume 1 (A-K) and SQL External Routine Programming. The minimum is 0.

    Protected mode servers consume disk resources as follows:

  • For functions running in protected mode, you need at least one protected mode server for each table function you want to run concurrently from separate sessions. See the cufconfig utility in Utilities: Volume 1 (A-K) and SQL External Routine Programming. A table function reserves one protected mode UDF server per vproc for the duration of the table function step.
  • A table function can be deadlocked if too few protected mode servers are configured.
  • The system neither detects nor reports such a deadlock.

  • You cannot specify more than one table UDF in a FROM clause.
  • You cannot invoke a table function in a FROM clause join condition using the FROM … JOIN …ON syntax.
  • Suppose you create the following table and table function.

         CREATE TABLE supp_indata (
           a FLOAT,
           b FLOAT;
     
          CREATE FUNCTION c_mytable (a FLOAT, b FLOAT) 
          RETURNS TABLE (c3 FLOAT, c4 FLOAT )  
          LANGUAGE C 
          NO SQL
          PARAMETER STYLE SQL
          EXTERNAL NAME 'CS!c_mytable!c_mytable.c';

    The following SELECT request is not valid because it uses the FROM … JOIN …ON syntax.

         SELECT t1.c4
         FROM TABLE (c_mytable(supp_indata.a, supp_indata.b)) AS t1
         JOIN supp_indata AS t2 ON t1.c3 = t2.b;

    However, the following SELECT request that uses slightly different syntax for the join is valid.

         SELECT t1.c4
         FROM TABLE(c_mytable(supp_indata.a, supp_indata.b)) AS t1,                           supp_indata AS t2
         WHERE t1.c3 = t2b;
  • You cannot create or replace a table function from an embedded SQL application.
  • A table function cannot reference a recursive view.
  • The size of any row returned by a table function cannot exceed the system limit.
  • If a row does exceed this limit, the system aborts the request in Teradata session mode or its containing transaction in ANSI session mode, rolls it back, and returns an error message to the requestor.

    The system defers the maximum row size check until you invoke the function because it cannot know whether the limit will be exceeded by a table function defined with dynamic result rows until the function runs.

  • The maximum number of columns that can be returned by any table function is 2,048.
  • When you run in protected mode, all instances of a table function run as tdatuser.
  • You must troubleshoot code problems outside Teradata Database.
  • Note that you can do a limited amount of code checking by building the trace facility (see “CREATE GLOBAL TEMPORARY TRACE TABLE” on page 317) into the code with the FNC_Trace_Write or FNC_Trace_Write_DL library calls. For details about these library calls and other troubleshooting methods you can build into table functions, see SQL External Routine Programming.

  • You can specify the TD_ANYTYPE data type as an input parameter for a table function.
  • You cannot specify the TD_ANYTYPE data type as the return type for a table function.
  • Any non‑deterministic elements referenced in the RETURN expression of a UDF can be replaced by values predefined by Unity Director. For more information about Unity Director, see Teradata Unity Installation Guide and Teradata Unity User Guide.
  • The mandatory way you must specify columns having a BLOB or CLOB data type depends on whether you are specifying a data type for a parameter column or for a RETURN TABLE clause column.
  •  

    IF you are specifying a LOB data type in this clause …

    THEN you must specify the LOB type …

    parameter

    with an AS LOCATOR phrase.

    RETURNS TABLE

    without an AS LOCATOR phrase.