What you want to accomplish is certainly possible by using a pipelined table function,
but it isn't necessarily a wise thing to do. For any non-trivial result set row-based
processing is always significantly slower than set-based processing. In addition to that,
by hiding your data access logic in a table function you deprive the query optimizer
of any information about the statistics and other physical characteristics of the underlying
tables and indexes. As a result, performance of such a chimera will lie anywhere between
suboptimal and unacceptable.
Consider rethinking your approach; in most cases you should be able to implement your data retrieval and manipulation logic in a single query, may be wrapped in a view for convenience.
If you, however, insist on shooting yourself and those who come after you in your
collective feet, here's how you might go about doing that.
CREATE OR REPLACE PROCEDURE inner
LANGUAGE SQL
DYNAMIC RESULT SETS 1
READS SQL DATA -- (1)
BEGIN
DECLARE c1 CURSOR
WITH RETURN -- (2)
FOR SELECT tabschema, tabname FROM syscat.tables;
OPEN c1;
END
Footnotes:
The READS SQL DATA here is important; a table function cannot have side effects, and so
shouldn't the stored procedure it calls. We enforce this contract at compilation time.
Make sure Db2 knows we want data in the caller, otherwise it will optimize it away.
Then the function itself:
CREATE OR REPLACE FUNCTION smitf()
RETURNS TABLE (
out_schema VARCHAR(128), out_name VARCHAR(128)
)
READS SQL DATA
BEGIN
DECLARE has_rows INT DEFAULT 1;
DECLARE schema_name VARCHAR(128);
DECLARE table_name VARCHAR(128);
DECLARE res1 RESULT_SET_LOCATOR VARYING;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET has_rows = 0;
CALL inner();
ASSOCIATE RESULT SET LOCATORS (res1) WITH PROCEDURE inner;
ALLOCATE cur1 CURSOR FOR RESULT SET res1;
FETCH cur1 INTO schema_name, table_name;
WHILE has_rows > 0 DO -- some bogus processing
IF table_name NOT LIKE 'A%' THEN
PIPE (LOWER(schema_name), LOWER(table_name)); -- magic is here
END IF;
FETCH cur1 INTO schema_name, table_name;
END WHILE;
RETURN;
END
and call it:
SELECT * FROM TABLE (smitf() CARDINALITY 100) WHERE ...
The CARDINALITY clause lets you tell the optimizer how many rows you expect the function to return, but it may not take this information into account, and even if it does, it may not help.
