PostgreSQL 如何方便的查看游标结果做验证

--1.创建模拟数据

DROP TABLE test;
CREATE TABLE test(id int, info text);
INSERT INTO test SELECT generate_series(1, 10), 'test';

　　

 

--2.创建存储过程

CREATE OR REPLACE  FUNCTION test_1() RETURNS refcursor AS
$BODY$
DECLARE
	CURSOR c1 FOR SELECT * FROM test;
BEGIN
	OPEN c1;
	RETURN c1;
END
$BODY$
LANGUAGE plsql;

　　

 

 

--3.在命令行中可以查询：

TEST=# BEGIN;
BEGIN
TEST=# SELECT test_1();
 TEST_1
--------
 C1
(1 row)


TEST=# FETCH 5 IN "C1";
 ID | INFO
----+------
  1 | test
  2 | test
  3 | test
  4 | test
  5 | test
(5 rows)


TEST=# END;
COMMIT

　　

 

--4.管理工具需要在匿名块中执行：

 

DO 
$$
DECLARE 
 	ret_ref refcursor;
 	one_row record;
BEGIN
	OPEN ret_ref FOR SELECT 1 ;
	ret_ref := test_1();
	
	FETCH ret_ref INTO one_row;
	WHILE ret_ref%FOUND LOOP
		raise notice 'id is: %, text is: %', one_row.id, one_row.info;
		FETCH NEXT IN ret_ref INTO one_row;
	END LOOP;
	
	CLOSE ret_ref;
END 
$$

结果：

00000: id is: 1, text is: test
00000: id is: 2, text is: test
00000: id is: 3, text is: test
00000: id is: 4, text is: test
00000: id is: 5, text is: test
00000: id is: 6, text is: test
00000: id is: 7, text is: test
00000: id is: 8, text is: test
00000: id is: 9, text is: test
00000: id is: 10, text is: test

　　

 

--注意：如果在存储过程中，已经消费了，则返回的游标需要重新定位到开始

 

 

--5.处理out参数的游标，一样的：

CREATE OR REPLACE  FUNCTION test_2(v_id int, c1 OUT refcursor) RETURNS refcursor AS
$BODY$
DECLARE
BEGIN
	OPEN c1 FOR SELECT * FROM test WHERE id < v_id;
END
$BODY$
LANGUAGE plsql;

DO 
$$
DECLARE 
 	ret_ref refcursor;
 	one_row record;
BEGIN
	OPEN ret_ref FOR SELECT 1 ;
	ret_ref := test_2(6);
	
	FETCH ret_ref INTO one_row;
	WHILE ret_ref%FOUND LOOP
		raise notice 'id is: %, text is: %', one_row.id, one_row.info;
		FETCH NEXT IN ret_ref INTO one_row;
	END LOOP;
	
	CLOSE ret_ref;
END 
$$

　　

结果：

00000: id is: 1, text is: test
00000: id is: 2, text is: test
00000: id is: 3, text is: test
00000: id is: 4, text is: test
00000: id is: 5, text is: test