openGauss每日一练第 15 天 | 函数

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

CREATE FUNCTION fuc_worth(price IN numeric(8,2), amount IN integer, worth OUT numeric(16,2))
RETURN numeric
AS
BEGIN
worth:= price * amount;
END;
/
CALL fuc_worth(10.22,203,1);
CALL fuc_worth(price => 6.66,amount => 666,worth => 256);
CALL fuc_worth(amount := 333, price := 2.57,worth => 128);

omm=# CREATE FUNCTION fuc_worth(price IN numeric(8,2), amount IN integer, worth OUT numeric(16,2))
omm-# RETURN numeric
omm-# AS
omm$# BEGIN
omm$# worth:= price * amount;
omm$# END;
omm$# /
CREATE FUNCTION
omm=# CALL fuc_worth(10.22,203,1);
  worth  
---------
 2074.66
(1 row)

omm=# CALL fuc_worth(price => 6.66,amount => 666,worth => 256);
  worth  
---------
 4435.56
(1 row)

omm=# CALL fuc_worth(amount := 333, price := 2.57,worth => 128);
 worth  
--------
 855.81
(1 row)

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

CREATE OR REPLACE FUNCTION fuc_worth1(price IN numeric(8,2), price1 out integer, worth OUT numeric(16,2))
returns SETOF RECORD
as $$
begin
price1 = round(price,0);
worth = price * price1;
return next;
end;
$$language plpgsql;
call fuc_worth1(3.66, 0, 0);

ALTER FUNCTION fuc_worth1(numeric(8,2),integer) RENAME TO fuc_cost1 ;
ALTER FUNCTION fuc_worth1(numeric) RENAME TO fuc_cost1 ;

omm=# CREATE OR REPLACE FUNCTION fuc_worth1(price IN numeric(8,2), price1 out integer, worth OUT numeric(16,2))
omm-# returns SETOF RECORD
omm-# as $$
omm$# begin
omm$# price1 = round(price,0);
omm$# worth = price * price1;
omm$# omm$# return next;
omm$# end;
$$language plpgsql;
CREATE FUNCTION
omm=# call fuc_worth1(3.66, 0, 0);
 price1 | worth 
--------+-------
      4 | 14.64
(1 row)
#如下修改函数名字，格式为 函数（入参数据类型）
omm=# ALTER FUNCTION fuc_worth1(numeric(8,2),integer,numeric(16,2)) RENAME TO fuc_cost1 ;
ERROR:  function fuc_worth1(numeric, integer, numeric) does not exist

omm=# ALTER FUNCTION fuc_worth1(numeric(8,2),integer) RENAME TO fuc_cost1 ;
ERROR:  function fuc_worth1(numeric, integer) does not exist
omm=# \sf fuc_worth1
CREATE OR REPLACE FUNCTION public.fuc_worth1(price numeric, OUT price1 integer, OUT worth numeric)
 RETURNS SETOF record
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$
begin
price1 = round(price,0);
worth = price * price1;
return next;
end;
$function$;
omm=# ALTER FUNCTION fuc_worth1(numeric) RENAME TO fuc_cost1 ;
ALTER FUNCTION

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

\sf fuc_cost1
select * from pg_proc where proname = 'fuc_cost1';

omm=# \sf fuc_worth1;
ERROR:  function "fuc_worth1;" does not exist
omm=# \sf fuc_cost1;
ERROR:  function "fuc_cost1;" does not exist
omm=# \sf fuc_cost1
CREATE OR REPLACE FUNCTION public.fuc_cost1(price numeric, OUT price1 integer, OUT worth numeric)
 RETURNS SETOF record
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$
begin
price1 = round(price,0);
worth = price * price1;
return next;
end;
$function$;

4.删除函数

drop function fuc_cost1;
drop function fuc_worth;

omm=# drop function fuc_cost1;
DROP FUNCTION
omm=# drop function fuc_worth;
DROP FUNCTION
omm=#