Skip to main content

SQL Functions don't allow: FROM,

  • February 9, 2017
  • 1 reply
  • 13 views
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?

    1 reply

    dhaller
    • dhaller
    • Author
    • New Participant
    • February 10, 2017

    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?

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement