plpgsql - 如何从存储过程中返回参数和引用游标？

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"

我想在我的应用程序中实现分页。我创建了存储过程,返回记录数作为输出参数和ref游标 - 数据本身(带限制和偏移)但结果 - 我得到 - “函数结果类型必须是bigint因为OUT参数”

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

据我所知 - 它抱怨“_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

1 个解决方案

#1

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.

使用一个OUT变量时,结果类型必须与OUT变量的类型相同。如果要返回多个值,则必须使用更多OUT变量,结果类型应为RECORD或RETURNING部分可以忽略。

Modern PostgreSQL doesn't allow what you did:

现代PostgreSQL不允许你做的事情:

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

注意:使用OFFSET LIMIT进行分页并不是最好的选择 - 请参阅http://use-the-index-luke.com/sql/partial-results/fetch-next-page

#1