CREATE PROCEDURE Statement (Table UDF)

Creates an interface to an external table user-defined function (table UDF). Users must be specifically licensed to use table UDFs.

For CREATE PROCEDURE reference information for external procedures, see CREATE PROCEDURE Statement (External Procedures). For CREATE PROCEDURE reference information for Java UDFs, see CREATE PROCEDURE Statement (Java UDF)

Quick Links:

Go to Parameters

Go to Usage

Go to Standards

Go to Permissions

Syntax

CREATE[ OR REPLACE ] PROCEDURE 
[ owner.]procedure-name ( [ parameter[, …]] )
| RESULT  result-column [, …] )
[ SQL SECURITY { INVOKER | DEFINER } ] 
EXTERNAL NAME ‘external-call’ 
    
parameter - (back to Syntax)
   [ IN ] parameter-name data-type [ DEFAULT expression ] 
   | [ IN  ] parameter-name table-type

table-type - (back to parameter)
   TABLE( column-name data-type [, ...] ) 

external-call - (back to Syntax)
   [column-name:]function-name@library; ...

Parameters

(back to top)

IN – the parameter is an object that provides a value for a scalar parameter or a set of values for a TABLE parameter to the UDF.
Note: TABLE parameters cannot be declared as INOUT or OUT. You can only have one TABLE parameter (the position of which is not important).

OR REPLACE – specifying OR REPLACE (CREATE OR REPLACE PROCEDURE) creates a new procedure, or replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing permissions. An error is returned if you attempt to replace a procedure that is already in use.

RESULT – declares the column names and their data types for the result set of the external UDF. The data types of the columns must be a valid SQL data type (e.g., a column in the result set cannot have TABLE as data type). The set of datums in the result implies the TABLE. External UDFs can only have one result set of type TABLE.
Note: TABLE is not an output value. A table UDF cannot have LONG VARBINARY or LONG VARCHAR data types in its result set, but a table parameterized function (TPF) can have large object (LOB) data in its result set.
A TPF cannot produce LOB data, but can have columns in the result set as LOB data types. However, the only way to get LOB data in the output is to pass a column from an input table to the output table. The describe attribute EXTFNAPIV4_DESCRIBE_COL_VALUES_SUBSET_OF_INPUT allows this, as illustrated in the sample file tpf_blob.cxx.

SQL SECURITY – defines whether the procedure is executed as the INVOKER (the user who is calling the UDF), or as the DEFINER (the user who owns the UDF). The default is DEFINER.
When SQL SECURITY INVOKER is specified, more memory is used because annotation must be done for each user that calls the procedure. Also, when SQL SECURITY INVOKER is specified, name resolution is done as the invoker as well. Therefore, care should be taken to qualify all object names (tables, procedures, and so on) with their appropriate owner. For example, suppose user1 creates this procedure:
```
CREATE PROCEDURE user1.myProcedure()
	RESULT( columnA INT )
	SQL SECURITY INVOKER
	BEGIN
		SELECT columnA FROM table1;
	END;
```
If user2 attempts to run this procedure and a table user2.table1 does not exist, a table lookup error results. Additionally, if a user2.table1 does exist, that table is used instead of the intended user1.table1. To prevent this situation, qualify the table reference in the statement (user1.table1, instead of just table1).

EXTERNAL NAME – An external UDF must have EXTERNAL NAME clause which defines an interface to a function written in a programming language such as C. The function is loaded by the database server into its address space.
The library name can include the file extension, which is typically .dll on Windows and .so on UNIX. In the absence of the extension, the software appends the platform-specific default file extension for libraries. This is a formal example.
```
CREATE PROCEDURE mystring( IN instr CHAR(255),
	IN input_table TABLE(A INT) )
	RESULT (CHAR(255))
EXTERNAL NAME
'mystring@mylib.dll;Unix:mystring@mylib.so'
```
A simpler way to write the preceding EXTERNAL NAME clause, using platform-specific defaults, is as follows:
```
CREATE PROCEDURE mystring( IN instr CHAR(255),
	IN input_table TABLE(A INT) ) 
	RESULT (CHAR(255))
EXTERNAL NAME ‘mystring@mylib’
```

Usage

(back to top)

You define table UDFs using the a_v4_extfn API. CREATE PROCEDURE statement reference information for external procedures that do not use the a_v3_extfn or a_v4_extfn APIs is located in a separate topic. CREATE PROCEDURE statement reference information for Java UDFs is located in a separate topic.

The CREATE PROCEDURE statement creates a procedure in the database. To create a procedure for themselves, a user must have the CREATE PROCEDURE system privilege. To create a procedure for others, a user must specify the owner of the procedure and must have either the CREATE ANY PROCEDURE or CREATE ANY OBJECT system privilege. If the procedure contains an external reference, the user must have the CREATE EXTERNAL REFERENCE system privilege in addition to previously mentioned system privileges, regardless of who owns the procedure.

If a stored procedure returns a result set, it cannot also set output parameters or return a return value.

When referencing a temporary table from multiple procedures, a potential issue can arise if the temporary table definitions are inconsistent and statements referencing the table are cached. Use caution when referencing temporary tables within procedures.

You can use the CREATE PROCEDURE statement to create external table UDFs implemented in a different programming language than SQL. However, be aware of the table UDF restrictions before creating external UDFs.

The data type for a scalar parameter, a result column, and a column of a TABLE parameter must be a valid SQL data type.

Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid SQL data type.

TPFs support a mix scalar parameters and single TABLE parameter. A TABLE parameter must define a schema for an input set of rows to be processed by the UDF. The definition of a TABLE parameter includes column names and column data types.

TABLE(c1 INT, c2 CHAR(20))

The above example defines a schema with the two columns c1 and c2 of types INT and CHAR(20). Each row processed by the UDF must be a tuple with two (2) values. TABLE parameters, unlike scalar parameters cannot be assigned a default value.

Standards

(back to top)

SQL—ISO/ANSI SQL compliant.
SAP Sybase Database product—The Transact-SQL CREATE PROCEDURE statement is different.
SQLJ—The syntax extensions for Java result sets are as specified in the proposed SQLJ1 standard.

Permissions

(back to top)

Unless creating a temporary procedure, a user must have the CREATE PROCEDURE system privilege to create a UDF for themselves. To create a UDF for others, they must specify the owner of the procedure and must have either the CREATE ANY PROCEDURE or CREATE ANY OBJECT system privilege. If the procedure contains an external reference, a user must also have the CREATE EXTERNAL REFERENCE system privilege, in addition to the previously mentioned system privileges.