How do i check if a ref cursor is pointing to an empty result set in postgres.
I have set the ref cursor variable to null, but when i run a function that returns a ref cursor , it runs forever when result set is empty. Is this a postgres bug?
Below is the code
function_1 returns a ref cursor
begin;
select * from function_1(11::bigint) as result;
fetch all from "test";
end;
create or replace function_1( In status_id bigint,OUT r_ref refcursor)
AS
Begin
r_ref:="test";
if select count(*) from table_1 > 1 then
open r_ref for select * from table_1;
else
r_ref:=null;
end if;
return;
end;
$$language plpgsql;
Let's start at the beginning. You declare a function, but do declare a return type; a function must declare a return type, further this is not defined as as an OUT parameter. Secondly, being a ref-cursor it cannot be set by an assignment statement. That makes the statement r_ref:="test"; an invalid statement, not to mention that the variable "test" is not defined (see documentation Identifiers and Keywords)
Finally, the structure if (select ...) is invalid. To get the count you need to provide a variable to hold it and then select into that variable:
There is no need for the status_id parameter, but assuming it is part of a large function I left it. However, take caution. This seems to be a table column name, if so you should probably not use it as a parameter name. There are numerous examples on SO as to why.