Table User Defined Functions( Table UDF ) in HANA

With the release of HANA 1.0 SPS5, not only do we get a new SQLScript editor and debugger, but there are also a few new SQLScript language features introduced with this release as well. Today, I would like to introduce Table UDFs.  Table UDFs are read-only user-defined functions which accept multiple input parameters and return exactly one results table.  SQLScript is the only language which is supported for table UDFs. Since these functions are ready-only, only read-only statements can be used within the function.  So you may not use statements like INSERT, UPDATE or DELETE. Also, any procedure calls within the function must also be read-only.  Currently, you can only create these functions in the catalog via the SQL Editor.  The syntax for creating a new table UDF is pretty straight forward.  For those of you who have been working with SQLScript since the beginning, you may notice the resurrection of the CREATE FUNCTION statement. This statement was used to create SQLScript procedures in earlier versions of HANA.  Of course, it has since been replaced with CREATE PROCEDURE. The CREATE FUNCTION statement will now be used to create UDFs in HANA.  In the following example, I’m creating the table UDF called GET_BP_ADDRESSES_BY_ROLE which accepts one input parameter called “partnerrole”, and returns a table with the structure defined explicitly.  You can also use global types to define the output structure as well. This function will execute one SELECT statement which contains an INNER JOIN of two tables and returns the result set to the output parameter.

CREATE  FUNCTION"SAP_HANA_EPM_DEMO".get_bp_addresses_by_role( partnerrole nvarchar(3))
   RETURNS table ( "PartnerId" NVARCHAR(10), "PartnerRole" NVARCHAR(3), "EmailAddress" NVARCHAR(255),
                                      "CompanyName" NVARCHAR(80), "AddressId" NVARCHAR(10), "City" NVARCHAR(40),
                                      "PostalCode" NVARCHAR(10), "Street" NVARCHAR(60) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
   RETURN select a."PartnerId", a."PartnerRole", a."EmailAddress", a."CompanyName",
                          a."AddressId", b."City", b."PostalCode", b."Street"
                              from "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::businessPartner" as a
                                 inner join "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::addresses" as b
                                        on a."AddressId" = b."AddressId"
                                            where a."PartnerRole" = :partnerrole;
END;

Once the above CREATE FUNCTION statement is executed via the SQL Editor, the new function will then show up in the catalog under the “Functions” folder.

The function can now be used in the FROM clause of your SELECT statements.   You can pass the input parameters as well.

select * from"SAP_HANA_EPM_DEMO".get_bp_addresses_by_role('02');

Additional features to UDFs are planned for future support packages, including scalar UDFs, as well as the ability to create UDFs via the HANA Development Workbench directly in your XS projects. Very similar to how we can now create procedures in HANA 1.0 SP5.