plpgsql - How to return out parameter and ref cursor from stored procedure?

Question

I would like to implement paging in my application. I created stored procedure that returns number of records as output parameter and ref cursor- data itself (with limits and offsets) But as result-I'm getting -" function result type must be bigint because of OUT parameters"

As far as I understand- it complains for out "_count" bigint.

Is there any case to return out parameter and ref cursor from same stored procedure?

  CREATE OR REPLACE FUNCTION aggr."GetPromotionsFull"("_limit" bigint, "_offset" bigint, out "_count" bigint)
  RETURNS refcursor AS   
$BODY$
DECLARE
      ref refcursor;    
BEGIN   

select count(1) into "_count" from aggr."Promotion" t 
inner join aggr."Company" c on t."CompanyId"=c."Id"  
   where 
   t."isDeleted"=false
  and c."isDeleted"=false;


  OPEN ref FOR
  SELECT t."Id",
  t."CompanyId",
  t."PromoName",
  t."Description",
  t."Url",
  t."ImgPath",
  t."CreatedDate",
  t."IsEnabled",
  t."isDeleted",
  c."Name"as "CompanyName"
  FROM aggr."Promotion" t
  inner join aggr."Company" c on t."CompanyId"=c."Id"  
   where 
   t."isDeleted"=false
   and c."isDeleted"=false
   limit "_limit" offset "_offset";

    RETURN ref;     
END
$BODY$
  LANGUAGE plpgsql VOLATILE

score 3 · Accepted Answer · answered May 11 '15 at 14:51

When you use one OUT variable, then result type have to be same type as OUT variable. When you want to return more than one values, you have to use more OUT variables and result type should be RECORD or RETURNING part can be ignored.

Modern PostgreSQL doesn't allow what you did:

postgres=# CREATE OR REPLACE FUNCTION fx(OUT a int) 
RETURNS numeric AS $$ select 10$$ 
LANGUAGE sql;
ERROR:  function result type must be integer because of OUT parameters

but you should to do:

postgres=# CREATE OR REPLACE FUNCTION fx(OUT a int, OUT b numeric)
AS $$ select 10, 20.1$$ 
LANGUAGE sql;
CREATE FUNCTION

Note: pagination with OFFSET LIMIT is not best idea - see http://use-the-index-luke.com/sql/partial-results/fetch-next-page

plpgsql - How to return out parameter and ref cursor from stored procedure?

1 Answers1