lib / pq

Pure Go Postgres driver for database/sql
https://pkg.go.dev/github.com/lib/pq
MIT License
9.04k stars 909 forks source link

how to fill param in function with output refcursor #1086

Open sonch3011 opened 2 years ago

sonch3011 commented 2 years ago

i have a fuction

create function find_all_by_user_id(OUT rc_out refcursor, p_user_id bigint) returns refcursor
    language plpgsql
as
$$
BEGIN
    open rc_out for
        select *
        from users u
        where u.id = p_user_id;
END;
$$;

My connect DB

func createDBConn() {
    config := GetDbYmlConfig()
    datasource := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
        config.Host, config.Port, config.Username, config.Password, config.Database,
    )
    Db, err = sql.Open(config.DriverName, datasource)
    fmt.Println(Db.Ping())
    Db.SetMaxOpenConns(config.MaximumPoolSize)
    Db.SetMaxIdleConns(config.MaximumIdle)
    if err != nil {
        panic(err.Error())
    }
}

but i don't know import param and call function

tx, err := db.Begin()
    if err != nil {
        return 0, errors.New(constant.Exception)
    }
    var cursor string
    a := tx.QueryRow("SELECT find_all_by_user_id(137)").Scan(&cursor)
    rows, _ := tx.Query("FETCH ALL " + pq.QuoteIdentifier(cursor))
    for rows.Next() {
        ...
    }

and cursor: "<unnamed portal 1>"

Thank you for your help