Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for Vertica (or replace) Function #1111

Closed
klandon opened this issue Oct 22, 2015 · 2 comments
Closed

Support for Vertica (or replace) Function #1111

klandon opened this issue Oct 22, 2015 · 2 comments

Comments

@klandon
Copy link

klandon commented Oct 22, 2015

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.

Example of code :
CREATE OR REPLACE FUNCTION default_to_hello(x VARCHAR) RETURN VARCHAR
AS BEGIN
-- The body of a SQL function in Vertica must be in the form 'RETURN expression'
RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 'Hello' END);
END;

Error Produced:

Current version of schema "public": 2
Migrating schema "public" to version 3 - test
ERROR: Migration of schema "public" to version 3 failed! Please restore backups and roll back database and code!
ERROR:

Migration V3__test.sql failed

SQL State : 42601
Error Code : 4856
Message : [Vertica]VJDBC ERROR: Syntax error at or near "EOL"
Location : /opt/flywaydb/sql/V3__test.sql (/opt/flywaydb/sql/V3__test.sql)
Line : 1
Statement : CREATE or replace FUNCTION default_to_hello(x VARCHAR) RETURN VARCHAR
AS BEGIN
-- The body of a SQL function in Vertica must be in the form 'RETURN expression'
RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 'Hello' END)

Code run in vertica :

dbadmin=> CREATE or replace FUNCTION default_to_hello(x VARCHAR) RETURN VARCHAR
dbadmin-> AS BEGIN
dbadmin-> -- The body of a SQL function in Vertica must be in the form 'RETURN expression'
dbadmin-> RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 'Hello' END);
dbadmin-> END;
CREATE FUNCTION
dbadmin=> select default_to_hello('Working');

default_to_hello

Working
(1 row)

dbadmin=> select default_to_hello(null);

default_to_hello

Hello
(1 row)

@mverrilli
Copy link
Contributor

#1112 if you want to try it.

@klandon
Copy link
Author

klandon commented Oct 26, 2015

Seems to be good to go, thanks.

@klandon klandon closed this as completed Oct 26, 2015
axelfontaine pushed a commit to flyway/flywaydb.org that referenced this issue Oct 27, 2015
@axelfontaine axelfontaine added this to the Flyway 4.0 milestone Oct 27, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants