How to call a SQL procedure with custom CDS Structured Type in an HDI container?

[iashishsingh]

Hello,

For an MTA targeted for SAP Cloud Foundry, we are creating SQL procedures. We would like to use CDS Structured Type Definition to define custom structured types. We can successfully build the hdbcds files to create the structure types but the procedures are not getting built. The error that we are getting is:

(HDB) Error: com.sap.hana.di.procedure: Database error 411: : invalid table type: table type does not exist "MY_SCHEMA_NAME"."opensap.common::interface.guids" [8201003]

Here are the details:

db/src/cfg/interface.hdbcds

namespace opensap.common;

context interface {
    type guid : String(36);
    
    type guids {
        id : guid;
    };
}

db/src/procedures/getInterfaces.hdbprocedure

PROCEDURE "getInterfaces"(
    IN aGuid "opensap.common::interface.guids"
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   READS SQL DATA AS

BEGIN 

   SELECT * 
    FROM interfaces;
   
END

Any guidance would be very much appreciated.

Thanks

[iashishsingh · Accepted solution]

As mentioned in a previously asked question by Thomas Jung, need to specify the type explicitly as a table. Updated hdbcds looks like

namespace opensap.common;

context interface {
    type guid : String(36);
    
    table type guids {
        id : guid;
    };
}

PS - Stumbled on the answer after posting the question 😉