Variable substitution tokens incompatible with PostgreSQL

[Fresa]

$variablename$ is used by PostgreSQL as dollar quoting: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

It would be nice to be able to decide which token to use, or completely swap out the VariableSubstitutionPreprocessor.

[AdrianJSClark]

There isn't a straightforward seam around the variable substitution code to make that change. However, I've got two ways forward that will work within the current framework. One is a work-around and one is a quick summary of what the fix would be.

Unfortunately I'm a bit time-poor at the moment, and am not particularly familiar with PostgreSQL, so can't implement either in full myself.

The Work Around

1. Create a PostgreSQL-specific variable preprocessor by inheriting from VariableSubstitutionSqlParser and specifying your own delimiter characters.
2. Create your own implementation of IScriptPreprocessor based on the existing VariableSubstitutionPreprocessor code with the important change of using your custom subclass of VariableSubstitutionSqlParser from the first step.
3. Disable the built-in variable substitution pre-processor by calling .WithVariablesDisabled() at configuration time.
4. Add your custom script preprocessor to your database upgrader using the .WithPreprocessor(...) extension method during configuration.

The Proper Fix

1. Create a PostgreSQL-specific variable preprocessor by inheriting from VariableSubstitutionSqlParser and specifying appropriate delimiter characters.
2. Create a PostgreSQL implementation of IScriptPreprocessor based on the existing VariableSubstitutionPreprocessor code with the important change of using the custom subclass of VariableSubstitutionSqlParser from the first step.
3. Copy and modify SqlScriptExecutor so that it creates the customised PostgreSQL variable substitution preprocessor at the appropriate time.
4. Update all configuration extension methods for PostgreSQL to wire-in the replacement, specialised SqlScriptExecutor.

[droyad]

Also see linked issues for more use cases