How do I check if a ref cursor points to empty result set in postgres

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;

Solution

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)

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier ...

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:

create or replace function_1( In status_id bigint)
   return refcursor
 language plpgsql 
as $$
declare 
    v_cnt integer;
    r_ref refcursor;
begin
    select count(*)
      into v_cnt 
      from table_1; 

    if v_cnt > 0 then 
       open r_ref for 
            select * from table_1;
    else
       open r_ref for 
            select * from table_1 where 0=1; -- this returns no rows but gives in the appropriate suructure
    end if;

    return r_ref;   
end;
$$;

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.