allow CREATE FUNCTION with RETURNS TABLE to use SQLPL

Today (in V12 <and maybe earlier>) it is possible CREATE FUNCTION using SQLPL and RETURN a value (of any data type).

This is pretty cool.. and powerful.

This is useful to perform complex SQLPL operations inside the UDF (LANGUAGE SQL).

For example, the UDF can CALL procedures, process result sets, loop, etc . All very handy. But this type of SQLPL UDF can only RETURN one value.

I believe the knowledge center refers to this type of UDF as compiled SQL scalar.

The logical extension or next step of this current and existing functionality is to allow CREATE FUNCTION using SQLPL and RETURNS TABLE.

I believe the knowledge center refers to this type of function today as SQL table.

But the syntax diagram is clear that only SQL statement can be in the SQL-routine-body … and I suppose it must be a SELECT.

No real SQLPL is allowed in this type of UDF that RETURNS a TABLE.

I think allowing CREATE FUNCTION using SQLPL and RETURNS TABLE could be useful for sophisticated application development.

Today I can create the complex SQLPL scalar function to return a single value. And it can do something procedurally complex. But if my procedure wants to return multiple pieces of information then I am stuck with the option of creating multiple UDF to return each piece of info… or I make one big UDF that gathers all the required data and then returns it as one big varchar (or XML) value with all the values concatenated together.

This current workaround is not horrible.. but it is not elegant.

Now that IBM/DB2 can CREATE FUNCTION using SQLPL that returns a single value… it is a logical extension to allow it to RETURNS TABLE. It does not sound like too much work…

To top it off… I am certain (not 100%) that this functionality exists in other RDBS such as DB2LUW