Skip to Content

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.

To report this post you need to login first.

23 Comments

You must be Logged on to comment or reply to a post.

  1. 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

    (0) 
    1. Rich Heilman 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

      (0) 
  2. 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

    (0) 
    1. Rich Heilman 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

      (0) 
  3. 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

    (0) 
    1. 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

      (0) 
      1. 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

        (0) 
  4. Former Member

    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

    (0) 
    1. 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

      (0) 
      1. Former Member

        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

        (0) 
        1. 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

          (0) 
  5. 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

     

    (0) 
    1. 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

      (0) 

Leave a Reply