0420 Custom Functions in MonetDB part 3

WHILE statement

In procedural SQL, WHILE statement is used for iterative statement where we repeat a block of SQL code as long as a specified condition is true. In the example bellow, we will repeat the loop 5 times, because our argument has 5 letters = length ( 'Surat' ).

CREATE OR REPLACE FUNCTION funcWhile( argWord VARCHAR(20) ) RETURNS STRING BEGIN     DECLARE i INT; SET i = 1;     DECLARE Res VARCHAR(40); SET Res = '';     WHILE i <= length( argWord ) DO         SET Res = Res || '_' || SUBSTRING( argWord, i, 1 );         SET i = i + 1;     END WHILE;     RETURN TRIM( Res, '_' ); END;

(1)     '' + '_' + Surat = _S (2)     _S + '_' + Surat = _S_u (3)     _S_u + '_' + Surat = _S_u_r (4)     _S_u_r + '_' + Surat = _S_u_r_a (5)     _S_u_r_a + '_' + Surat = _S_u_r_a_t TRIM( _S_u_r_a_t, '_' ) = S_u_r_a_t SELECT funcWhile('Surat');

This example will return the smallest number X for which 2X >= argument. SELECT funcExponential(7);

CREATE OR REPLACE FUNCTION funcExponential( argNumber INTEGER ) RETURNS INTEGER BEGIN     DECLARE X INT; SET X = 1;     WHILE POWER( 2, X ) < argNumber DO         SET X = X + 1;     END WHILE;     RETURN X; END;

CREATE OR REPLACE FUNCTION funcFactorial( argNumber INTEGER ) RETURNS INTEGER BEGIN     DECLARE Res INTEGER; SET Res = 1;     WHILE 1 < argNumber DO         SET Res = Res * argNumber;         SET argNumber = argNumber - 1;     END WHILE;     RETURN Res; END;

This is an example where we are calculating factorial of an argument. SELECT funcWhile(4);

Infinite Loop

If we made a mistake, our WHILE statement can create an infinite loop. In that case, solution is to violentely break the process. In mclient we can exit by typing "Ctrl + C" to exit execution.

CREATE OR REPLACE FUNCTION funcNeverEnd( argNumber INTEGER ) RETURNS INTEGER BEGIN     WHILE 1 = 1 DO         SET argNumber = argNumber + 1;     END WHILE;     RETURN argNumber; END;

SELECT funcNeverEnd(1);

Nesting WHILE

It is possible to nest WHILE statement inside of another WHILE statement. SELECT * FROM funcNested();

CREATE OR REPLACE FUNCTION funcNested() RETURNS TABLE( Res INT ) BEGIN     DECLARE i INT; SET i = 1;     DECLARE j INT;     DECLARE TABLE tabRes ( Res INT );     WHILE i <= 2 DO         SET j = 1;         WHILE j <= 2 DO             INSERT INTO tabRes VALUES ( i * 10 + j );             SET j = j + 1;         END WHILE;         SET i = i + 1;     END WHILE;     RETURN tabRes; END;

IF Statement in Procedural SQL

For conditional logic we can use IF statement. CREATE OR REPLACE FUNCTION funcIF() RETURNS INTEGER BEGIN     IF ( 1 = 2 )     THEN RETURN 1;     ELSE RETURN 2;     END IF; END;

Because 1 <> 2, the returned value is 2. SELECT funcIF();

It is also possible to have several conditions inside of the IF statement. CREATE OR REPLACE FUNCTION funcIF() RETURNS INTEGER BEGIN     IF ( 1 = 2 ) THEN RETURN 1;     ELSEIF ( 1 = 2 ) THEN RETURN 2;     ELSEIF ( 1 = 1 ) THEN RETURN 3;     ELSE RETURN 3;     END IF; END;

One of these conditions is TRUE, so the final result will be 3. SELECT funcIF();

Breaking WHILE Loop

MonetDB doesn't support BREAK keyword in a WHILE loop. We have to use flag variable to mark when we want to exit the loop. We will set our flag variable to one value, and we will examine whether it has that value at the start of each cycle. Inside of the loop we can change the value of a flag variable when conditions are met. That change will make condition of a loop to fail. SELECT funcBREAK();

CREATE OR REPLACE FUNCTION funcBREAK() RETURNS INTEGER BEGIN     DECLARE i INTEGER; SET i = 1;     DECLARE Flag INTEGER; SET Flag = 0;     WHILE i <= 10 AND Flag = 0 DO         SET i = i + 1;         IF i = 5 THEN             SET Flag = 1;         END IF;     END WHILE;     RETURN i; END;

Skip Some Cycles in a WHILE Loop

MonetDB doesn't support CONTINUE keyword in a WHILE loop. If we want to conditionally skip some cycles, we should wrap our statements in the IF function. In the example to the right, we are calculating total of the numbers between 1 and 10, but we are skipping all the even numbers. Result should be 1+3+5+7+9=25.   SELECT funcCONTINUE();

CREATE OR REPLACE FUNCTION funcCONTINUE() RETURNS INT BEGIN     DECLARE i INT; SET i = 1;     DECLARE Res INT; SET Res = 0;     WHILE i < 10 DO         IF MOD( i, 2 ) <> 0  THEN             SET Res = Res + i;         END IF;         SET i = i + 1;     END WHILE;     RETURN Res; END;

Functions Overloading

In the same schema it is possible to create several functions with the same name. Trick is that these functions must have different arguments. I will create three simple functions.

CREATE FUNCTION funcOverload() RETURNS INTEGER BEGIN     RETURN 1; END;

CREATE FUNCTION funcOverload(A INT) RETURNS INTEGER BEGIN     RETURN A; END;

CREATE FUNCTION funcOverload(A INT, B INT) RETURNS INTEGER BEGIN     RETURN A + B; END;

Creation was successful so we can now test our functions:

SELECT funcOverload();

SELECT funcOverload(5);

SELECT funcOverload(20,5);

Deleting of Overloaded Functions

When we try to delete overloaded function, MonetDB will complain that we have to provide the full signature of a function. DROP FUNCTION funcOverload;

I will delete the version of the function, with one argument, by using full singature. DROP FUNCTION funcOverload(INT);

Now this version doesn't work.

Other functions still work.

System Tables

System table sys.functions contains all of our functions.   SELECT * FROM sys.functions WHERE name = 'funcneverend';

If we want to list all of the functions that are created by us, we have to filter out system functions.

SELECT * FROM sys.functions WHERE system = false;

Function "funcNeverEnd" has ID 14331. We can use that fact to read about its arguments. SELECT * FROM sys.args WHERE func_id = 14331;

There will always be one argument named "result". This is the argument that represents the value that the function will return.