PostgreSQL function返回多行的操作

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

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;

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;

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;

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;

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, …);

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 )

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 )

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 )

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 )