Skip to Content
Author's profile photo Rich Heilman

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.

12-7-2012 12-41-25 PM.png

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.

Assigned Tags

      23 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Rich,

      Since a table UDF is limited to one table output, and it's read-only, it seems like another manifestation of a scripted CalcView.

      I'm wondering if you could clarify what the use case of a function would be over a stored procedure?

      Thanks!

      Jody

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Hi Jody.  The main purpose for table functions is that they can be embedded into SQL statements, such as SELECT statements, and are reusable. You can not embed a procedure call in this way.

      Cheers,

      Rich Heilman

      Author's profile photo Rama Shankar
      Rama Shankar

      Good sample - thanks!

      Author's profile photo Angelo Gervasoni
      Angelo Gervasoni

      Can I create a function that returns an int or varchar?

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Yes, you can create a scalar function to return an integer, varchar is still not yet supported. 

      http://scn.sap.com/community/developer-center/hana/blog/2013/07/01/scalar-user-defined-functions-in-sap-hana

      Cheers,

      Rich Heilman

      Author's profile photo Angelo Gervasoni
      Angelo Gervasoni

      unfortunately I'm use Sp5

      I saw that in the SP06 udf not support varchar and sql statement

      so they are too limited ....

      Author's profile photo Former Member
      Former Member

      Hi Rich

      In the above syntax returns table (    )

      language

      we are declaring variables individually

      returns table ( "CATEGORY" NVARCHAR(40))

      can we give directly the structure name or table name like

      returns table  ( "schemaname"."tablename")

      Language

      Thanks

      Pawan Akella

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      I checked with development and for what ever reason this is not supported. We will work to allow the use of tables/table types in a future revision/support package.   Thanks for bringing this to our attention.

      Cheers,

      Rich Heilman

      Author's profile photo Abhinav Kumar
      Abhinav Kumar

      Hi Rich,

      Is this feature supported now in HANA SP11?

      Thanks,

      Abhinav Kumar

      Author's profile photo Former Member
      Former Member

      Hi Rich,

      In table UDF it is possible to pass a table type as an input parameter. Now when we create such a table UDF how do we pass data to this UDF from the select query where we call the UDF?

      Thanks and Regards,

      Vijay

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Dependin on what you want to do you can do something like this:

      create function getUserLoginTimes ( unames TABLE (user_name NVARCHAR(256) ))

      RETURNS TABLE (USER_NAME NVARCHAR(256), LAST_LOGIN_TIME TIMESTAMP)

      LANGUAGE SQLSCRIPT AS

      BEGIN

          return  select u.user_name, u.last_successful_connect as last_login_time

                  from users u

                  where u.user_name in (select distinct user_name from :unames);

      END;

      create procedure callfnct ()

      language sqlscript

      as

      begin

         un = select user_name from users;

        

         select * from  getUserLoginTimes (:un);

      end;

      Here we pass a table variable for the table type parameter.

      That works in plain SQL as well, but there you have to reference an actual table or view.

      create view myusers as select user_name from users;

      select * from  getUserLoginTimes (myusers);

      works as well, while

      with

          myuser as ( select user_name from users)

      select * from

          getUserLoginTimes (myuser);

      fails.

      - Lars

      Author's profile photo Former Member
      Former Member

      Hi Lars,

      Thank you. I was aware of the procedure call, but was wondering how to do it from the select query. 🙂

      Thanks and Regards,

      Vijay

      Author's profile photo DAMODAR SAI MALLIKA
      DAMODAR SAI MALLIKA

      Hi Rich ,

      Thanks for sharing the information !! 🙂

      i have a question regarding the functions .

      Generally we create functions via catalog  .

      But once a function is created,  the next time when we want to edit, it is not editable .

      can you share the information how to redefine the already created functions ??

      Thanks and Regards,

      Damodar Sai

      Author's profile photo Former Member
      Former Member

      Hi Damodar,

      Generally Catalog objects created via SQL console, will have to be dropped before recreation. Please drop the function using DROP FUNCTION command and you can recreate the function under the same name using the CREATE FUNCTION command. However if you are developing functions using Content based development reactivating the function after change would essentially give you the result you desire.

      Thanks and Regards,

      Vijay

      Author's profile photo DAMODAR SAI MALLIKA
      DAMODAR SAI MALLIKA

      Hi Ravi ,

      Can you tell me what could be the file name ending with , if we create functions via Content.

      Example for procedures , via content  , it is XXX.hdbprocedure

      similary can you specify the file name(ending  for functions ).

      Thanks and Regards,

      Damodar Sai

      Author's profile photo Former Member
      Former Member

      Hi Damodar,

      For Table UDF the file extension would be .hdbtablefunction and for scalar UDF it would be .hdbscalarfunction

      Thanks and Regards,

      Vijay

      Author's profile photo DAMODAR SAI MALLIKA
      DAMODAR SAI MALLIKA

      Hi Ravi ,

      Thanks for giving the information !! 🙂

      It was helpful..

      Thanks and Regards,

      Damodar Sai

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Just to add,  in the context of XSA, the file extension for both scalar and table functions is .hdbfunction

      Cheers,

      Rich

      Author's profile photo Ricardo Soares
      Ricardo Soares

      Hi, thanks for the tutorial.

      Is it possible to debug a table function?

      I have a few table functions with many long selects inside.

      Thanks,

      Ric.

      Author's profile photo Former Member
      Former Member

      Dear Rich, Lars,

      I would like to read the return definition of a function. I find the Return parameter in "PUBLIC":"FUNCTION_PARAMETERS". I find the gerated table in TABLE_TYPE_SCHEMA and TABLE_TYPE_NAME. So far so good. The Table has the pattern "_SYS_SS_TBL_xxx_RET".

      However I do not find information about this table in "PUBLIC"."TABLES" and "PUBLIC"."TABLE_COLUMNS".

      I see in the "SYS"."TABLES" and "SYS"."TABLE_COLUMNS" Views that there is an filter on the table_name

      WHERE NOT EXISTS (SELECT
      *
      FROM SYS.RS_TABLES_ T2
      WHERE T1.OID = T2.OID
      AND T1.VID < T2.VID
      AND T1.SCHEMA = T2.SCHEMA
      AND T1.NAME = T2.NAME)
      AND (T1.NAME NOT LIKE '\_SYS%' ESCAPE '\'
      OR T1.NAME LIKE '\_SYS\_REP\_%' ESCAPE '\'
      OR T1.SCHEMA = '_SYS_SECURITY' )
      AND ( 1 = (SELECT
      HAS_PRIV
      FROM SYS.HAS_NEEDED_SYSTEM_PRIV)
      OR CURRENT_USER = T1.SCHEMA
      OR HASANYPRIVILEGES(CURRENT_USER,
      T1.OID,
      T1.SCHEMA,
      'TABLE') = 1 ) ) A

       

      I marked the condition. Why are these tables filtered out? The only way for me to get to that information would be to go to the underlying tables "SyS"."RS_TABLES_" and "SYS"."RS_COLUMNS_" and let SYS give read rights to it. I do not want this but is there any other possibility to get to the column definition of the gerated return table?

       

      Thanks & Kind Regards,

      KLaus

       

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Hi Klaus,

      the definition of table typed parameters for functions can be found in the public view "FUNCTION_PARAMETER_COLUMNS". (as of SPS12)

      For example

      drop function test_retparam;
      create function test_retparam () 
      returns table ( curtimestamp timestamp
                    , curuser nvarchar(256))
      as
      begin
      
      return
          select current_timestamp as curtimestamp
               , current_user as curuser 
          from dummy;
          
      end;
       
      
      select * from test_retparam();
      
      select * from "PUBLIC"."FUNCTION_PARAMETERS" 
      where function_name ='TEST_RETPARAM';
      /*
      SCHEMA_NAME FUNCTION_NAME   FUNCTION_OID    PARAMETER_NAME  DATA_TYPE_ID    DATA_TYPE_NAME  LENGTH  SCALE   POSITION    TABLE_TYPE_SCHEMA   TABLE_TYPE_NAME IS_INPLACE_TYPE PARAMETER_TYPE  HAS_DEFAULT_VALUE   IS_NULLABLE
      DEVDUDE     TEST_RETPARAM   177033          ?               45              TABLE_TYPE      0       0       0           ?                   ?               TRUE            RETURN          FALSE               TRUE       */
      
      select * from "PUBLIC"."FUNCTION_PARAMETER_COLUMNS" 
      where function_name ='TEST_RETPARAM';
      
      /*
      SCHEMA_NAME FUNCTION_NAME   FUNCTION_OID    PARAMETER_NAME  PARAMETER_POSITION  COLUMN_NAME     POSITION    DATA_TYPE_NAME  LENGTH  SCALE   IS_NULLABLE
      DEVDUDE     TEST_RETPARAM   177033          ?               0                   CURTIMESTAMP    1           TIMESTAMP       27      7       TRUE       
      DEVDUDE     TEST_RETPARAM   177033          ?               0                   CURUSER         2           NVARCHAR        256     ?       TRUE       
      */

      Before SPS12 there is no/I don't know a supported way to get the parameter definition for table typed functions.

      On the question about the filtering of system objects: this is done to hide the internals of the database management and avoid misuse and misinterpretation.

      Hope that helps anyways.

      Cheers

      Lars

      Author's profile photo Former Member
      Former Member

      Thanks a lot!

      Author's profile photo Sk Kamruzzaman
      Sk Kamruzzaman

      Really helpful document..   Thanks a lot!