Add the possibility to chose a custom prefix for named parameters

[skoot]

We use postgres and we have a bunch of queries in which we use the ::TEXT or ::INT syntax. Escaping them isn't convenient, and we just wouldn't have to if we were able to chose another character for the named params (like "@").

Letting users chose their own character prefix for named variable would be neat.

It could very easily be done on the package level by using a global variable, or it could be set on the connection level and then passed all the way down to the compileNamedQuery function.

What do you think?

[jhngrant]

@skoot If you're using the pq driver ( github.com/lib/pq ), it uses the Postgres-native ordinal markers:

rows, err := db.Query(`SELECT name FROM users WHERE favorite_fruit = $1
	OR age BETWEEN $2 AND $2 + 3`, "orange", 64)

err := db.QueryRow(`SELECT $1::uuid`, s).Scan(&scanned)

[skoot]

I'm using pgx and I use sqlx precisely to avoid ordinal markers and use named variables instead.

[jhngrant]

@skoot My apologies. I misunderstood your original issue.

I'd agree, the historical Postgres type cast syntax is keyboard friendly when typing SQL statements:

SELECT 1234::TEXT

However, to keep the sqlx library lean there may be a counter argument of switching to standard SQL for production code:

SELECT CAST(1234 AS TEXT)

This would of course avoid the parsing issue you are seeing with prepared statements when using the :: type cast syntax.

https://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

@skoot can you please share how are you bridging sqlx with pgx. I am trying to find a working example where I could use two features from sqlx with pgx namely - struct scan and named parameters.

[skoot]

Sure, it's an easy 2 step process: You start by getting a database/sql object from a pgx connection or pool, as explained here:

https://godoc.org/github.com/jackc/pgx/stdlib

Then you wrap it up in sqlx as you would any other database/sql object.