User Defined Functions in Informatica

Informatica Powercenter Transformation Language  provides a long list of functions which one can use in Designer and workflow Manager.However Powercenter allows us to define functions of our own which are referred  as  User Defined Functions.

User-defined functions extend the PowerCenter transformation language. You can create and manage user-defined functions with the PowerCenter transformation language in the Designer. You can add them to expressions in the Designer or Workflow Manger to reuse expression logic and build complex expressions. User-defined functions are available to other users in a repository.

User Defined Functions are 2 types.

• Public UDF: Public UDFs can be used by all the users in the repository. Public UDFs can be used in transformations, Workflows, Link conditions and in Command task.
• Private UDF: Private UDF can only be used inside a public UDF.

Creating User Defined Function: 
User defined functions are used to create complex functions by using the existing built-in functions provided by the informatica etl tool. Follow the steps below to create UDF of your own.

• After logging to the designer and opening the folder right click on User Defined Functions folder in the Navigator and click on New. one pop up window will open as shown below
• Now enter the UDF name in the Name field. Select the UDF type as public or private. Add the required number of arguments(Name, datatype, prcesision and scale).
• Click on the Launch Editor button. This opens an expression editor window. Here define the functionality of the UDF by using the existing built-in functions, User defined function and Arguments.
• Click on Ok and save the UDF.

Below Image shows creating UDE named RemoveAdditionalSpaces using expression 

REG_REPLACE(LTRIM(RTRIM(INPUT)),'\S+',' ')

Which removes multiple spaces between the characters and extra spaces before and after the string.

After creating a UDF , You can edit or delete it in the future by right clicking on it iin the Navigator.
Now you  can reference this function directly wherever you can by prefixing :UDF. before ude name.
like,
:UDF.RemoveAdditionlSpaces(Portname)

Important:

• Public UDFs cannot be changed to private UDFs. Where as private UDFs can be changed to Public UDFs.
• You cannot nest the a UDF within the same UDF.

Hope this helps.