Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

function

Hi,

 

I am not able to create any function in vertca 7.1

 

http://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm

 

CREATE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN
     RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);
   END;

 

This is throwing the below error.

 

Error: [Vertica][VJDBC](4856) ERROR: Syntax error at or near "EOL"
SQLState:  HY000
ErrorCode: 4856
Error occured in:
CREATE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN
     RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END)

 

 

Also select * from function; is showing Error: [Vertica][VJDBC](4566) ERROR: Relation "function" does not exist

 

 

 

Regards,

Deb

Comments

  • Hi Deb,

     

    The code works for me when I cut & paste it from your post. Since the error message mentions " Syntax error at or near "EOL"" could there be some sort unusual line ending character or control character in your script? What version of Vertica are you using (have you upgarded to the latest SP2, or are you using an earlier version?), and what locale do you have set?

     

    There is no "function" system table. Did you mean the USER_FUNCTIONS system table? That one shows you the user definde function you have created:

     

    dbadmin=> \x
    Expanded display is on.
    dbadmin=> select * from user_functions;
    -[ RECORD 1 ]----------+--------------------------------------------------------
    schema_name | public
    function_name | ApproxCountDistinct
    procedure_type | User Defined Aggregate

    . . .

    -[ RECORD 47 ]---------+--------------------------------------------------------
    schema_name | public
    function_name | myzeroifnull
    procedure_type | User Defined Function
    function_return_type | Integer
    function_argument_type | x Integer
    function_definition | RETURN CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END
    volatility | immutable
    is_strict | f
    is_fenced | f
    comment |

    Did the documentation refer to a function system table?

  • Hi,

     

    I have install single node Vertica using Loop back IP (127.0.0.1) log back.

     

    Now I am adding nodes to make it 3 node cluster.

     

    But while doing update_vertica --add-hosts 16.183.41.34,16.183.41.35 its showing the below error

     

    Error: Existing single-node localhost (loopback) cluster cannot be expanded

    Hint: Move cluster to external address first. See online documentation.

    Installation FAILED with errors.

     

    What can be the solution to proceed ?

     

    Thanks in advance.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.