Postgres - call a function that returns a refcursor

Getting Help
1

Hi all, does anyone here know how to call a Postgresql function that returns a refcursor ? I’ve succeeded in querying views and tables but can’t work out how to call a function

2

In order to use ‘refcursors’ in PostgreSQL, you must be in a transaction. So use:

tx := db.Begin ()
...
row := tx.QueryRow ("SELECT RefCursFunc ('mycursor')")
err := row.Scan (&cursorName)
...
rows, err := tx.Query ("FETCH ALL IN mycursor")
...
for rows.Next () {
    ...
}
tx.Commit ()

Although we know the name of the cursor already, it’s important to read the result of the “SELECT”-query with “row.Scan ()”. Ordinary functions (like “has_table_privilege ()”) can be called like a simple “SELECT”-statement using “db.QueryRow ()”.

3

Hi Stefan , thanks for your time , what type do I need to declare cursorName as ? I tried to run your code and got a memory violation error and I’m pretty sure it’s the row.Scan(&cursorName) call that’s. causing it, I’m doing all this on a Linux command line so I don’t. have access to a debugger

4

This is the working code


package main

import (
	"database/sql"
	"fmt"
	"log"
	_ "github.com/lib/pq"
)

const (
	host     = "10.10.1.11"
	port     = 5432
	user     = "postgres"
	password = "Giraffes09"
	dbname   = "PJKSolutions"
)

func main() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)

	db, err := sql.Open("postgres", psqlInfo)

	var cursorName string

	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()

	tx, err := db.Begin()
	row := tx.QueryRow("SELECT public.spgetallclients('mycursor');")
	err = row.Scan(&cursorName)
	rows, err := tx.Query("FETCH ALL from mycursor;")
	

	if err != nil {
		log.Fatal(err)
	}

	defer rows.Close()

	
	var companyname string

	for rows.Next() {
			 rows.Scan(&companyname)
			 fmt.Println(companyname)
	}

	tx.Commit()
}
5

Finally worked it out, you have to end each statement with a ; thanks for your help

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.