Problem creating Vertica SQL functions

The problem is that I cannot execute "CREATE OR REPLACE FUNCTION" statements in SQL commander. I am able to create a function, though, by right-clicking the "Functions (SQL)" element in the tree, selecting "Create Function...", and using the GUI form. But when I open this newly created simple function in a tab, copy its generated code to an SQL Commander, and try to "Execute" - it results in an error: CREATE OR REPLACE FUNCTION "VADIM"."ff"() RETURN varchar(20) AS BEGIN RETURN ((('Hello, ' || "current_user"()) || '!'))::varchar(20); END; The error is: [CREATE - 0 row(s), 0.000 secs] [Error Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "EOL" ... Stopped because of errors I get the impression (see the screenshot which shows the selections after I click on the page icon next to the error message) that DbVisualizer interprets the text before the first semicolon after "RETURN" as the "current statement", and leaves out the "END;" part. And also I get the same error when I delete the "END;". I'm running the latest 9.2.3 Pro version on x64 Win7, and I saw the same issue in 9.2.1.

Hi Vadim, Please see this part of the Users Guide for how to execute complex SQL that contain multiple embedded statements: http://confluence.dbvis.com/display/UG92/Executing+Complex+Statements Also, you don't have to copy the function code to an SQL Commander tab to change it: just edit the code in the Function Editor and click Save: http://confluence.dbvis.com/display/UG92/Editing+a+Code+Object Best Regards, Hans

Thanks Hans! Indeed, using the SQL->Execute Buffer works well. That would simply require to keep functions in a separate source file. And while both other approaches described in your first link (using the Oracle-like slash to mark an end of a statement and redefining the delimiter) do work in DbVisualizer, such code wouldn't be compatible with vsql, Vertica's command line client. Also, using a specialized GUI to create or edit functions/procedures where the DDL is pulled from DB, is something that I would use only in rare cases. BTW, DbVisualizer truncates DDL code of long Vertica SQL functions that it pulls from DB. E.g., see this http://teknologk.blogspot.com/2013/06/viewing-function-definitions-in-vertica.html post for a possible reason and a possible fix.

Hi Vladim, Thanks for reporting the issue with truncated DDL for large functions. I have opened a ticket and we looking into this. Best Regards, Hans