Consider supporting numbered parameters

[Arthurm1]

As well as...

val f: Query[String ~ Int, Country] =
  sql"""
    SELECT name, population
    FROM   country
    WHERE  name LIKE $varchar
    AND    population < $int4
  """.query(country)

it would be nice to have something like...

val f: Query[String ~ Int, Country] =
  sql"""
    SELECT name, population
    FROM   country
    WHERE  name LIKE $1
    AND    population < $2
  """.query(country)
     .params(varchar ~ int4)

or similar

Occasionally I've come across queries that simply can't be supported by the inability to re-use parameters within it. It's always possible to rewrite the query to cope with this but it nearly always becomes more complicated and I feel that the query shouldn't be subject to the limitation of the database api.

Here's a contrived example that doesn't work because the same value has had to be split into 2 params...

PREPARE foo_fail (int, int) AS
  select case when a.someData > $1 then 0 else a.someData end,
         sum(a.someOtherData)
  from (select 1 as someData, 2 as someOtherData) a
  group by case when a.someData > $2 then 0 else a.someData end;
EXECUTE foo_fail(1, 1);

and here it works with a repeated use of a single param...

PREPARE foo_success (int) AS
  select case when a.someData > $1 then 0 else a.someData end,
         sum(a.someOtherData)
  from (select 1 as someData, 2 as someOtherData) a
  group by case when a.someData > $1 then 0 else a.someData end;
EXECUTE foo_success(1);