Creating user-defined SQL functions
A user-defined SQL function can be used anywhere in a query where an ordinary SQL expression can be used, except in the table partition clause or the projection segmentation clause.
To create a SQL function, the user must have CREATE privileges on the schema. To use a SQL function, the user must have USAGE privileges on the schema and EXECUTE privileges on the defined function.
This following statement creates a SQL function called myzeroifnull that accepts an INTEGER argument and returns an INTEGER result.
=> CREATE FUNCTION myzeroifnull(x INT) RETURN INT
AS BEGIN
RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);
END;
You can use the new SQL function (myzeroifnull) anywhere you use an ordinary SQL expression. For example, create a simple table:
=> CREATE TABLE tabwnulls(col1 INT);
=> INSERT INTO tabwnulls VALUES(1);
=> INSERT INTO tabwnulls VALUES(NULL);
=> INSERT INTO tabwnulls VALUES(0);
=> SELECT * FROM tabwnulls;
a
---
1
0
(3 rows)
Use the myzeroifnull function in a SELECT statement, where the function calls col1 from table tabwnulls:
=> SELECT myzeroifnull(col1) FROM tabwnulls;
myzeroifnull
--------------
1
0
0
(3 rows)
Use the myzeroifnull function in the GROUP BY clause:
=> SELECT COUNT(*) FROM tabwnulls GROUP BY myzeroifnull(col1);
count
-------
2
1
(2 rows)
If you want to change a user-defined SQL function's body, use the CREATE OR REPLACE syntax. The following command modifies the CASE expression:
=> CREATE OR REPLACE FUNCTION myzeroifnull(x INT) RETURN INT
AS BEGIN
RETURN (CASE WHEN (x IS NULL) THEN 0 ELSE x END);
END;
To see how this information is stored in the Vertica catalog, see Viewing Information About SQL Functions.