Replace the SQL literals with placeholders "?"

[dineshtessell]

I am looking for a way, how we can achieve all the SQLValues to be replaced with the placeholders like "?". Is there any example for it.

For example, SELECT name, email FROM users, xyz WHERE name = 'John Doe' AND age > 30

Should be changed to SELECT name, email FROM users, xyz WHERE name = ? AND age > ?

We are able to acehive this MySQL package as below, and for PostgreSQL we are looking.

MySQL

package main

import (
    "fmt"
    "log"

    "github.com/xwb1989/sqlparser"
)

func main() {
    // Example SQL statement
    sqlText := "SELECT name, email FROM users, xyz WHERE name = 'John Doe' AND age > 30;"

    stmt, err := sqlparser.Parse(sqlText)
    if err != nil {
        log.Fatal(err)
    }

    // Define a visitor function
    visitor := func(node sqlparser.SQLNode) (kontinue bool, err error) {
        switch node := node.(type) {
        case *sqlparser.SQLVal:
            fmt.Printf("Found literal: %s\n", string(node.Val))
            *node = sqlparser.SQLVal{
                Type: sqlparser.ValArg,
                Val:  []byte("?"),
            }
        }
        return true, nil
    }

    // Traverse the AST using the visitor function
    err = sqlparser.Walk(visitor, stmt)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println(sqlparser.String(stmt))
}

ubuntu@foo:~/sql_parser$ go run main.go Found literal: John Doe Found literal: 30 select name, email from users, xyz where name = ? and age > ?

[lfittl]

@dineshtessell Thanks for reaching out! Have you looked at the Normalize function? (https://pkg.go.dev/github.com/pganalyze/pg_query_go/v4#Normalize)

That replaces literal values with parameter references ($1, etc). The motivation for using parameter references like $1 and not ? is for the query to remain parseable by the parser (? is an operator in Postgres, and thus introduces ambiguity into the query).

(side note: I just realized that the code comment for that function is out of date - we used to use ? a long time ago, but no longer do)

[dineshtessell]

Thank you so much @lfittl. This truly helps

[dineshtessell]

For others reference,

package main

import ( "fmt"

pg_query "github.com/pganalyze/pg_query_go/v4"

)

func main() { tree, err := pg_query.Normalize(SELECT name, email FROM "users", xyz WHERE name = 'John Doe' AND age > 30;) if err != nil { panic(err) } fmt.Printf("%s\n", tree) }

Output SELECT name, email FROM "users", xyz WHERE name = $1 AND age > $2;