PostgreSQL function返回多行的操作
1. 建表
postgres=# create table tb1(id integer,name character varying); CREATE TABLE postgres=# postgres=# insert into tb1 select generate_series(1,5),'aa'; INSERT 0 5
2. 返回單字段的多行(returns setof datatype)
不指定out參數(shù),使用return next xx:
create or replace function func01()returns setof character varying as $$ declare n character varying; begin for i in 1..5 loop select name into n from tb1 where id=i; return next n; end loop; end $$ language plpgsql;
指定out參數(shù),使用return next:
create or replace function func02(out character varying)returns setof character varying as $$ begin for i in 1..5 loop select name into $1from tb1 where id=i; return next; end loop; end $$ language plpgsql;
使用return query:
create or replace function func03()returns setof character varying as $$ begin for i in 1..5 loop return query(select name from tb1 where id=i); end loop; end $$language plpgsql;
3. 返回多列的多行(returns setog record)
不指定out參數(shù),使用return next xx:
create or replace function func04()RETURNS SETOF RECORD as $$ declare r record; begin for i in 1..5 loop select * into r from tb1 where id=i; return next r; end loop; end; $$language plpgsql;
在使用func04的時(shí)候注意,碰到問(wèn)題列下:
問(wèn)題一:
postgres=# select func04(); ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function func04() line 7 at RETURN NEXT
解決:
If you call your set-returning function the wrong way (IOW the way you might normally call a function), you will get this error message: Set-valued function called in context that cannot accept a set. Incorrect: select sr_func(arg1, arg2, …); Correct: select * from sr_func(arg1, arg2, …);
問(wèn)題二:
postgres=# select * from func04(); ERROR: a column definition list is required for functions returning "record" LINE 1: select * from func04();
解決:
postgres=# select * from func04() as t(id integer,name character varying); id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa (5 rows)
這個(gè)問(wèn)題在func04如果指定out參數(shù)就不會(huì)有問(wèn)題,如下func05所示:
指定out參數(shù),使用return next:
create or replace function func05(out out_id integer,out out_name character varying)returns setof record as $$ declare r record; begin for i in 1..5 loop select * into r from tb1 where id=i; out_id:=r.id; out_name:=r.name; return next; end loop; end; $$language plpgsql;
postgres=# select * from func05(); id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa (5 rows)
使用return query:
create or replace function func06()returns setof record as $$ begin for i in 1..5 loop return query(select id,name from tb1 where id=i); end loop; end; $$language plpgsql;
postgres=# select * from func06() as t(id integer,name character varying); id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa (5 rows)
補(bǔ)充:Postgresql - plpgsql - 從Function中查詢并返回多行結(jié)果
通過(guò)plpgsql查詢表,并返回多行的結(jié)果。
關(guān)于創(chuàng)建實(shí)驗(yàn)表插入數(shù)據(jù)這里就不說(shuō)啦
返回查詢結(jié)果
mytest=# create or replace function test_0830_5() returns setof test mytest-# as $$ mytest$# DECLARE mytest$# r test%rowtype; -- 將 mytest$# BEGIN mytest$# FOR r IN mytest$# SELECT * FROM test WHERE id > 0 mytest$# LOOP mytest$# RETURN NEXT r; mytest$# END LOOP; mytest$# RETURN; mytest$# END mytest$# $$ language plpgsql; CREATE FUNCTION mytest=# select test_0830_5(1); test_0830_5 ------------------------------------------ (2,abcabc,"2018-08-30 09:26:14.392187") ...... (11,abcabc,"2018-08-30 09:26:14.392187") (10 rows) mytest=# select * from test_0830_5(); id | col1 | col2 ----+--------+---------------------------- 2 | abcabc | 2018-08-30 09:26:14.392187 ...... 11 | abcabc | 2018-08-30 09:26:14.392187 (10 rows)
返回某列
mytest=# CREATE OR REPLACE FUNCTION test_0830_6(date) RETURNS SETOF integer AS $$ mytest$# BEGIN mytest$# RETURN QUERY SELECT id mytest$# FROM test mytest$# WHERE col2 >= $1 mytest$# AND col2 < ($1 + 1); mytest$# IF NOT FOUND THEN mytest$# RAISE EXCEPTION 'No id at %.', $1; mytest$# END IF; mytest$# RETURN; mytest$# END mytest$# $$ mytest-# LANGUAGE plpgsql; CREATE FUNCTION mytest=# select test_0830_6('2018-08-30'); test_0830_6 ------------- 2 ...... 11 (10 rows)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教。
相關(guān)文章
postgresql 切換 log、xlog日志的實(shí)現(xiàn)
這篇文章主要介紹了postgresql 切換 log、xlog日志的實(shí)現(xiàn)方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01使用python-slim鏡像遇到無(wú)法使用PostgreSQL的問(wèn)題及解決方法
這篇文章主要介紹了使用python-slim鏡像遇到無(wú)法使用PostgreSQL的問(wèn)題及解決方法,本文給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧2024-08-08Postgresql 查看SQL語(yǔ)句執(zhí)行效率的操作
這篇文章主要介紹了Postgresql 查看SQL語(yǔ)句執(zhí)行效率的操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02PostgreSQL中的template0和template1庫(kù)使用實(shí)戰(zhàn)
這篇文章主要介紹了PostgreSQL中的template0和template1庫(kù)使用實(shí)戰(zhàn),具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01PostgreSQL中date_trunc函數(shù)的語(yǔ)法及一些示例
這篇文章主要給大家介紹了關(guān)于PostgreSQL中date_trunc函數(shù)的語(yǔ)法及一些示例的相關(guān)資料,DATE_TRUNC函數(shù)是PostgreSQL數(shù)據(jù)庫(kù)中用于截?cái)嗳掌诓糠值暮瘮?shù),文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-04-04postgresql 中position函數(shù)的性能詳解
這篇文章主要介紹了postgresql 中position函數(shù)的性能詳解,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02PostgreSQL查看正在執(zhí)行的任務(wù)并強(qiáng)制結(jié)束的操作方法
這篇文章主要介紹了PostgreSQL查看正在執(zhí)行的任務(wù)并強(qiáng)制結(jié)束的操作方法,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-01-01Windows?系統(tǒng)?PostgreSQL?手工安裝配置方法
這篇文章主要介紹了Windows?系統(tǒng)?PostgreSQL?手工安裝配置方法,本文主要說(shuō)一下在?Windows?系統(tǒng)中安裝?PostgreSQL?的方法,我這里沒(méi)有采用?exe?安裝包的形式去安裝,EDB?發(fā)布的那個(gè)?exe?安裝包形式的對(duì)于中文環(huán)境數(shù)據(jù)庫(kù)的排序規(guī)則設(shè)定有問(wèn)題,需要的朋友可以參考下2022-09-09PostgreSQL:string_agg?多列值聚合成一列的操作示例
PostgreSQL中的STRING_AGG()函數(shù)是一個(gè)聚合函數(shù),用于連接字符串列表并在字符串之間放置分隔符,這篇文章主要介紹了PostgreSQL:string_agg多列值聚合成一列,需要的朋友可以參考下2023-08-08