SQL Functions don't allow: FROM,

dhaller

I was thinking of using a Vertica function to take the place of a large chunk of SQL code into which has to be inserted a filter parameter for account_id at various places. Initially it seems I could have used SQL Functions, but according to the following descriptions, probably not.

There seems to a huge disconnect between this explanation of SQL Functions:
https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONStatements.htm%3FTocPath%3DSQL%2520Reference%2520Manual%7CSQL%2520Statements%7CCREATE%2520FUNCTION%2520Statements%7C_____0

where it states:
"User defined SQL functions are useful for executing complex queries and combining HP Vertica built-in functions"

Looking further into the CREATE SQL FUNCTION page: https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm

"Note: Only one RETURN expression is allowed in the CREATE FUNCTION definition. FROM, WHERE, GROUP BY, ORDER BY, LIMIT, aggregation, analytics, and meta function are not allowed."

Not using FROM, WHERE, GROUP BY?? How can the result still be a complex query, or any query at all? Am I missing something there?

emoreno

It will be nice if we can see an example of what you want to do. In Vertica you have different ways to extended the usage,

• External Procedures: where you use select to call a script no matter the language. You don't use FROM, WHERE, ETC, just call the script that inside can have a connection to vertica and work with tables.
• SQL functions that you can stare useful sql functions to make it simple, you pass parameters and return a value.
• User Define Functions that you can code in C++, Java or Python to do more complex calculations.

Not sure what is the best for your case, but probably if you look at the programming guide you can find examples that can help you to define what is the best for your use case.

https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/ExtendingHPVertica/OptionsForExtendingVertica.htm?TocPath=Extending%20HP%20Vertica|_____1

I hope this helps.

Eugenia

dhaller

Thanks for looking at this issue.

From the documents it seems that my situation is not supported. I would like something like this:

SELECT * FROM (
SELECT vertica_function_block1(1000)
UNION
SELECT vertica_function_block2(1000)
)sub
;

Where these functions would be different complex subqueries that allow me to pass a parameter for account_id and they would return rows of data:

Example function template:

SELECT * FROM A
JOIN B ON a.id=b.id
WHERE a.account_id = <>
;

However, the SQL function in Vertica cannot contain these SQL expressions FROM, WHERE, GROUP BY ,etc, so this type of function is not supported. Correct?