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 會復雜很多。