Recursive SQL PL ?
in [DB2]
|
Prev: strange query behaviour
Next: COUNT(*) Not accurate?
From: asceta on 31 Mar 2010 14:17 I was wondering, whether it is possible to create recursive SQL PL functions in DB2. A simple example: CREATE OR REPLACE FUNCTION fibo ( k integer ) RETURNS integer BEGIN IF k < 2 THEN return 1; ELSE return fibo(k-1) + fibo(k-2); END IF; END ! ends up with rather unexpected error: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL20481N The creation or revalidation of object xxxxxx would result in an invalid direct or indirect self-reference. LINE NUMBER=11. SQLSTATE=429C3 I use DB2 9.7 Express Anyone can help me solve that issue ?
From: Lennart on 31 Mar 2010 15:40 On 31 mar, 20:17, asceta <marek.patr...(a)gmail.com> wrote: > I was wondering, whether it is possible to create recursive SQL PL > functions in DB2. A simple example: > > CREATE OR REPLACE FUNCTION fibo ( > k integer > ) > RETURNS integer > BEGIN > IF k < 2 THEN > return 1; > ELSE > return fibo(k-1) + fibo(k-2); > END IF; > END ! > > ends up with rather unexpected error: > > DB21034E The command was processed as an SQL statement because it was > not a > valid Command Line Processor command. During SQL processing it > returned: > SQL20481N The creation or revalidation of object xxxxxx > would result in an invalid direct or indirect self-reference. LINE > NUMBER=11. > SQLSTATE=429C3 > > I use DB2 9.7 Express > > Anyone can help me solve that issue ? I don't think you can have the function call itself (I don't have access to 9.7 so I haven't tried, but that seems to be what the error message says). One option is to use a recursive cte in the function. In your case: create function fibo (k int) returns bigint return with next (i, j, n) as ( values (bigint(0), bigint(1), k) union all select j, i+j, n-1 from next where n>1 ) select max(j) from next @ [... ~]$ time db2 "values fibo(92)" 1 -------------------- 7540113804746346429 1 record(s) selected. real 0m0.046s user 0m0.011s sys 0m0.021s There are restrictions on what kind of recursion you are allowed to in a cte as well (I'm not entirely clear of what they are though). As an example you can't do ackerman because it involves nested recursive calls. /Lennart
From: Serge Rielau on 1 Apr 2010 11:30 > Anyone can help me solve that issue ? You can use dynamic SQL for the recursive part. So do a dynamic 'SET ? = ..' and then return the result of that. Note that DB2 imposed a limit of 64 levels on nesting. -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Lennart on 1 Apr 2010 15:03 On 31 mar, 20:17, asceta <marek.patr...(a)gmail.com> wrote: > I was wondering, whether it is possible to create recursive SQL PL > functions in DB2. A simple example: > > CREATE OR REPLACE FUNCTION fibo ( > k integer > ) > RETURNS integer > BEGIN > IF k < 2 THEN > return 1; > ELSE > return fibo(k-1) + fibo(k-2); > END IF; > END ! > > ends up with rather unexpected error: > > DB21034E The command was processed as an SQL statement because it was > not a > valid Command Line Processor command. During SQL processing it > returned: > SQL20481N The creation or revalidation of object xxxxxx > would result in an invalid direct or indirect self-reference. LINE > NUMBER=11. > SQLSTATE=429C3 > > I use DB2 9.7 Express > > Anyone can help me solve that issue ? I've got a private mail from a person that pointed out that in 9.7 there is a new concept, module. Since I don't have a 9.7 installation yet, I haven't checked it out. From his description it looks as if you can forward declare a function (pretty much the same way as you do with a header file). Anyhow, here's his example: CREATE MODULE M@ ALTER MODULE M PUBLISH FUNCTION FIBO ( K INTEGER ) RETURNS INTEGER@ ALTER MODULE M ADD FUNCTION FIBO ( K INTEGER ) RETURNS INTEGER BEGIN IF K < 2 THEN RETURN 1; ELSE RETURN FIBO(K-1) + FIBO(K-2); END IF; END@ VALUES(M.FIBO(25)) 1 ----------- 121393 1 record(s) selected. I'm curious, is it possible to define for example: CREATE MODULE X@ ALTER MODULE X PUBLISH FUNCTION ACK ( i INT, j INT ) RETURNS INT@ ALTER MODULE X ADD FUNCTION ACK ( i INT, j INT ) RETURNS INT BEGIN IF i = 0 THEN RETURN j+1; ELSE IF j = 0 THEN RETURN ACC(i-1, 1); ELSE RETURN ACC(i-1, ACC(i,j-1)); END IF; END@ VALUES(X.ACC(3,6)) @ Anyone with a working 9.7 install that is willing to try? /Lennart
From: Lennart on 1 Apr 2010 15:05
On 1 Apr, 21:03, Lennart <erik.lennart.jons...(a)gmail.com> wrote: > On 31 mar, 20:17, asceta <marek.patr...(a)gmail.com> wrote: > > > > > I was wondering, whether it is possible to create recursive SQL PL > > functions in DB2. A simple example: > > > CREATE OR REPLACE FUNCTION fibo ( > > k integer > > ) > > RETURNS integer > > BEGIN > > IF k < 2 THEN > > return 1; > > ELSE > > return fibo(k-1) + fibo(k-2); > > END IF; > > END ! > > > ends up with rather unexpected error: > > > DB21034E The command was processed as an SQL statement because it was > > not a > > valid Command Line Processor command. During SQL processing it > > returned: > > SQL20481N The creation or revalidation of object xxxxxx > > would result in an invalid direct or indirect self-reference. LINE > > NUMBER=11. > > SQLSTATE=429C3 > > > I use DB2 9.7 Express > > > Anyone can help me solve that issue ? > > I've got a private mail from a person that pointed out that in 9.7 > there is a new concept, module. Since I don't have a 9.7 installation > yet, I haven't checked it out. From his description it looks as if you > can forward declare a function (pretty much the same way as you do > with a header file). Anyhow, here's his example: > > CREATE MODULE M@ > > ALTER MODULE M PUBLISH FUNCTION FIBO ( K INTEGER ) RETURNS INTEGER@ > > ALTER MODULE M ADD FUNCTION FIBO ( K INTEGER ) > RETURNS INTEGER > BEGIN > IF K < 2 THEN > RETURN 1; > ELSE > RETURN FIBO(K-1) + FIBO(K-2); > END IF; > END@ > > VALUES(M.FIBO(25)) > > 1 > ----------- > 121393 > > 1 record(s) selected. > > I'm curious, is it possible to define for example: > > CREATE MODULE X@ > > ALTER MODULE X PUBLISH FUNCTION ACK ( i INT, j INT ) RETURNS INT@ > > ALTER MODULE X ADD FUNCTION ACK ( i INT, j INT ) > RETURNS INT > BEGIN > IF i = 0 THEN > RETURN j+1; > ELSE IF j = 0 THEN > RETURN ACC(i-1, 1); > ELSE > RETURN ACC(i-1, ACC(i,j-1)); > END IF; > END@ > > VALUES(X.ACC(3,6)) @ > > Anyone with a working 9.7 install that is willing to try? > > /Lennart Sorry, make that: CREATE MODULE X@ ALTER MODULE X PUBLISH FUNCTION ACK ( i INT, j INT ) RETURNS INT@ ALTER MODULE X ADD FUNCTION ACK ( i INT, j INT ) RETURNS INT BEGIN IF i = 0 THEN RETURN j+1; ELSE IF j = 0 THEN RETURN ACK(i-1, 1); ELSE RETURN ACK(i-1, ACC(i,j-1)); END IF; END@ VALUES(X.ACK(3,6)) @ /Lennart |