User Defined Functions

Create Function: compile, link and add a new function to a database a function.Create Function privilege- enables user to Create a function.This privilege is not granted automatically and must be granted explicitly.


Syntax: GRANT CREATE FUNCTION ON Developer1 TO Developer1; Allows ‘Developer1’ to create functions in their own database.

Drop Function: removes the function by not linking from its shared library.
Drop Function privilege - enables user to DROP or REPLACE a previously created function.Granted automatically WITH GRANT OPTION to the creator of a database or function.Granted automatically to a database or user when it is created.


Syntax: GRANT DROP ON SPECIFIC FUNCTION Parse_Text TO develop1.
Allows ‘develop1’ to drop or replace the Parse_Text function..


Alter Function - performs either or both of the following:
 - Controls whether an existing function can run in protected mode as a separate process or in non-protected mode as part of the database.
  -  Re-compiles or relinks the function and redistributes it.
ALTER FUNCTION privilege - enables user to ALTER a function.This privilege is not granted automatically and must be explicitly granted.Alter Function privilege should be held only by the DBA.


Syntax: ALTER FUNCTION Parse_Text EXECUTE NOT PROTECTED;
Change the function from protected to non-protected mode.
ALTER FUNCTION Parse_Text COMPILE; Recompile the function without changing the protection mode.


Execute Function privilege: Enables user to evoke a function in an SQL statement. It enables a user to execute either specific functions, or all functions in a specified database. There is no EXECUTE FUNCTION command.
This privilege is not granted automatically to the creator of a database or user and must be explicitly granted. A user can execute any function they create, in protected mode.
GRANT EXECUTE FUNCTION On SYSLIB TO Usera;
Allows “Usera” to execute all function in database SYSLIB.
GRANT EXECUTE FUNCTION ON SPECIFIC FUNCTION Develop.Parse_Text TO Userc;
Allows “Userc” to execute the one function called ‘Parse_Text’ located in database “Develop”


Rename Function: This function permits the renaming either of a function name or a specific function name.
Syntax: RENAME SPECIFIC FUNCTION Match_Text TO Scan_Text;
RENAME FUNCTION Imagine_numbers(FLOAT, FLOAT) TO Imaginary_numbers;
COMMENT ON
COMMENT [ON] [ FUNCTION ] <object_name> [AS] ‘<comment>’
To create comments on a function use FUNCTION keyword and specify function name.
COMMENT ON FUNCTION Parse_Text ‘Scans for text using a pattern’;
To comment on function parameter then use the COLUMN keyword option.
COMMENT ON COLUMN Parse_Text.source_text AS ‘The text to be scanned’;
Limitations of User Defined Functions:
 - UDFs cannot execute any SQL.
 - UDFs cannot execute any Standard I/O functions.
 - UDFs cannot be used to calculate the value of a Primary Index column for an INSERT statement.
 - UDFs cannot be used to calculate the value of a Primary Index column in a Where clause to Select a Row.

LOB in Teradata

It is possible to create functions which operate on large object data types. The following library functions exists to provide the ability use LOB's as function input or output:

• FNC_GetLobLength - acquire Lob length

• FNC_LobAppend - add to Lob in memory

• FNC_LobClose - close a context to a Lob

• FNC_LobOpen - open a context to a Lob

• FNC_LobRead - read some or all

• FNC_LobRef2Loc - convert a Lob Reference to a Locator 

• FNC_LobLoc2Ref - convert a Locator to a Lob Reference

Example :

The CREATE FUNCTION syntax permits BLOB's or CLOB's to be the data type of an argument or a return value

CREATE FUNCTION JPEG_CROP

(

Image BLOB AS LOCATOR 

Width INTEGER, 

Height INTEGER, 

X_Offset INTEGER, 

Y_Offset INTEGER

) 

RETURNS BLOB AS LOCATOR

LANGUAGE C

NO SQL

EXTERNAL;

Recursive Triggers

A recursive trigger contains a triggered action which causes the triggering action to reoccur. For example, assume a row trigger is defined with a triggering action that inserts a row into a table. If the triggered action also inserts into the same table, it will cause the trigger to fire again. This will continue until one of two events occur:

• The WHEN clause logic in the trigger definition causes the cycle to end. 

• Sixteen levels of recursion occur. 

The system automatically stops the recursion after sixteen levels to prevent an infinite loop.

Example 1:
CREATE TABLE table1_recur
(col_a INT);
CREATE TRIGGER recur_1
AFTER INSERT ON table1_recur
REFERENCING NEW AS t1
FOR EACH ROW
WHEN (t1.col_a < 10)
INSERT INTO table1_recur (t1.col_a + 1);
INSERT INTO table1_recur( 7 );

SELECT * FROM table1_recur;
      col_a
-----------
          7
          6
          8
         10

DELETE FROM table1_recur;

Now, we will remove the WHEN clause which controls the stopping of the recursion. In theory, this could produce an infinite loop, however the Teradata trigger implementation forces the recursion to rollback with a failure after 16 iterations. 

Example 2:
REPLACE TRIGGER recur_1
AFTER INSERT ON table1_recur
REFERENCING NEW AS t1
FOR EACH ROW
INSERT INTO table1_recur (t1.col_a + 1);
INSERT INTO table1_recur( 7 );
*** Failure 5848 Statement# 1, Info =0 

SELECT * FROM table1_recur;
*** Query completed. No rows found. 

Note that no rows are found in the table because the failure of the transaction has caused all activity to be rolled back. 

Limitations of Partition Primary Index

1. Partition Primary Index(PPI) occupies extra two bytes for storing partition number.
2. Partition Primary index cannot be created on volatile and Global Temporary tables and compressed join index.
3. PPI does not allow NUPI, allows only UPI if PPI is part of primary index. Because in case of NUPI, it would require checking for a duplicate key value in every  partition, which would be very expensive. But in case of UPI,This will result in checking for unique constraint in the same partition.