openGauss每日一练第15天-自定义存储过程和函数

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# create function test123(num1 in integer,num2 in integer,num3 out integer) return integer
omm-# as
omm$# beginomm$#
num3 := num1 + num2;
omm$# end;
omm$# /
CREATE FUNCTION
omm=# call test123(1,2,3);
num3
------
3
(1 row)

omm=# call test123(2,3,4);
num3
------
5
(1 row)

omm=# call test123(num1 := 3,num2 :=44,3);
num3
------
47
(1 row)

omm=# create function test456(i int out result_1 bigint,out result_2 bigint)
omm-# returns setof record
omm-# as $$
omm$# begin
omm$# result_1 = i+1;
omm$# result_2 i * 10;
omm$# return next;omm$#
end;
omm$# $$language plpgsql;
ERROR: syntax error at or near "out"
LINE 1: create function test456(i int out result_1 bigint,out result...
^
omm=# CREATE OR REPLACE FUNCTION test456(i int, out result_1 bigint, out result_2
omm(# bigint)
omm-# returns SETOF RECORD
omm-# as $$
omm$# begin
omm$# result_1 = i + 1;
omm$# result_2 = i * 10;
omm$# omm$# end;
return next;
omm$# $$language plpgsql;
CREATE FUNCTION
omm=# call test456(1, 0, 0);
result_1 | result_2
----------+----------
2 | 10
(1 row)

omm=# \sf test456
CREATE OR REPLACE FUNCTION public.test456(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 + 1;
result_2 = i * 10;
return next;
end;
$function$;
omm=# alter function test123(integer,integer) rename to test123_new;
ALTER FUNCTION
omm=# \sf test123_new
CREATE OR REPLACE FUNCTION public.test123_new(num1 integer, num2 integer, OUT num3 integer)
RETURNS integer
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE
begin
num3 := num1 + num2;
end$function$;
omm=# drop function test123_new;
omm=# DROP FUNCTION

omm=# drop function test456;
DROP FUNCTION
omm=# \sf
function name is required
omm=#