PL/pgSQL的RETURN QUERY例子

我的例子：

数据准备：

create table custinfo(custid integer,callingcnt integer);
insert into custoinfo valuse(1,10),(2,6),(3,8);

函数生成：

CREATE OR REPLACE FUNCTION get_callingcnt(custid int)
  RETURNS TABLE (
   custid     int
  ,callingcnt int
  ) AS
$$
BEGIN

   RETURN QUERY
   SELECT t.custid
         ,t.callingcnt
   FROM  custinfo t
   WHERE t.custid = custid;

END;
$$  LANGUAGE plpgsql;

执行结果：

[pgsql@localhost bin]$ ./psql
psql (9.1.2)
Type "help" for help.

pgsql=# select get_callcnt(1);
 get_callcnt 
-------------
 (1,10)
(1 row)

pgsql=# select get_callcnt(2);
 get_callcnt 
-------------
 (2,6)
(1 row)

pgsql=# \q
[pgsql@localhost bin]$ 

 

转载于:https://www.cnblogs.com/gaojian/p/3179929.html