The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
How do I create an alias for a built-in function?

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?
0
Answers
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".
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.
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:
Check if it works:
More Info: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/DirectedQueries/CustomDirectedQueries.htm