Can we access a table from SQL UDF in TD 14?

Database

Can we access a table from SQL UDF in TD 14?

Hello,

We are trying to create a SQL Udf .It should access the table. For example, like below

CREATE FUNCTION SYSLIB.INCLUDE (RLS CHAR(1))

RETURNS VARCHAR(90)

LANGUAGE SQL

CONTAINS SQL

DETERMINISTIC

COLLATION INVOKER

INLINE TYPE 1

RETURN

 SELECT SECUR_LBL FROM  db.SECUR_LABEL_USER WHERE USER_ID=USER ANd SECUR_TYPE=RLS ;

Is this possible with Td 14 version? Any help would be really helpful.

Thank you.

12 REPLIES
Teradata Employee

Re: Can we access a table from SQL UDF in TD 14?

Are you converting fuctions from other RDBM (e.g SQL Server) to Teradata?

What will be the use of this fuction?

Regards, Adeel

Teradata Employee

Re: Can we access a table from SQL UDF in TD 14?

A SQL UDF is only allowed to perform an expression and return the result. A Select is not allowed in a SQL UDF.

Re: Can we access a table from SQL UDF in TD 14?

accessing objects from within functions has been a useful feature in other DBMS (eg SQL Server)

you are able to build a reusable set of functions for lookups, data validations, etc that can be use in other SPs, querys, etc allowing the developers to join to functions as virtual tables, select only certain values from the output, etc.

While Teradata is superior is many areas, it seems they are about 5-6 years behind other DBMSs when is comes to being developer friendly.

Are there plans to add this capabilty?

N/A

Re: Can we access a table from SQL UDF in TD 14?

So if that is the case, (

A SQL UDF is only allowed to perform an expression and return the result. A Select is not allowed in a SQL UDF.

)

THen what is the correct way to impliment what Mani has asked?  Can you select and return data in a c or Java Function UDF?

Teradata Employee

Re: Can we access a table from SQL UDF in TD 14?

For the correct way o implement that .... again .... What will be the use of this fuction?

Re: Can we access a table from SQL UDF in TD 14?

If we cannot access other table from a udf in terdata, Can we access a properties file from inside a udf so that we can fetch configuration or create a lookup?

Teradata Employee

Re: Can we access a table from SQL UDF in TD 14?

Yes, but in that case you should make sure the UDF is running as PROTECTED mode.  (This is the mode it has when first created or replaced; it only gets to not-protected by an explicit ALTER command.)

Highlighted
Teradata Employee

Re: Can we access a table from SQL UDF in TD 14?

I have been prompted to point out that I/O should only be done by table functions or table operators.  If this is a simple scalar UDF, you should use GLOP instead of an actual file for properties - that is exactly what GLOP was made for.  For details on how to use it, see the SQL External Routine Programingming manual at info.teradata.com

Re: Can we access a table from SQL UDF in TD 14?

Can we use cufconfig utility for passing variables to C++ udf.  I created a properties file and added it to the cufconfig UdfEnvFile variable . However i was not able access the configuration from the properties file.

I looked out for examples , but i didn't got any lead on that. Can you please elaborate it with an example ?