{"action":"closed","issue":{"url":"https://api.github.com/repos/flyway/flyway/issues/1111","labels_url":"https://api.github.com/repos/flyway/flyway/issues/1111/labels{/name}","comments_url":"https://api.github.com/repos/flyway/flyway/issues/1111/comments","events_url":"https://api.github.com/repos/flyway/flyway/issues/1111/events","html_url":"https://github.com/flyway/flyway/issues/1111","id":112828977,"number":1111,"title":"Support for Vertica (or replace) Function","user":{"login":"klandon","id":3999130,"avatar_url":"https://avatars.githubusercontent.com/u/3999130?v=3","gravatar_id":"","url":"https://api.github.com/users/klandon","html_url":"https://github.com/klandon","followers_url":"https://api.github.com/users/klandon/followers","following_url":"https://api.github.com/users/klandon/following{/other_user}","gists_url":"https://api.github.com/users/klandon/gists{/gist_id}","starred_url":"https://api.github.com/users/klandon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/klandon/subscriptions","organizations_url":"https://api.github.com/users/klandon/orgs","repos_url":"https://api.github.com/users/klandon/repos","events_url":"https://api.github.com/users/klandon/events{/privacy}","received_events_url":"https://api.github.com/users/klandon/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-10-22T15:29:54Z","updated_at":"2015-10-26T14:58:10Z","closed_at":"2015-10-26T14:58:10Z","body":"Seems creating functions with flyway in vertica works fine if the statement starts with \"CREATE FUNCTION\" but if you use the support \"CREATE OR REPLACE FUNCTION\" then the script breaks on the firs \";\" and fails to migrate.\r\n\r\nExample of code :\r\nCREATE OR REPLACE FUNCTION default_to_hello(x VARCHAR) RETURN VARCHAR\r\nAS BEGIN\r\n-- The body of a SQL function in Vertica must be in the form 'RETURN expression'\r\nRETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 'Hello' END);\r\nEND;\r\n\r\nError Produced:\r\n\r\nCurrent version of schema \"public\": 2\r\nMigrating schema \"public\" to version 3 - test\r\nERROR: Migration of schema \"public\" to version 3 failed! Please restore backups and roll back database and code!\r\nERROR:\r\n\r\nMigration V3__test.sql failed\r\n\r\nSQL State : 42601\r\nError Code : 4856\r\nMessage : [Vertica]VJDBC ERROR: Syntax error at or near \"EOL\"\r\nLocation : /opt/flywaydb/sql/V3__test.sql (/opt/flywaydb/sql/V3__test.sql)\r\nLine : 1\r\nStatement : CREATE or replace FUNCTION default_to_hello(x VARCHAR) RETURN VARCHAR\r\nAS BEGIN\r\n-- The body of a SQL function in Vertica must be in the form 'RETURN expression'\r\nRETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 'Hello' END)\r\n\r\nCode run in vertica :\r\n\r\ndbadmin=> CREATE or replace FUNCTION default_to_hello(x VARCHAR) RETURN VARCHAR\r\ndbadmin-> AS BEGIN\r\ndbadmin-> -- The body of a SQL function in Vertica must be in the form 'RETURN expression'\r\ndbadmin-> RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 'Hello' END);\r\ndbadmin-> END;\r\nCREATE FUNCTION\r\ndbadmin=> select default_to_hello('Working');\r\n\r\ndefault_to_hello\r\n\r\nWorking\r\n(1 row)\r\n\r\ndbadmin=> select default_to_hello(null);\r\n\r\ndefault_to_hello\r\n\r\nHello\r\n(1 row)"}}