DB2 SQL Function with dynamic return value

Question

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 
DETERMINISTIC
NO EXTERNAL ACTION 
RETURN
  SELECT id, cmd, attr
      FROM test.commandtbl c
      WHERE c.attr=param;

Calling it like:

select * from table (selector('c'))!      

The problem is that I want the return table to be dynamic in size and type. I want to use the function with a lot of return fields and and while testing I don't want to always check the return table it everything still matches.

For example:

Test1 is with 5 return columns: INT, INT, INT, CHAR(10), VARCHAR(100)

Test2 is with 20 return columns: 10 VARCHAR(100) and 10 INT

and so on.

Is there a way to do that?

Answer 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:

CREATE FUNCTION selector (param VARCHAR(3))
RETURNS GENERIC TABLE
EXTERNAL NAME...

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

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