Hello,

try using SETOF instead of refcursor.

This Technote shows a sample: https://www.ibm.com/support/pages/node/1076721

Hope this helps, best regards

  CREATE TABLE test AS
SELECT
    ('{a,b,c,d,e,f}'::text[])[1 + FLOOR(random() * 6)] AS type_1,
    ('{m,n,o,p,q,r}'::text[])[1 + FLOOR(random() * 6)] AS type_2,
    random() * 1000 AS some_float
FROM generate_series(1,20000);

CREATE OR REPLACE FUNCTION test_cur_cognos() RETURNS setof test AS $$
 DECLARE
 ref refcursor; -- Declare a cursor variable
 BEGIN

 SELECT type_1, type_2, some_float FROM test; -- Open a cursor
 -- Return the cursor to the caller
 END;
$$ LANGUAGE plpgsql;

  CREATE TABLE test AS
SELECT
    ('{a,b,c,d,e,f}'::text[])[1 + FLOOR(random() * 6)] AS type_1,
    ('{m,n,o,p,q,r}'::text[])[1 + FLOOR(random() * 6)] AS type_2,
    random() * 1000 AS some_float
FROM generate_series(1,20000);

CREATE OR REPLACE FUNCTION test_cur_cognos() RETURNS setof test AS $$
 DECLARE
 ref refcursor; -- Declare a cursor variable
 BEGIN

 SELECT type_1, type_2, some_float FROM test; -- Open a cursor
 -- Return the cursor to the caller
 END;
$$ LANGUAGE plpgsql;

Due to the way the PostgreSQL implemented things, you need to follow the approach as shown in  https://www.ibm.com/support/pages/node/1076721

While PostgreSQL introduced CREATE PROCEDURE syntax, if you are using PostgreSQL ODBC (i.e. CQM), it will describe them as functions.

If you are using Dynamic Query, PostgreSQL JDBC will return metadata for the same objects when their implementation for DatabaseMetadata.getProcedures or getFunctions are called. In other words, you may observe the same object appearing twice.

If you are using CQM/PostgreSQL ODBC, contact support, about how to force functions to be treated as procedures in FM.

Historically,  Informix created the opposite challenge, it claimed all procedures were functions.

While FM UX has a gesture to turn a function into a procedure, there is no equivalent in FM for procedure to function.

Most vendors do not have these idioms.

  CREATE TABLE test AS
SELECT
    ('{a,b,c,d,e,f}'::text[])[1 + FLOOR(random() * 6)] AS type_1,
    ('{m,n,o,p,q,r}'::text[])[1 + FLOOR(random() * 6)] AS type_2,
    random() * 1000 AS some_float
FROM generate_series(1,20000);

CREATE OR REPLACE FUNCTION test_cur_cognos() RETURNS setof test AS $$
 DECLARE
 ref refcursor; -- Declare a cursor variable
 BEGIN

 SELECT type_1, type_2, some_float FROM test; -- Open a cursor
 -- Return the cursor to the caller
 END;
$$ LANGUAGE plpgsql;