CREATE FUNCTION using JDBC connection

nirka

Hi,

I'm using DBVisualizer to connect to vertica (with JDBC connection).

I tried to create a simple function from myVertica page:
https://my.vertica.com/docs/7.0.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;

It works with vsql, but doesn't work using JDBC with DBVisualizer.
I get this error:

ERROR: Syntax error at or near "EOL"

Its not possible to create function this way?

Thanks

Jim_Knicely

Hi,

Run it like this:

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

Or, you should be able to execute the entire buffer using the 'SQL Commander/ Execute Buffer' menu selection. According the the documentation for DbVisualizer:
Execute Buffer sends the complete editor buffer for execution as one statement. No comments are removed and no parsing of individual statements based on any delimiters is made. This operation is useful when executing SQL blocks or SQLs used to create procedures, functions, etc.

nirka

Thanks, the block start / end needs to be in new lines and it works