Recursive UDF

[Szymon Dembek]

Hello

Is it possible to write a recursive (a function which calls itself) UDF
in DB2 (v.9) ?

When I load one, I get SQL0440N - function "XXX" not found...

Thanks in advance

--
Szymon Dembek

[Serge Rielau]

Szymon Dembek wrote:

Hello

Is it possible to write a recursive (a function which calls itself) UDF
in DB2 (v.9) ?

When I load one, I get SQL0440N - function "XXX" not found...

You have to go through dynamic SQL. To do that in a UDF you need a
helper procedure.
Here is a recipe (untested):

--#SET TERMINATOR @
CREATE PROCEDURE poo(IN a INT, OUT aplus1 INT)
BEGIN
DECLARE txt VARCHAR(200);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SET aplus1 = a;
IF a < 60 THEN
SET txt = 'SELECT FOO('|| CHAR(a) || ') + 1 FROM SYSIBM.SYSDUMMY1';
PREPARE stmt FROM txt;
OPEN cur;
FETCH cur INTO aplus1;
CLOSE cur1;
END IF;
END
@

CREATE FUNCTION FOO(a INT) RETURNS INT
BEGIN ATOMIC
DECLARE aplus1 INT;
CALL POO(a, aplus1);
RETURN aplus1;
END
@
--#SET TERMINATOR ;

VALUES FOO(1);

Note that the permitted level of recursion is 64 in DB2 9 (up from 16 in
DB2 V8)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

SET txt = 'SELECT FOO('|| CHAR(a) || ') + 1 FROM SYSIBM.SYSDUMMY1';

Note: In your production solution you want to use parameter markers for
the function arguments of course. I was a bit sloppy here.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Szymon Dembek]

Serge Rielau wrote:

Serge Rielau wrote:

> SET txt = 'SELECT FOO('|| CHAR(a) || ') + 1 FROM SYSIBM.SYSDUMMY1';

Note: In your production solution you want to use parameter markers for
the function arguments of course. I was a bit sloppy here.

Thanks a lot !

--
Szymon Dembek

[--CELKO--]

>Is it possible to write a recursive (a function which calls itself) UDF in DB2 (v.9) ? <<

Perhaps if you post your actual problem, we can find a way to avoid
recursion.