如何在没有refcursor的情况下返回postgres中的多表？ [英] How to return multi tables in postgres without refcursor?

问题描述

我在sql server中有一个这样的过程:::::

  CREATE   PROC  SP_FetchData（）
  as  
 开始 
 选择 FirstName，LastName 来自 TBl_Users; 
 选择 TaskId，CreatedBy 来自 TBl_Log; 
  END ; 

我想在postgres函数中执行相同的查询而不使用refcursor。我怎么能这样做？



我尝试过：

  CREATE   FUNCTION  SP_FetchData（） RETURNS  setof refcursor  AS  
 $$ 
  DECLARE  c1 refcursor ; 
  DECLARE  c2 refcursor; 
  BEGIN  
  OPEN  c1  FOR  
 选择 FirstName，LastName 来自 TBl_Users; 
  RETURN  NEXT c1; 
 
  OPEN  c2  FOR  
 选择 TaskId，CreatedBy 来自 TBl_Log; 
  RETURN  NEXT c2; 
  END ; 
 $$  LANGUAGE  '  plpgsql'; 

- 它工作正常但我不想使用refcursor。我该怎么办？

解决方案

DECLARE c1 refcursor;
DECLARE c2 refcursor;
BEGIN
OPEN c1 FOR
选择 FirstName，LastName 来自 TBl_Users;
RETURN NEXT c1;

OPEN c2 FOR
选择 TaskId，CreatedBy 来自 TBl_Log;
RETURN NEXT c2;
END ;

LANGUAGE ' plpgsql';

- 它工作正常，但我不想使用refcursor。我怎么能这样做？

据我所知，PostGres不支持多个结果集，没有像SQL Server那样的游标。



基本上你可以加入结果集，例如使用 UNION ALL 操作，但这需要结构和两个结果集的数据类型是相同的。基于你的问题，情况并非如此。



因此，如果UNION不是一个可行的选项，你可能需要使用ref游标或将查询分成两个不同的程序

I have one procedure in sql server like this:::::

CREATE PROC SP_FetchData()
as
begin
select FirstName, LastName from TBl_Users;
select TaskId,CreatedBy from TBl_Log;
END;

I want execute the same query in postgres function without using refcursor. how can I do it?

What I have tried:

CREATE FUNCTION SP_FetchData() RETURNS setof refcursor AS
$$
DECLARE c1 refcursor;
DECLARE c2 refcursor;
BEGIN
    OPEN c1 FOR
    select FirstName, LastName from TBl_Users;
    RETURN NEXT c1;

    OPEN c2 FOR
    select TaskId,CreatedBy from TBl_Log;
    RETURN NEXT c2;
END;
$$ LANGUAGE 'plpgsql';

-- It is working properly but I don't want to use refcursor. How can I do it?

解决方案

DECLARE c1 refcursor; DECLARE c2 refcursor; BEGIN OPEN c1 FOR select FirstName, LastName from TBl_Users; RETURN NEXT c1; OPEN c2 FOR select TaskId,CreatedBy from TBl_Log; RETURN NEXT c2; END;

LANGUAGE 'plpgsql';

-- It is working properly but I don't want to use refcursor. How can I do it?

As far as I know PostGres does not support multiple result sets without cursors like SQL Server does.

Basically you could join the result sets for example using UNION ALL operation but that would require that the structure and the data types of both results sets are the same. Based on your question, this is not the case.

So if UNION is not a feasible option you probably need to use ref cursors or separate the queries to two different procedures.

这篇关于如何在没有refcursor的情况下返回postgres中的多表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！