PostgreSQL: function 返回结果集多列和单列的例子 · postgresql手册

今天有人问到在 PostgreSQL 函数中如何返回结果集的单列，返回结果集（多列）的方法很多，那么如何返回结果集的单列呢，做了下测试，具体步骤如下: **一测试一：返回多条记录(单列) --1 创建测试表并插入记录** <table style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="80%" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> skytf=> create table test_result1 (id integer,name varchar(32));<br style="LINE-HEIGHT: 28px"/>CREATE TABLE <p style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px 0px 10px; LINE-HEIGHT: 28px; PADDING-RIGHT: 0px">skytf=> create table test_result2 (id integer,name varchar(32));<br style="LINE-HEIGHT: 28px"/>CREATE TABLE</p> <p style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px 0px 10px; LINE-HEIGHT: 28px; PADDING-RIGHT: 0px">skytf=> insert into test_result1 select generate_series(1,10),'a';<br style="LINE-HEIGHT: 28px"/>INSERT 0 10</p> <p style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px 0px 10px; LINE-HEIGHT: 28px; PADDING-RIGHT: 0px">skytf=> insert into test_result2 select generate_series(1,10),'b';<br style="LINE-HEIGHT: 28px"/>INSERT 0 10</p> <p style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px 0px 10px; LINE-HEIGHT: 28px; PADDING-RIGHT: 0px">skytf=> select * From test_result1;<br style="LINE-HEIGHT: 28px"/> id | name<span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/>----+------<br style="LINE-HEIGHT: 28px"/> 1 | a<br style="LINE-HEIGHT: 28px"/> 2 | a<br style="LINE-HEIGHT: 28px"/> 3 | a<br style="LINE-HEIGHT: 28px"/> 4 | a<br style="LINE-HEIGHT: 28px"/> 5 | a<br style="LINE-HEIGHT: 28px"/> 6 | a<br style="LINE-HEIGHT: 28px"/> 7 | a<br style="LINE-HEIGHT: 28px"/> 8 | a<br style="LINE-HEIGHT: 28px"/> 9 | a<br style="LINE-HEIGHT: 28px"/> 10 | a<br style="LINE-HEIGHT: 28px"/>(10 rows)</p> <p style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px 0px 10px; LINE-HEIGHT: 28px; PADDING-RIGHT: 0px">skytf=> select * From test_result2;<br style="LINE-HEIGHT: 28px"/> id | name<span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/>----+------<br style="LINE-HEIGHT: 28px"/> 1 | b<br style="LINE-HEIGHT: 28px"/> 2 | b<br style="LINE-HEIGHT: 28px"/> 3 | b<br style="LINE-HEIGHT: 28px"/> 4 | b<br style="LINE-HEIGHT: 28px"/> 5 | b<br style="LINE-HEIGHT: 28px"/> 6 | b<br style="LINE-HEIGHT: 28px"/> 7 | b<br style="LINE-HEIGHT: 28px"/> 8 | b<br style="LINE-HEIGHT: 28px"/> 9 | b<br style="LINE-HEIGHT: 28px"/> 10 | b<br style="LINE-HEIGHT: 28px"/>(10 rows)</p></td></tr></tbody></table> **--2 方法一：返回多条记录（单列）** <table style="HEIGHT: 383px; WIDTH: 767px; WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="767" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> CREATE OR REPLACE FUNCTION skytf.func_test_result_single ( in_id integer)<br style="LINE-HEIGHT: 28px"/> <strong style="LINE-HEIGHT: 28px"><font style="LINE-HEIGHT: 28px" color="#ff0000">RETURNS SETOF varchar</font></strong><span class="Apple-converted-space"> </span>as<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>DECLARE<br style="LINE-HEIGHT: 28px"/> v_name varchar;<br style="LINE-HEIGHT: 28px"/>BEGIN<br style="LINE-HEIGHT: 28px"/> <span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/> for v_name in ( (select name from test_result1 where id = in_id) union (select name from test_result2 where id = in_id) )loop<br style="LINE-HEIGHT: 28px"/> RETURN NEXT v_name;<br style="LINE-HEIGHT: 28px"/> end loop;<br style="LINE-HEIGHT: 28px"/> return;<br style="LINE-HEIGHT: 28px"/>END;<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>LANGUAGE PLPGSQL;</td></tr></tbody></table> **--执行函数** <table style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="80%" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> skytf=> SELECT * FROM func_test_result_single(1) ;<br style="LINE-HEIGHT: 28px"/> func_test_result_single<span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/>-------------------------<br style="LINE-HEIGHT: 28px"/> b<br style="LINE-HEIGHT: 28px"/> a<br style="LINE-HEIGHT: 28px"/>(2 rows)</td></tr></tbody></table> **--3 方法二：使用 reutrn query 返回多条记录（单列）** <table style="HEIGHT: 353px; WIDTH: 768px; WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="768" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> CREATE OR REPLACE FUNCTION skytf.func_test_result_query_single ( in_id integer)<br style="LINE-HEIGHT: 28px"/> <strong style="LINE-HEIGHT: 28px"><font style="LINE-HEIGHT: 28px" color="#ff0000">RETURNS<span class="Apple-converted-space"> </span></font><font style="LINE-HEIGHT: 28px" color="#ff0000">SETOF varchar<span class="Apple-converted-space"> </span></font></strong>as<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>DECLARE<br style="LINE-HEIGHT: 28px"/> v_rec RECORD;<br style="LINE-HEIGHT: 28px"/>BEGIN<br style="LINE-HEIGHT: 28px"/> <span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/> return query ( (select name from test_result1 where id = in_id) union (select name from test_result2 where id = in_id) ); <p style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px 0px 10px; LINE-HEIGHT: 28px; PADDING-RIGHT: 0px"> return;<br style="LINE-HEIGHT: 28px"/>END;<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>LANGUAGE PLPGSQL;</p></td></tr></tbody></table> **--执行函数** <table style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="80%" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> skytf=> select func_test_result_query_single (1);<br style="LINE-HEIGHT: 28px"/> func_test_result_query_single<span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/>-------------------------------<br style="LINE-HEIGHT: 28px"/> b<br style="LINE-HEIGHT: 28px"/> a<br style="LINE-HEIGHT: 28px"/>(2 rows)</td></tr></tbody></table> 备注：在返回指定 SETOF varchar 返回 varchar 类型单个字段，接下来介绍下返回多条记录多列的场景。 **二测试二：返回多条记录（多列） --2.1 使用游标和"RETURNS SETOF RECORD" 返回多条记录（多列）** <table style="HEIGHT: 383px; WIDTH: 761px; WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="761" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> CREATE OR REPLACE FUNCTION skytf.func_test_result_muti ( in_id integer)<br style="LINE-HEIGHT: 28px"/> <strong style="LINE-HEIGHT: 28px"><font style="LINE-HEIGHT: 28px" color="#ff0000">RETURNS SETOF RECORD<span class="Apple-converted-space"> </span></font></strong>as<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>DECLARE<br style="LINE-HEIGHT: 28px"/> v_rec RECORD;<br style="LINE-HEIGHT: 28px"/>BEGIN<br style="LINE-HEIGHT: 28px"/> <span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/> for v_rec in ( (select id , name from test_result1 where id = in_id) union (select id , name from test_result2 where id = in_id) )loop<br style="LINE-HEIGHT: 28px"/> RETURN NEXT v_rec;<br style="LINE-HEIGHT: 28px"/> end loop;<br style="LINE-HEIGHT: 28px"/> return;<br style="LINE-HEIGHT: 28px"/>END;<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>LANGUAGE PLPGSQL;</td></tr></tbody></table> **--执行函数** <table style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="80%" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> skytf=> SELECT * FROM func_test_result_muti(1) t(id integer,name varchar);<br style="LINE-HEIGHT: 28px"/> id | name<span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/>----+------<br style="LINE-HEIGHT: 28px"/> 1 | a<br style="LINE-HEIGHT: 28px"/> 1 | b<br style="LINE-HEIGHT: 28px"/>(2 rows) </td></tr></tbody></table> **--2.2 使用 reutrn query 返回多条记录（多列）** <table style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="80%" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> CREATE OR REPLACE FUNCTION skytf.func_test_result_query ( in_id integer)<br style="LINE-HEIGHT: 28px"/> <strong style="LINE-HEIGHT: 28px"><font style="LINE-HEIGHT: 28px" color="#ff0000">RETURNS SETOF RECORD</font></strong><span class="Apple-converted-space"> </span>as<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>DECLARE<br style="LINE-HEIGHT: 28px"/> v_rec RECORD;<br style="LINE-HEIGHT: 28px"/>BEGIN<br style="LINE-HEIGHT: 28px"/> <span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/> <span class="Apple-converted-space"> </span><strong style="LINE-HEIGHT: 28px"><font style="LINE-HEIGHT: 28px" color="#ff0000">return query</font></strong> ( (select id , name from test_result1 where id = in_id) union (select id , name from test_result2 where id = in_id) ); <p style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px 0px 10px; LINE-HEIGHT: 28px; PADDING-RIGHT: 0px"> return;<br style="LINE-HEIGHT: 28px"/>END;<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>LANGUAGE PLPGSQL;</p></td></tr></tbody></table> **--执行函数** <table style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="80%" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> skytf=> SELECT * FROM func_test_result_query(1) t(id integer,name varchar);<br style="LINE-HEIGHT: 28px"/> id | name<span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/>----+------<br style="LINE-HEIGHT: 28px"/> 1 | a<br style="LINE-HEIGHT: 28px"/> 1 | b<br style="LINE-HEIGHT: 28px"/>(2 rows)</td></tr></tbody></table> **--2.3 使用 out 输出参数返回多条记录（多列）** <table style="HEIGHT: 458px; WIDTH: 818px; WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="818" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> CREATE OR REPLACE FUNCTION skytf.func_test_result_out ( in_id integer,out o_id integer,out o_name varchar)<br style="LINE-HEIGHT: 28px"/> RETURNS SETOF RECORD as<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>DECLARE<br style="LINE-HEIGHT: 28px"/> v_rec RECORD;<br style="LINE-HEIGHT: 28px"/>BEGIN<br style="LINE-HEIGHT: 28px"/> <span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/> for v_rec in ( (select id , name from test_result1 where id = in_id) union (select id , name from test_result2 where id = in_id) )loop<br style="LINE-HEIGHT: 28px"/> o_id := v_rec.id;<br style="LINE-HEIGHT: 28px"/> o_name := v_rec.name;<br style="LINE-HEIGHT: 28px"/> RETURN NEXT ;<br style="LINE-HEIGHT: 28px"/> end loop;<br style="LINE-HEIGHT: 28px"/> return;<br style="LINE-HEIGHT: 28px"/>END;<br style="LINE-HEIGHT: 28px"/>$$<br style="LINE-HEIGHT: 28px"/>LANGUAGE PLPGSQL;</td></tr></tbody></table> **--执行函数** <table style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 16px/28px 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53; WIDOWS: 1; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(220,231,230); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" cellspacing="1" cellpadding="1" width="80%" border="1"><tbody style="LINE-HEIGHT: 28px"><tr style="LINE-HEIGHT: 28px"><td style="LINE-HEIGHT: 28px"> skytf=> select skytf.func_test_result_out(1);<br style="LINE-HEIGHT: 28px"/> func_test_result_out<span class="Apple-converted-space"> </span><br style="LINE-HEIGHT: 28px"/>----------------------<br style="LINE-HEIGHT: 28px"/> (1,a)<br style="LINE-HEIGHT: 28px"/> (1,b)<br style="LINE-HEIGHT: 28px"/>(2 rows)</td></tr></tbody></table> **三总结：** 以上只是为了演示 PostgreSQL 函数的语法给出简单的例子，生产过程中的 function 会复杂很多。