Allow variable escaping for PostgreSQL scripts

[matkokostelic]

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?

[felipeschneider88]

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]

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]

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