CODEWITHSUNDEEP
sqldatabasepostgresqlgo
Yakkodash
Yakkodash

Reputation: 53

Executing SQL query with variable number of named parameters in Golang

So I have this PostgreSQL function, which takes variable number of named arguments and returns list of corresponding items:

CREATE OR REPLACE FUNCTION read_user(
  _id BIGINT DEFAULT NULL,
  _phone VARCHAR(30) DEFAULT NULL,
  _type VARCHAR(15) DEFAULT NULL,
  _last VARCHAR(50) DEFAULT NULL,
  _first VARCHAR(50) DEFAULT NULL
) 
RETURNS setof T_USERS
AS $$ 
BEGIN
  RETURN QUERY
  SELECT * FROM T_USERS
  WHERE ( id = _id OR _id IS NULL )
    AND ( phone = _phone OR _phone IS NULL )
    AND ( type = _type OR _type IS NULL )
    AND ( last = _last OR _last IS NULL )
    AND ( first = _first OR _first IS NULL );
    EXCEPTION WHEN others THEN
      RAISE WARNING 'Transaction failed and was rolled back';
      RAISE NOTICE '% %', SQLERRM, SQLSTATE;
END
$$ LANGUAGE plpgsql;

So I can run polymorphic queries like these:

SELECT read_user(_id := 2);
SELECT read_user(_first := 'John', _last := 'Doe');

In Golang I can make something like:

stmt, err := db.Prepare("SELECT read_user(_id = ?)")

But how can I do the same, but with variable amount of read_user arguments? I'm using pq driver https://github.com/lib/pq.

Upvotes: 5

Views: 10197

Answers (1)

mkopriva
mkopriva

Reputation: 38233

You can construct your one statement by enumerating all the parameters with their placeholders and then you could pass nil explicitly where you don't have the parameter value.

stmt, err := db.Prepare("SELECT read_user(_id := $1, _phone := $2, _type := $3, _last := $4, _first := $5)")
if err != nil {
    // ...
}
stmt.Query(2, nil, nil, nil, nil) // result should be equivalent to `SELECT read_user(_id := 2)`
stmt.Query(nil, nil, nil, "Doe", "John") // result should be equivalent to `SELECT read_user(_first := 'John', _last := 'Doe')`

And if you want to have named parameters in Go as well, you can create a struct type to represent the parameters and a wrapper func that'll map that parameter type's fields into the query:

type readUserParams struct {
    Id    interface{}
    Phone interface{}
    Type  interface{}
    Last  interface{}
    First interface{}
}

func readUser(p *readUserParams) {
    stmt.Query(p.Id, p.Phone, p.Type, p.Last, p.First)
    // ...
}

readUser(&readUserParams{Id: 2})
readUser(&readUserParams{First: "John", Last:"Doe"})

Upvotes: 4

Related Questions