Support Named Arguments in stdlib

[tebeka]

Is your feature request related to a problem? Please describe.
The stdlib driver does not support sql.NamedArg.

Describe the solution you'd like
I'd like statements like INSERT INTO journal (time, login, content) VALUES (:time, :login, :content); (or with @ instead of :) to work.

Describe alternatives you've considered
I can use "pgx" directory with pgx.NamedArgs

Additional context
Nope.

[jackc]

You can use pgx.NamedArgs with stdlib.

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
	_ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
	db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	var a, b, c string
	err = db.QueryRow("select @a::text, @b::text, @c::text", pgx.NamedArgs{"a": "foo", "b": "bar", "c": "baz"}).Scan(&a, &b, &c)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println(a, b, c)
}

[tebeka]

Thanks for answering. I know I can use pgx.NamedArgs (see issue), but I prefer to use the stdlib compatibility.
I'll have a look and see if I can submit a patch.

[jackc]

I know I can use pgx.NamedArgs (see issue), but I prefer to use the stdlib compatibility.

I don't understand. It is using stdlib / database/sql.

[tebeka]

Sorry for not being clear. I want to use sql.NamedVar and not pgx.NamedArgs. The idea is that the only reference to pgx is in the import, all the rest done via database/sql API.

[jackc]

Sorry for not being clear. I want to use sql.NamedVar and not pgx.NamedArgs. The idea is that the only reference to pgx is in the import, all the rest done via database/sql API.

Ah. I see now.

Some databases such as MSSQL directly support named parameters. PostgreSQL does not. As far as I can tell, sql.NamedArg was intended for databases with direct support.

While it is presumably possible to support it, I'm reluctant to do so. pgx.NamedArgs is explicit that it actually rewrites the query (i.e. it implements the pgx.QueryRewriter interface). But I'm not sure it is a good idea to automatically rewrite the SQL query.

• The MySQL driver for Go came to the same conclusion (Add support for named input parameters go-sql-driver/mysql#561).
• The old lib/pq driver never implemented it either (Implement Named Parameters lib/pq#534).
• See also NamedArg use in Query #351 for previous discussion.

[tebeka]

Thanks. My plan was to convert slice of sql.NamedArg to pgx.NamedArgs, any issues with this approach?

[jackc]

Thanks. My plan was to convert slice of sql.NamedArg to pgx.NamedArgs, any issues with this approach?

That is how you would implement this, but I am still doubtful that it is a good idea. As mentioned before, I'm reluctant to automagically rewrite the SQL query. But in addition, the value of supporting sql.NamedArg isn't clear to me. I would see it being valuable if it allowing writing code against database/sql that was usable on multiple databases. But when the MySQL and PostgreSQL drivers don't support it, it can't be used for cross-database code. So it would only be used when it was known that the underlying driver was pgx. And in that case, why not just use pgx.NamedArgs?

[tebeka]

I'm reluctant to automagically rewrite the SQL query

I thought that you do that when pgx.NamedArgs are passed anyway?

the value of supporting sql.NamedArg isn't clear to me

Mostly my OCD of using only stdlib :)

I'm closing this issue, thanks for explaining.

[jackc]

I'm reluctant to automagically rewrite the SQL query

I thought that you do that when pgx.NamedArgs are passed anyway?

I consider using pgx.NamedArgs to be explicit in rewriting the query because it implements the pgx.QueryRewriter interface. Whereas I would not expect sql.NamedArg to rewrite the query given that it is a feature natively supported by other databases without query rewriting.