Support named parameters for postgres Spanner

[jkebinger]

Thanks for stopping by to let us know something could be better!

I'm always frustrated when I have to write ugly queries and code with $1/p1 place holders

In postgres dialect spanner the following is required to bind a parameter

StatementBuilder.new("SELECT * from foo where bar = $1").bind("p1").to(1);

Google SQL dialect spanner lets us do

StatementBuilder.new("SELECT * from foo where bar = :bar").bind("bar").to(1);

in these trivial examples it doesn't seem like a big difference but as queries grow, especially dynamically building them, the numbered place holders get unwieldy

Describe the solution you'd like
Please add the same support for named params in postgres dialect as the google sql dialect

Describe alternatives you've considered
For some workloads I've used google sql dialect instead.
I could probably write some string parsing code on top to look for named placeholders and do some named placeholder to number placeholder mapping

Additional context

I'm on version 26.10.0 of the google cloud libraries-bom so if this feature has been enabled since, please let me know.

[jkebinger]

Turns out this is pretty easy to workaround, simple helper method can do it (below). Thanks to chatGPT for polishing it a bit.

Still weird that this is only needed for one dialect

package cloud.prefab.server.data.spanner;

import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.Value;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.ListMultimap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Helper class for handling named parameters in SQL queries.
 */
public class NamedParamsHelper {

  private static final Pattern COLON_WORD_FINDER = Pattern.compile(":(\\w+)");

  /**
   * Builds a SQL statement with named parameters replaced by positional parameters.
   *
   * @param query The SQL query with named parameters.
   * @param bindings Map of parameter names to their values.
   * @return A Statement.Builder with positional parameters.
   * @throws IllegalStateException if a binding for a parameter is not found.
   */
  public static Statement.Builder buildWithNamedParams(
    String query,
    Map<String, Value> bindings
  ) {
    var modifiedQuery = new StringBuilder();
    int index = 1;
    Matcher matcher = COLON_WORD_FINDER.matcher(query);
    ListMultimap<String, Integer> placeHolders = ArrayListMultimap.create();

    while (matcher.find()) {
      placeHolders.put(matcher.group(1), index);
      matcher.appendReplacement(modifiedQuery, "\\$" + index);
      index++;
    }
    matcher.appendTail(modifiedQuery);

    var builder = Statement.newBuilder(modifiedQuery.toString());
    for (var paramAndIndexes : placeHolders.asMap().entrySet()) {
      Value value = bindings.get(paramAndIndexes.getKey());
      if (value == null) {
        throw new IllegalStateException(
          "No binding found for parameter: " + paramAndIndexes.getKey()
        );
      }
      paramAndIndexes
        .getValue()
        .forEach(paramIndex -> builder.bind("p" + paramIndex).to(value));
    }
    return builder;
  }
}

[olavloite]

@jkebinger The reason that named parameters are not supported for PostgreSQL is quite simply because the standard PostgreSQL dialect does not support it. We therefore cannot support it for two reasons:

1. The main reason for supporting PostgreSQL dialect is to guarantee portability and compatibility with PostgreSQL.
2. We actually use the PostgreSQL query parser to parse the SQL strings. This parser does not support named parameters, which would mean that we would have to patch it to add support for named parameters.

[olavloite]

Closing this, as this is works-as-intended.