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

Allow variable escaping for PostgreSQL scripts #9

Closed
matkokostelic opened this issue Oct 12, 2022 · 4 comments
Closed

Allow variable escaping for PostgreSQL scripts #9

matkokostelic opened this issue Oct 12, 2022 · 4 comments
Labels
enhancement New feature or request

Comments

@matkokostelic
Copy link

matkokostelic commented Oct 12, 2022

If SQL script contains something like $BODY$ (used in postgresql to create functions), exception happens:

Upgrade failed due to an unexpected exception: System.InvalidOperationException: Variable BODY has no value defined at DbUp.Engine.Preprocessors.VariableSubstitutionSqlParser.<>c__DisplayClass3_0.<ReplaceVariables>b__1(String name) at DbUp.Support.SqlParser.Parse() at DbUp.Engine.Preprocessors.VariableSubstitutionSqlParser.ReplaceVariables(IDictionary2 variables) at DbUp.Engine.Preprocessors.VariableSubstitutionPreprocessor.Process(String contents) at DbUp.Support.ScriptExecutor.PreprocessScriptContents(SqlScript script, IDictionary2 variables) at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary2 variables) at DbUp.Engine.UpgradeEngine.PerformUpgrade()

SQL script is like this:

CREATE FUNCTION public.fn_something( b_id integer) RETURNS integer LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ BEGIN RETURN ( select count(*) as something from sometable where id=b_id); END $BODY$;

Any other idea than renaming $BODY$ to something which will not be picked up as variable?

@matkokostelic matkokostelic added the enhancement New feature or request label Oct 12, 2022
@felipeschneider88
Copy link

I want to know if there is a way. So far, I only manage to pass any function or SP with this
.WithVariablesDisabled() //added to accept $func$ in the .sql files

The whole code is this

var dbup = DeployChanges.To .PostgresqlDatabase(csb.ConnectionString) .WithScriptsFromFileSystem("./sql") .JournalToPostgresqlTable("public", "dbupJournal") .WithVariablesDisabled() //added to accept $func$ in the .sql files .LogToConsole() .Build();

@szyb
Copy link

szyb commented Nov 30, 2022

For PostgreSQL you may also replace $BODY$ to just $$.
The other workaround I found is to add variable named BODY with value $BODY$

.WithVariable("BODY", "$BODY$")

But anyway, escaping would be very useful (this is a first thought of the programmer)

@grendizeras
Copy link

grendizeras commented Aug 31, 2023

Would be nice if you implement this feature, and maybe make it more general, by allowing end users to specify which symbols should be treated like variables. We have insert statements, that has text that contains '$ ' symbol and because of that it breaks migration. If I could to set variable symbol like '$#' or '$$' (e.g. $$Var$$ ) it would be very handy and no fancy logic here

Can be done by setting VariableSubstitutionSqlParser.VariableDelimiter in derived class

@droyad droyad transferred this issue from DbUp/DbUp Jan 30, 2024
@droyad
Copy link
Member

droyad commented Dec 10, 2024

Duplicate of #1 as far as I can tell

@droyad droyad marked this as a duplicate of #1 Dec 10, 2024
@droyad droyad closed this as completed Dec 10, 2024
@github-project-automation github-project-automation bot moved this from Wishlist to Done in Issue sorting Dec 10, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
Status: Done
Development

No branches or pull requests

5 participants