openGauss每日一练第15天 | 学习心得体会

1.创建带有入参和出参的函数1，调用函数时使用按参数值传递和命名标记法传参

create or replace function test1 (i in int, o out int) return int
as
begin
o = i*2;
end;
/
omm=# call test1(i=>2,o=>0);
 o 
---
 4
(1 row)

omm=# omm=# 

2.创建返回类型为record的函数2，重命名函数2

create or replace function test2(i int, out res_1 bigint, out res_2 bigint) 
returns SETOF RECORD
as $$
begin
res_1 = i * 2;
res_2 = i * 3;
return next;
end;
$$language plpgsql;
alter function test2(int) rename to test3;

3.使用\sf和系统函数查看函数定义

omm=# \sf test3
omm=# CREATE OR REPLACE FUNCTION public.test3(i integer, OUT res_1 bigint, OUT res_2 bigint)
 RETURNS SETOF record
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$
begin
res_1 = i * 2;
res_2 = i * 3;
return next;
end;
$function$;
omm=# select * from pg_proc where proname='test3';
 proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtyp
es | proallargtypes | proargmodes |   proargnames   | proargdefaults |     prosrc     | probin | proconfig | proacl | prodefaultargpos | fencedmode | proshippable | propackage | prokind 
   | {23,20,20}     | {i,o,o}     | {i,res_1,res_2} |                |               +|        |           |        |                  | f          | f            | f          | f
---------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+--------------+-------------+-----------+-------------+----------+-----------------+------------+----------
---+----------------+-------------+-----------------+----------------+----------------+--------+-----------+--------+------------------+------------+--------------+------------+---------
 test3   |         2200 |       10 |   11750 |     100 |    1000 |           0 | -            | f        | f           | f         | f            | f           | t         | v           |        1 |               0 |       2249 | 23       
         |              |          |         |         |         |             |              |          |             |           |              |             |           |             |          |                 |            |          
   |                |             |                 |                | begin         +|        |           |        |                  |            |              |            | 
   |                |             |                 |                | res_1 = i * 2;+|        |           |        |                  |            |              |            | 
         |              |          |         |         |         |             |              |          |             |           |              |             |           |             |          |                 |            |          
   |                |             |                 |                | res_2 = i * 3;+|        |           |        |                  |            |              |            | 
         |              |          |         |         |         |             |              |          |             |           |              |             |           |             |          |                 |            |          
         |              |          |         |         |         |             |              |          |             |           |              |             |           |             |          |                 |            |          
   |                |             |                 |                | return next;  +|        |           |        |                  |            |              |            | 
         |              |          |         |         |         |             |              |          |             |           |              |             |           |             |          |                 |            |          
   |                |             |                 |                |                |        |           |        |                  |            |              |            | 
(1 row)

   |                |             |                 |                | end;          +|        |           |        |                  |            |              |            | 
         |              |          |         |         |         |             |              |          |             |           |              |             |           |             |          | 

4.删除函数

drop function test1;
drop function test3;