CREATE FUNCTION Examples
1. Create a function named “add” that adds the values that correspond to function parameters a and b, both of integer data type, and returns the result as an integer. The function is written in JavaScript and can be called with its parameters swapped.
CREATE OR REPLACE FUNCTION add(a INTEGER, b INTEGER) RETURN (INTEGER) AS LANGUAGE JAVASCRIPT SOURCE='return a+b',COMMUTATIVE=TRUE;
2. Create a function that adds the values that correspond to function parameters a, b, and c, all of integer data type, and returns the result as an integer. The function is written in JavaScript.
CREATE OR REPLACE FUNCTION add(a INTEGER, b INTEGER, c INTEGER) RETURN (INTEGER) AS LANGUAGE JAVASCRIPT SOURCE='return a+b+c';
3. Create a function in SQL that adds the integer value corresponding to parameter n to the timestamp value corresponding to parameter d and returns it as a timestamp.
CREATE OR REPLACE FUNCTION dateadd(d TIMESTAMP, n INTEGER) RETURN (TIMESTAMP) AS
BEGIN RETURN d+n*interval'1'day; END;
4. Create functions in SQL that calculate the hyperbolic sine, cosine, and tangent of the value corresponding to parameter x.
CREATE OR REPLACE FUNCTION cosh(x FLOAT8)RETURN(FLOAT8) AS
BEGIN RETURN (EXP(x)+EXP(-x))/2;END;
CREATE OR REPLACE FUNCTION sinh(x FLOAT8)RETURN(FLOAT8) AS BEGIN RETURN (EXP(x)-EXP(-x))/2;END;
CREATE OR REPLACE FUNCTION tanh(x FLOAT8)RETURN(FLOAT8) AS BEGIN RETURN sinh(x)/cosh(x); END;
5. Create a skew function in SQL:
CREATE OR REPLACE FUNCTION skew(x FLOAT8) RETURN(FLOAT8)AS
DECLARE n,av_x,av_x2,m3,sd FLOAT8 NOT NULL;
BEGIN
SELECT :n=count(x);
SELECT :av_x=sum(x)/n;
SELECT :av_x2=av_x*av_x;
SELECT :sd=sqrt((sum(x*x)-2*sum(x)*av_x+av_x2*n)/(n-1));
SELECT :m3=(sum(x*x*x)-3*av_x*sum(x*x)+3*av_x2*sum(x)-av_x2*av_x*n)/n;
RETURN m3/(sd*sd*sd)
END;
6. Create a kurtosis function in SQL:
CREATE OR REPLACE FUNCTION kurtosis(x FLOAT8) RETURN(FLOAT8)AS
DECLARE n,av_x,av_x2,m4,m2 FLOAT8 NOT NULL;
BEGIN
SELECT :n=count(x);
SELECT :av_x=sum(x)/n;
SELECT :av_x2=av_x*av_x;
SELECT :m2=(sum(x*x)-2*sum(x)*av_x+av_x2*n)/n
SELECT :m4=(sum(x*x*x*x)-4*av_x*sum(x*x*x)+6*av_x2*sum(x*x)-4*av_x2*av_x*sum(x)+
av_x2*av_x2*n)/n;
RETURN m4/(m2*m2)-3
END;
Not all errors are detected when the function is compiled.
Last modified date: 11/28/2023