CODEWITHSUNDEEP
hana
Lukas Eder
Lukas Eder

Reputation: 221265

Is it possible to look up a table-valued function's return columns in SAP HANA's dictionary views?

I've created a table-valued function in SAP HANA:

CREATE FUNCTION f_tables
RETURNS TABLE (
    column_value INTEGER
)
LANGUAGE SQLSCRIPT
AS
BEGIN
    RETURN SELECT 1 column_value FROM SYS.DUMMY;
END

Now I'd like to be able to discover the function's table type using the dictionary views. I can run this query here:

select * 
from function_parameters 
where schema_name = '[xxxxxxxxxx]' 
and function_name = 'F_TABLES'
order by function_name, position;

Which will yield something like:

PARAMETER_NAME         TABLE_TYPE_SCHEMA    TABLE_TYPE_NAME
---------------------------------------------------------------------
_SYS_SS2_RETURN_VAR_   [xxxxxxxxxx]         _SYS_SS_TBL_[yyyyyyy]_RET

Unfortunately, I cannot seem to be able to look up that _SYS_SS_TBL_[yyyyyyy]_RET table in SYS.TABLES (and TABLE_COLUMNS), SYS.VIEWS (and VIEW_COLUMNS), SYS.DATA_TYPES, etc. in order to find the definitions of the individual columns.

Note that explicitly named table types created using CREATE TYPE ... do appear in SYS.TABLES...

Is there any way for me to look formally look up a table-valued function's return columns? I'm not interested in parsing the source, obviously.

Upvotes: 3

Views: 1985

Answers (1)

Timo D
Timo D

Reputation: 1803

These kind of tables are internal row-store tables, therefore you can only find your _SYS_SS_TBL_[yyyyyyy]_RET table in SYS.RS_TABLES_. This will give you some basic information, including a column ID (CID). This value is important to find the column information.

For example, if your CID is 100, you can find column information in the RS_COLUMNS_ table with this query:

SELECT * FROM SYS.RS_COLUMNS_ WHERE CID = 100

Upvotes: 1

Related Questions