CREATE PROCEDURE Statement (Table UDF)

(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’