I'd like a function to return a strongly-typed refcursor. My goal is to
allow callers of the function to know, based on the function's return
type, the number and data types of the columns that it can expect in the
refcursor. From what I see in plpgsql, all refcursors are allowed to
point to any query at all.
In particular, my caller's environment will be Sybase PowerBuilder, and
the function will be tied to a PowerBuilder datawindow. In the past
these datawindow clients have used Oracle ref cursors, but we've
preferred that these Oracle ref cursors be strongly typed. Oracle
defines a strongly-typed ref cursor as one that includes the 'return'
clause in the ref cursor definition. For example, in Oracle:
----------beginning of code------------------
create or replace package x_pkg is
type x_rec is record (col1 number);
type x_type is ref cursor return x_rec;
end;
/
create or replace function x_func return x_pkg.x_type
as
cur x_pkg.x_type;
begin
open cur for select 1 from dual;
return cur;
end;
/
----------end of code------------------
This code will create a function 'x_func' that must return a ref cursor
whose query contains a single numeric column. If I 'describe' the
function I will see that the function returns a record that contains a
single numeric column.
How can I write a plpgsql function that behaves similarly?