测试使用和返回refcursor的PostgreSQL函数 [英] Testing PostgreSQL functions that consume and return refcursor

问题描述

我想测试一个Postgres函数的结果（改变函数是不可能的）。

函数接收REFCURSOR和其他几个参数作为参数，并返回相同的RECURSOR。

  get_function_that_returns_cursor（ret，4100，'SOMETHING'，123465）
  

现在我想在Postgres中创建一个小测试，以获取此FUNCTION的结果。
类似下面的代码（这是我的方法，但它不工作）：

  DO $$ DECLARE 
 ret REFCURSOR; 
 row_to_read table_it_will_return％ROWTYPE; 
 BEGIN 
 PERFORM get_function_that_returns_cursor（ret，4100，'SOMETHING'，123465）; 
  -  OR SELECT get_function_that_returns_cursor（ret，4100，'SOMETHING'，123465）INTO ret 
 for row_to_read IN SELECT * FROM ret LOOP 
  - （...）
 RAISE NOTICE'Row read ...'; 
 END LOOP; 
 CLOSE ret; 
 END $$; 
  

有关如何让此工作的任何建议吗？一个通用的解决方案，可以用于测试这种类型的函数（获取一个Cursor并返回Cursor？

如果我们不知道rowtype是

在PostgresQL中调试此类东西的最佳方法是什么？ >解决方案

Q1

您的小测试可以是纯SQL：

  BEGIN; 
 SELECT get_function_that_returns_cursor（'ret'，4100，'foo'，123）;注意：'ret'
 FETCH ALL IN ret;适用于任何行类型
 
 COMMIT;  - 或ROLLBACK; 
  

code> COMMIT 之后，大多数客户端只显示lat的结果命令。

在 返回光标 。

Q2

如果我们不知道正在返回的rowtype，我们该怎么做呢？

只想检查结果，您可以将整个记录投射到 text 。
这种方式避免了函数的动态返回类型的问题。

考虑这个演示：

  CREATE TABLE a（a_id int PRIMARY KEY，a text）; 
 INSERT INTO VALUES（1，'foo'），（2，'bar'）; 
 
 CREATE OR REPLACE FUNCTION reffunc（INOUT ret refcursor）AS  -  INOUT param :) 
 $ func $ 
 BEGIN 
 OPEN ret FOR SELECT * FROM a; 
 END 
 $ func $ LANGUAGE plpgsql; 
 
 
 CREATE OR REPLACE FUNCTION ctest（）
 RETURNS SETOF text AS 
 $ func $ 
 DECLARE 
 curs1 refcursor; 
 rec record; 
 BEGIN 
 curs1：= reffunc（'ret'）; - 简单赋值
 
 LOOP 
 FETCH curs1 INTO rec; 
当未找到时退出; - 注意放置！ 
 RETURN NEXT rec :: text; 
 END LOOP; 
 END 
 $ func $ LANGUAGE plpgsql; 
  

- > SQLfiddle

I want to test results of a Postgres function (changing the function is not a possibility).

The function receives as arguments a REFCURSOR and several other things and returns the same RECURSOR.

get_function_that_returns_cursor(ret, 4100, 'SOMETHING', 123465)

Now I want to create a small test in Postgres to get the results of this FUNCTION. Something Like the code below (this is my approach but it is not working):

DO $$ DECLARE
 ret REFCURSOR; 
 row_to_read table_it_will_return%ROWTYPE ;
BEGIN
 PERFORM get_function_that_returns_cursor(ret, 4100, 'SOMETHING', 123465);
-- OR SELECT get_function_that_returns_cursor(ret, 4100, 'SOMETHING', 123465) INTO ret
 FOR row_to_read IN SELECT * FROM ret LOOP
   -- (...)
   RAISE NOTICE 'Row read...';
 END LOOP;
 CLOSE ret;
END $$;

Any suggestion on how to get this to work? A generic solution that can be used for testing this type of functions (that get a Cursor and return a Cursor?

And if we don't know the rowtype that is being returned how could we do it?

What is the best way to debug this kind of things in PostgresQL

解决方案

Q1

Your "small test" can be plain SQL:

BEGIN;
SELECT get_function_that_returns_cursor('ret', 4100, 'foo', 123); -- note: 'ret'
FETCH ALL IN ret; -- works for any rowtype

COMMIT;  -- or ROLLBACK;

Execute COMMIT / ROLLBACK after you inspected the results. Most clients only display the result of the lat command.

More in the chapter Returning Cursors of the manual.

Q2

And if we don't know the rowtype that is being returned how could we do it?

Since you only want to inspect the results, you could cast the whole record to text. This way you avoid the problem with dynamic return types for the function altogether.

Consider this demo:

CREATE TABLE a (a_id int PRIMARY KEY, a text);
INSERT INTO a VALUES (1, 'foo'), (2, 'bar');

CREATE OR REPLACE FUNCTION reffunc(INOUT ret refcursor) AS  -- INOUT param :)
$func$
BEGIN
    OPEN ret FOR SELECT * FROM a;
END
$func$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION ctest()
  RETURNS SETOF text AS
$func$
DECLARE
    curs1 refcursor;
    rec   record;
BEGIN
  curs1 := reffunc('ret');   -- simple assignment

  LOOP
    FETCH curs1 INTO rec;
    EXIT WHEN NOT FOUND;     -- note the placement!
    RETURN NEXT rec::text;
  END LOOP;
END
$func$ LANGUAGE plpgsql;

-> SQLfiddle

这篇关于测试使用和返回refcursor的PostgreSQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！