Postgresql - plpgsql - 从Function中查询并返回多行结果

通过plpgsql查询表，并返回多行的结果。

 

创建实验表插入数据参考之前文章

 

返回查询结果

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)