Running a PostgreSQL Stored Procedure or Function

PostgreSQL procedures behave quite differently to procedures in other types of databases. When the procedure has INOUT or OUT parameters, these values are returned in a result-set, rather than returning the values in the parameter.

In the following example, this procedure has two OUT parameters. The procedure returns a result-set consisting of 1 row and 2 columns. AQT will read the grid and populate the Values column in the parameters grid.

Procedure or Function returning a result-set (refcursor)

A routine can return a set of data by having an OUT parameter of type refcursor (for a Procedure) or a return value of type refcursor (for a Function) .

When you run the routine, instead of getting the data, you get the name of the refcursor (<unnamed portal 13> in this example).

The data for can be retrieved by reading cursor, with the following:

run the following: fetch all from "<unnamed portal 13>"
you must run both the procedure / function call and the fetch within the same transaction.

Once the transaction has finished, the cursor is deleted; any attempt to read the cursor will give:

ERROR: cursor "<unnamed portal 13>" does not exist

The method normally recommended for running a Procedure / Function and returning the results, is to use the following:

However, this method is problematic as, until you have done the call, you don't know the name of the cursor for the fetch statement.

Specifying the cursor name

You can get around the problem with the cursor name by specifying the name to be used for the refcursor. To do this, you will need to amend the definition of the routine as follows:

In the case of a Procedure, specify the refcursor with a mode of INOUT rather than OUT. Example:
Procedure test.proc_getemp(IN dept character varying, INOUT outdata refcursor)
In the case of a Function, include a refcursor as a parameter to the function. Example:
Function test.func_getemp(dep character varying, outdata refcursor) RETURNS SETOF refcursor

When you call the routine, you can now give the name for the cursor.

select test.func_getemp('A00','cursor01')

The result is:

Because the cursor now has a fixed name, you can run the routine and display the results with:

Using AQT

AQT will deal with the above complexities. You can run PostgreSQL procedures and functions with AQT and it will display the data returned by refcursors without any extra steps required by yourself.

Advanced Query Tool

https://www.querytool.com