学习心得

CREATE OR REPLACE FUNCTION/PROCEDURE
ALTER FUNCTION/PROCEDURE
DROP FUNCTION/PROCEDURE
\sf <function_name>

0.进入系统

su - omm
gsql -r

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

创建func_1

CREATE OR REPLACE FUNCTION func_1(int1 IN integer, int2 IN integer, res OUT integer)
RETURN integer
AS
BEGIN
res := int1 - int2;
END;
/

• 回显

参数值传递调用func_1

call func_1(5, 4 , 0);

• 回显

 res
-----
   1
(1 row)

命名标记法传参

CALL func_1(int1 => 15,int2 => 3,res => 0);

• 回显

 res
-----
  12
(1 row)

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

创建func_2

CREATE OR REPLACE FUNCTION func_2(i integer, out result_1 bigint, out result_2 bigint)
returns SETOF RECORD
as $$
begin
result_1 = i + 5;
result_2 = i * 5;
return next;
end;
$$language plpgsql;

• 回显

CREATE FUNCTION

CALL func_2(5, 0, 0)

• 回显

 result_1 | result_2
----------+----------
       10 |       25
(1 row)

重命名func_2为func_20

ALTER FUNCTION func_2(int) RENAME TO func_20;

• 回显

ALTER FUNCTION

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

\sf func_20

• 回显

CREATE OR REPLACE FUNCTION public.func_20(i integer, OUT result_1 bigint, OUT result_2 bigint)
 RETURNS SETOF record
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$
begin
result_1 = i + 5;
result_2 = i * 5;
return next;
end;
$function$;

select * from pg_proc where proname = 'func_20';

• 回显

 proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prose
cdef | proleakproof | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallar
gtypes | proargmodes |      proargnames      | proargdefaults |      prosrc       | probin | proconfig | proacl | prodefaultar
gpos | fencedmode | proshippable | propackage | prokind
---------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+------
-----+--------------+-------------+-----------+-------------+----------+-----------------+------------+-------------+---------
-------+-------------+-----------------------+----------------+-------------------+--------+-----------+--------+-------------
-----+------------+--------------+------------+---------
 func_20 |         2200 |       10 |   11750 |     100 |    1000 |           0 | -            | f        | f           | f
     | f            | f           | t         | v           |        1 |               0 |       2249 | 23          | {23,20,2
0}     | {i,o,o}     | {i,result_1,result_2} |                |                  +|        |           |        |
     | f          | f            | f          | f
         |              |          |         |         |         |             |              |          |             |
     |              |             |           |             |          |                 |            |             |
       |             |                       |                | begin            +|        |           |        |
     |            |              |            |
         |              |          |         |         |         |             |              |          |             |
     |              |             |           |             |          |                 |            |             |
       |             |                       |                | result_1 = i + 5;+|        |           |        |
     |            |              |            |
         |              |          |         |         |         |             |              |          |             |
     |              |             |           |             |          |                 |            |             |
       |             |                       |                | result_2 = i * 5;+|        |           |        |
     |            |              |            |
         |              |          |         |         |         |             |              |          |             |
     |              |             |           |             |          |                 |            |             |
       |             |                       |                | return next;     +|        |           |        |
     |            |              |            |
         |              |          |         |         |         |             |              |          |             |
     |              |             |           |             |          |                 |            |             |
       |             |                       |                | end;             +|        |           |        |
     |            |              |            |
         |              |          |         |         |         |             |              |          |             |
     |              |             |           |             |          |                 |            |             |
       |             |                       |                |                   |        |           |        |
     |            |              |            |
(1 row)

4.删除函数

drop function func_1;
drop function func_20;
\sf func_20;

• 回显

DROP FUNCTION
DROP FUNCTION
ERROR:  function "func_20;" does not exist