Pgx: Named arguments

[jeromer]

It would be useful to support named arguments in queries instead of only $x placeholders.

SQL queries would look like this:

SELECT [...] FROM table WHERE foo=:arg;

INSERT INTO table [...] VALUES(:name, :foo, :bar);

And then we could do something like:

conn.QueryRow(
        somSQL,
        map[string]interface{}{
            "name": "jack",
            "foo": "hello",
            "bar": "baz",
        }
    )

Supporting this feature would be more friendly with Go implementations of yesql such as https://github.com/nleof/goyesql. Furthermore it would make it possible to use https://github.com/dimitri/regresql which is currently (as far as I know) not possible without named parameters.

Using QueryRow may not be possible because of the impact on the actual code (did not check). But using a new function like QueryRowWithMap (or any more meaningful name) would work.

I can write a patch if you find the feature useful for pgx.

:)

[jackc]

I'm open to this idea, but it may be more involved than it appears at first glance. PostgreSQL doesn't support named parameters. This would require query parsing (simple string substitution would not be sufficient) and rewriting. You can look at https://github.com/jackc/pgx/blob/master/internal/sanitize/sanitize.go to get an idea of the level of parsing required -- and/or maybe reuse most of it.

As far as the interface goes, I think the simplest would be a new function that maps a SQL string and map of arguments to a rewritten string and slice of arguments. e.g. ("select :id from users", map[string]interface{}{"id": 1}) maps to ("select $1 from users", []interface{1}).

Beyond that, I'm not super keen on adding QueryRowWithMap. When you consider the variants and types that delegate query methods, this probably would involve adding a dozen or more new public functions.

There are two ways that I can think of to insert this functionality into Query*.

One is to define a new type type NamedParamsMap map[string]interface{} and have a single argument of that type trigger the new behavior. That would definitely require any libraries requiring named args to specifically support pgx though.

The other is to trigger this behavior on a single argument of map[string]interface{} and a SQL string that has named arguments. Just the map is insufficient because that could already be a single argument to a JSON or hstore field.

Another question is how this does or doesn't interact with normal prepared statements.

[GeertJohan]

Perhaps pgx could adopt how sqlx solved this, by adding such things as PrepareNamed ?

[jackc]

Maybe. I think it would still require a simple parser and it looks like sqlx still ends up with "named" variants of a bunch of functions.

[fmmajd]

I think postgres now supports named arguments. I'm able to use them in the sql console.

[jackc]

The psql client supports named variables of a sort. But it is handled entirely client side. The PostgreSQL wire protocol only supports positional parameters.