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.