CREATE ROW TYPE complex(r FLOAT, i FLOAT);
CREATE FUNCTION plus(c1 complex, c2 complex) RETURNING complex; RETURN row(c1.r + c2.r, c1.i + c2.i)::complex;END FUNCTION;
CREATE FUNCTION divide(c1 complex, count INT) RETURNING complex; RETURN row(c1.r/count, c1.i/count)::complex;END FUNCTION;
Once these operators are supported functions like sum() and avg()start working for complex numbers. This made me wonder about building a more complete treatment of complex numbers into the server, and how much can be donewithout resorting to external functions. How about the product of two complexnumbers which is defined as:
- (a + ib)(c + id) = (ac - db) + i(bc + ad)
- (a + ib) / (c + id) = (ac + bd)/(c^2 + d^2) + i(bc - ad)/(c^2 + d^2)
Following the same logic as the documented example, these, along with the modulus could be defined as:
CREATE FUNCTION times(c1 complex, c2 complex) RETURNING complex; RETURN row((c1.r * c2.r) - (c1.i * c2.i), (c2.r * c1.i) + (c1.r * c2.i))::complex;END FUNCTION;
CREATE FUNCTION divide(c1 complex, c2 complex) RETURNING complex; RETURN row(((c1.r * c2.r) + (c1.i * c2.i))/((c2.r * c2.r) + (c2.i * c2.i)), ((c1.i * c2.r) - (c1.r * c2.i))/((c2.r * c2.r) + (c2.i * c2.i)))::complex;END FUNCTION;
CREATE FUNCTION modulus(c1 complex) RETURNING float; RETURN sqrt((c1.r * c1.r) + (c1.i * c1.i));END FUNCTION;
Let's put it to the test. Multiplying i*i should result in -1.
CREATE TABLE t1(col1 complex);insert into t1 values(ROW(0,1)::complex);select col1*col1 from t1;...(expression) ROW(-1.00000000000,0.00)1 row(s) retrieved.
So far so good.
Now let's see if these functions can be used as the basis for more advanced operations such as transformations in Wessel's complex plane. I'll pick theparticularly scenic subset of the complex plane known as the Mandelbrot set, which is built by repeated iterations of the transformation
- z -> z^2 + c
Here's a function written in Stored Procedure Language that takes a point on thecomplex plane as an argument and returns 0 if it is a member of the Mandelbrotset, or the number of iterations used to determine that it is not a member ofthe set, which could then be charted as a colour by a client application.
CREATE FUNCTION mandelbrot(c complex) RETURNING int;DEFINE iter, max_iter, result int;DEFINE z complex;LET max_iter = 999;LET z = ROW(0,0)::complex;FOR iter = 1 TO max_iter LET z = (z*z) + c; IF modulus(z) > 2 THEN EXIT FOR; END IF;END FOR;IF iter > max_iter THEN LET result = 0;ELSE LET result = iter;END IF;RETURN result;END FUNCTION;
Let's test a couple of coordinates:
execute function mandelbrot(ROW(0,1)::complex);execute function mandelbrot(ROW(1,1)::complex);...(expression) 01 row(s) retrieved.There you have it, the Mandelbrot set constructed entirely from SQL & SPL! I'm not sure ifI'll ever get around to writing a client to call this function and display the results for a set of coordinates.
(expression) 21 row(s) retrieved.
Update 8/25/06: I got around to it here.
In case you're wondering what possible use there could ever possibly be for constructing a Mandelbrot set using SQL.. well, probably none, it's just a thought experiment to push the IDS built-in mathematics capabilities and see where they go...