且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

PL/pgSQL的RETURN QUERY例子

更新时间:2022-09-24 20:01:29

我的例子:

数据准备:

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

函数生成:

PL/pgSQL的RETURN QUERY例子
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;
PL/pgSQL的RETURN QUERY例子

执行结果:

PL/pgSQL的RETURN QUERY例子
[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]$ 
PL/pgSQL的RETURN QUERY例子