Db2 Sql Function With Dynamic Return Value

I have the following working DB2 SQL function CREATE OR REPLACE FUNCTION selector (param VARCHAR(3)) RETURNS TABLE (id INT, CMD VARCHAR(1024), ATTR CHAR(10)) LANGUAGE SQL DETERMI

Solution 1:

You can consider SQL a statically typed language in that it has little ability to discover its variable (e.g. column) and object (e.g. result set) data types at run time; you have to declare types at the statement compilation time. In other words, what you want to achieve is not possible.

There is a concept of a generic table function which allows you to define a Java-based UDF that returns some result set:

CREATEFUNCTION selector (param VARCHAR(3))
RETURNS GENERIC TABLEEXTERNAL NAME...

However, you still need to declare the result set structure on the receiving end:

SELECT t.*FROMTABLE (selector('c')) AS t (foo INT, bar INT, baz VARCHAR(10)...)