CODEWITHSUNDEEP
postgresqlref-cursor
rshiny
rshiny

Reputation: 1

Is it possible to use OUT/INOUT parameter along with return refcursor in POSTGRESQL?

Here is an example snippet. I am trying to have a INOUT parameter and also trying to return a refcursor.

CREATE OR REPLACE FUNCTION reffunc2(IN key int, INOUT name int) 
RETURNS refcursor 
AS $$
DECLARE
ref refcursor;
BEGIN
name = 123;
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
$$ 
LANGUAGE plpgsql;

Upvotes: 0

Views: 988

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247665

Specifying an OUT or INOUT parameter is the same as specifying a return value, so the two definitions have to match:

  • with a single OUT parameter, RETURNS must specify the type of that parameter

  • with more than one OUT parameter, you must use RETURNS record

You cannot specify one result column as OUT parameter and the other one as result.

Use this:

CREATE FUNCTION reffunc2(
   IN key int,
   INOUT name int,
   OUT x refcursor
) RETURNS record

In the function body, you must assign values to name and x and use RETURN without argument.

Upvotes: 0

Related Questions