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
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