How do I create an alias for a built-in function?

bloopie

The issue is that an external application makes a query to Vertica with the SQL command COUNT_BIG. Vertica doesn't provide a function called COUNT_BIG as it has combined with COUNT. I want to make an alias/synonym called COUNT_BIG which would call the COUNT function. How do I go about this?

We're developing UDx functions in Python. Can I simulate a COUNT-like aggregate function in a UDx?

Bryan_H

You can create an alias using CREATE FUNCTION (SQL) as described at https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm
You could probably write a UDTF in Python to emulate COUNT_BIG, but the above will emulate COUNT_BIG more simply if you don't need further calculations.

bloopie

@Bryan_H said:
You can create an alias using CREATE FUNCTION (SQL) as described at https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm
You could probably write a UDTF in Python to emulate COUNT_BIG, but the above will emulate COUNT_BIG more simply if you don't need further calculations.

I tried something like:
CREATE FUNCTION
COUNT_BIG
RETURN BIGINT
AS
BEGIN
RETURN COUNT;
END;
This is obviously not correct, but how do I know what datatype/arguments the COUNT function should receive? I tried something similar and got an error saying something like "return should be simple".

Bryan_H

OK, I stand corrected: CREATE FUNCTION (SQL) doesn't operate as a macro, and thus can't parse COUNT as this is non-scalar. So you could write a COUNT_BIG UDTF in Python, or if you have a support contract, you could request a new feature to add a COUNT_BIG macro to be added to the Vertica engine - this would be fairly trivial since INTEGER and BIGINT are the same data type in Vertica (64-bit integer), so COUNT_BIG would just be an alias or macro on COUNT.

LenoyJ

Probably use Directed Queries? Maybe a little wonky and you'll have to do if for every type of query coming in from your tool.
Something like:

--Create a pseudo sql function. We'll need this else the next statement will return an error.
CREATE FUNCTION count_big(x INT) RETURN INT
AS BEGIN 
 RETURN (x); 
END;

--Create Directed query
SAVE QUERY SELECT count_big(1) AS count FROM iris;
CREATE DIRECTED QUERY CUSTOM 'redirect_countbig_to_count' SELECT count(1) AS count FROM iris;
ACTIVATE DIRECTED QUERY redirect_countbig_to_count;

Check if it works:

dbadmin=> SELECT count_big(1) AS count FROM iris;
 count
-------
   150
(1 row)

More Info: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/DirectedQueries/CustomDirectedQueries.htm