Execute a UDR

After you register a UDR as an external routine in the database, it can be called in one of the following ways:

• In a client application or SPL routine, through SQL statements:
  • In the select list of a SELECT statement
  • In the WHERE clause of a SELECT, UPDATE, or DELETE statement
  • In the VALUES clause of an INSERT statement
  • In the SET clause of an UPDATE statement
  • With the EXECUTE PROCEDURE or EXECUTE FUNCTION statement
• In a C UDR, as an SQL statement that one of the following DataBlade® API statement-execution functions sends to the database server:
  • mi_exec()
  • mi_exec_prepared_statement()
  • mi_open_prepared_statement()

  For more information about how to use statement-execution functions, see Execute SQL statements.

• Through an implicit UDR call
  An implicit UDR is a UDR that the database server calls automatically in response to some SQL task. For example, in the following SELECT statement, the database server calls the a_to_int() cast function when it executes the SELECT statement:

  CREATE IMPLICIT CAST (a AS INTEGER WITH a_to_int);
  ...
  SELECT a:int FROM tab1 WHERE b > 6;

• Through the Fastpath interface

  The Fastpath interface of the DataBlade API allows you to call a UDR directly from within another UDR. For more information, see Call UDRs with the Fastpath interface.

Each occurrence of a UDR, implicit or explicit, in an SQL or SPL statement is a routine instance. One routine instance might involve several routine invocations. A routine invocation is one execution of the UDR. For example, if the following query selects five matching rows, the query has one routine instance of the a_func() user-defined function and five routine invocations for this function:

SELECT a_func(x) FROM table1 WHERE y > 7;

To execute a UDR instance in an SQL statement, the database server takes the following steps:

1. The query parser breaks the SQL statement into its syntactic parts and performs any routine resolution required.

   The query optimizer develops a query plan, which efficiently organizes the execution of the SQL-statement parts.

2. The query executer calls the routine manager, which handles execution of the UDR instance and any invocations.