Can an SQL UDF contain SQL in TD 13.10?

Extensibility

Can an SQL UDF contain SQL in TD 13.10?

I would like to embed a Select statement in a UDF so that I can pop entries from a queue table into a broader query result.  Something like:


     Create FUNCTION Q_Pop1 (TxnType Char(10))


     RETURNS Integer


     LANGUAGE SQL


     CONTAINS SQL


     SPECIFIC Q_Pop1


     CALLED ON NULL INPUT


     SQL SECURITY DEFINER


     COLLATION INVOKER


     INLINE TYPE 1


     RETURN CASE


          when TxnType = 'TypeA' Then Select and Consume top 1 txn_id from QTBL_1

     END;

The use would be something like:

     Sel A.resource, Q_Pop('Deposit') from A where a.avail = 'Y';

From reading manuals and the forums I'm pretty sure that I cannot embed SQL in a UDF.  I thought about burying the SQL inside of some C but if I get a couple thousand rows in the answer set bouncing out to C to connect back to the database to return a row a couple thousand times wouldn't be efficient... 

Any suggestions?

Thanks,

Jim

Tags (1)
1 REPLY

Re: Can an SQL UDF contain SQL in TD 13.10?

So, the answer to my question is "No" - a select statement cannot be embedded in an SQL UDF.  SQL "expressions" can be used, but not Select statements.  This capability exists in other DBMS platforms and hopefully will come to Teradata.  In my case I'll have to solve the problem with a more complex stored procedure.  So much for the easy way out!