Postgres - call a function that returns a refcursor

(Pete Kane) #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

(Stefan Mühlebach) #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 ()”.

(Pete Kane) #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

(Pete Kane) #4

This is the working code

package main

import (
	_ ""

const (
	host     = ""
	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 {

	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 {

	defer rows.Close()

	var companyname string

	for rows.Next() {


(Pete Kane) #5

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

(system) #6

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