create schema b_compat_create_func;
set current_schema to 'b_compat_create_func';
DROP ROLE if EXISTS us1;
NOTICE: role "us1" does not exist, skipping
CREATE ROLE us1 IDENTIFIED BY 'Aa@123456';
GRANT ALL ON SCHEMA b_compat_create_func TO us1;
SET ROLE us1 PASSWORD 'Aa@123456';
create function func(n int) returns varchar(50) return (select n+1);
select func(100);
func
------
101
(1 row)
drop function if exists func(int);
create function func(n int) returns varchar(50) return (select (n+1)::text);
select func(100);
func
------
101
(1 row)
drop function if exists func(int);
create function func(s char(20)) returns char(50) return concat('Hello, ', s, '!');
select func('me');
func
------------
Hello, me!
(1 row)
drop function if exists func(char(20));
create function func(s date) returns timestamp return current_timestamp;
select func('2023-01-28'::date);
func
------------------------
--?.*
(1 row)
drop function if exists func(date);
create function func(p1 datetime, p2 int) returns time return null;
select func('2023-01-28 00:00:00'::datetime, 1);
func
------
(1 row)
drop function if exists func(datetime, int);
-- test for duplicated options
create function func(s int)
returns char(50)
deterministic
sql security invoker
sql security invoker
return s;
select func(20);
func
------
20
(1 row)
drop function if exists func;
create function func(s int)
returns char(50)
deterministic
sql security invoker
language C
not deterministic
language sql
sql security definer
return s;
select func(20);
func
------
20
(1 row)
drop function if exists func;
create table test11(
name varchar(50) not null,
current_value int not null,
increment int not null default 1,
primary key (name)
) engine=innodb;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test11_pkey" for table "test11"
insert into test11 values ('a', 999, 999);
drop function if exists currval(varchar(50));
NOTICE: function currval(pg_catalog.varchar) does not exist, skipping
delimiter /
create function currval(seq_name varchar(50)) returns integer
language sql
deterministic
contains sql
sql security definer
comment ''
begin
declare value integer;
set value = 0;
select current_value into value from test11 where name = seq_name;
return value;
end/
delimiter ;
select currval('a');
currval
---------
999
(1 row)
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length';
drop function if exists currval(varchar(50));
delimiter //
create function currval(seq_name varchar(50)) returns integer
comment 'abcdefghijk'
begin
declare value integer;
set value = 0;
select current_value into value from test11 where name = seq_name;
return value;
end //
delimiter ;
select currval('a');
currval
---------
999
(1 row)
show create function currval;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
----------+----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
currval | CREATE DEFINER = us1 FUNCTION b_compat_create_func.currval(seq_name varchar(50))+| sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURN integer NOT FENCED NOT SHIPPABLE +| | | |
| BEGIN declare value integer; +| | | |
| set value = 0; +| | | |
| select current_value into value from test11 where name = seq_name; +| | | |
| return value; +| | | |
| end ; +| | | |
| | | | |
(1 row)
reset dolphin.sql_mode;
drop function if exists currval(varchar(50));
delimiter /
create function currval(seq_name varchar(50)) returns integer
language sql
begin
declare value integer;
set value = 0;
select current_value into value from test11 where name = seq_name;
return value;
end /
delimiter ;
select currval('a');
currval
---------
999
(1 row)
drop function if exists currval(varchar(50));
delimiter /
create function currval(seq_name varchar(50)) returns integer
deterministic
begin
declare value integer;
set value = 0;
select current_value into value from test11 where name = seq_name;
return value;
end /
delimiter ;
select currval('a');
currval
---------
999
(1 row)
drop function if exists currval(varchar(50));
delimiter /
create function currval(seq_name varchar(50)) returns integer
contains sql
begin
declare value integer;
set value = 0;
select current_value into value from test11 where name = seq_name;
return value;
end /
delimiter ;
select currval('a');
currval
---------
999
(1 row)
drop function if exists currval(varchar(50));
delimiter /
create function currval(seq_name varchar(50)) returns integer
sql security definer
begin
declare value integer;
set value = 0;
select current_value into value from test11 where name = seq_name;
return value;
end /
delimiter ;
select currval('a');
currval
---------
999
(1 row)
drop function if exists simplecompare;
NOTICE: function simplecompare() does not exist, skipping
delimiter /
create function simplecompare(n int, m int) returns varchar(20)
begin
declare s varchar(20);
if n > m then set s = '>';
elseif n = m then set s = '=';
else set s = '<';
end if;
set s = concat(n, ' ', s, ' ', m);
return s;
end/
delimiter ;
select simplecompare(1, 2);
simplecompare
---------------
1 < 2
(1 row)
drop function if exists func_ddl_0011;
NOTICE: function func_ddl_0011() does not exist, skipping
delimiter |
create function func_ddl_0011(n1 int, n2 int) returns int
begin
declare num int;
set num = n1 + n2;
return num;
END|
delimiter ;
call func_ddl_0011(1, 5);
ERROR: Only support procedure in muiti result call statement
drop function if exists simplecompare;
delimiter //
create function simplecompare(n int, m int) returns varchar(20)
begin
declare s varchar(20);
if n > m then set s = '>';
elseif n = m then set s = '=';
else set s = '<';
end if;
set s = concat(n, ' ', s, ' ', m);
return s;
end //
delimiter ;
call simplecompare(1, 2);
ERROR: Only support procedure in muiti result call statement
-- test for function body with flow control stmt
drop function if exists func1;
NOTICE: function func1() does not exist, skipping
delimiter |
create function func1(b int, c int) returns int
if b < c then return b;
else return c;
end if|
select func1(2,3)|
func1
-------
2
(1 row)
drop function if exists func2|
NOTICE: function func2() does not exist, skipping
create function func2(b int) returns int
case b
when 1 then return 9;
when 2 then return 99;
when 3 then return 999;
else return 0;
end case|
select func2(3)|
func2
-------
999
(1 row)
drop function if exists func3|
NOTICE: function func3() does not exist, skipping
create function func3(b int) returns int
loop
if b < 10 then set b = b + 10;
return b;
end if;
end loop|
select func3(3)|
func3
-------
13
(1 row)
drop function if exists func3|
create function func3(b int) returns int
label1:loop
if b < 10 then set b = b + 10;
return b;
end if;
end loop|
select func3(3)|
func3
-------
13
(1 row)
drop function if exists func3|
create function func3(b int) returns int
label1: loop
if b < 10 then set b = b + 10;
return b;
end if;
end loop|
select func3(3)|
func3
-------
13
(1 row)
drop function if exists func4|
NOTICE: function func4() does not exist, skipping
create function func4(b int) returns int
repeat
set b = b + 10;
return b;
until b > 10 end repeat|
select func4(3)|
func4
-------
13
(1 row)
drop function if exists func4|
create function func4(b int) returns int
label2:repeat
set b = b + 10;
return b;
until b > 10 end repeat|
select func4(3)|
func4
-------
13
(1 row)
drop function if exists func4|
create function func4(b int) returns int
label2: repeat
set b = b + 10;
return b;
until b > 10 end repeat|
select func4(3)|
func4
-------
13
(1 row)
drop function if exists func5|
NOTICE: function func5() does not exist, skipping
create function func5(b int) returns int
while b < 10 do
set b = b + 10;
return b;
end while|
select func5(3)|
func5
-------
13
(1 row)
drop function if exists func5|
create function func5(b int) returns int
label3:while b < 10 do
set b = b + 10;
return b;
end while|
select func5(3)|
func5
-------
13
(1 row)
drop function if exists func5|
create function func5(b int) returns int
label3: while b < 10 do
set b = b + 10;
return b;
end while|
select func5(3)|
func5
-------
13
(1 row)
-- test for begin-end statement compound by flow control statement
drop function if exists func6|
NOTICE: function func6() does not exist, skipping
create function func6(b int) returns int
loop
begin
if b > 0 then
return b + 10;
else return -1;
end if;
end;
end loop|
select func6(3)|
func6
-------
13
(1 row)
-- test for nested begin-end within begin-end
drop function if exists func7 |
NOTICE: function func7() does not exist, skipping
create function func7(b int) returns int
begin
declare num int;
set num = 0;
begin
num = num - b;
num = num * 10;
end;
return num;
end|
select func7(5)|
func7
-------
-50
(1 row)
drop function if exists func8|
NOTICE: function func8() does not exist, skipping
create function func8(b int) returns int
BEGIN
IF b = 1 THEN
BEGIN
set b = b + 10;
return b;
END;
ELSE
BEGIN
set b = b - 10;
return b;
END;
END IF;
END|
select func8(3)|
func8
-------
-7
(1 row)
drop function if exists func9|
NOTICE: function func9() does not exist, skipping
create function func9(b int) returns int
BEGIN
BEGIN
set b = b + 10;
return b;
END;
END|
select func9(5)|
func9
-------
15
(1 row)
-- test for function body with begin-end stmt
drop function if exists currval(varchar(50))|
delimiter /
create function currval(seq_name varchar(50))
returns integer
begin
declare value integer;
set value = 0;
select current_value into value from test11 where name = seq_name;
return value;
end/
delimiter ;
select currval('a');
currval
---------
999
(1 row)
-- test for flow control statement compound by begin-end stmt
drop function if exists func10;
NOTICE: function func10() does not exist, skipping
delimiter //
create function func10(b int) returns int
begin
if b > 0 then return b + 10;
else return -1;
end if;
end//
delimiter ;
select func10(9);
func10
--------
19
(1 row)
drop function if exists hello;
NOTICE: function hello() does not exist, skipping
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ', s, '!');
select hello('me');
hello
------------
Hello, me!
(1 row)
drop function if exists hello;
delimiter |
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ', s, '!')|
delimiter ;
select hello('me');
hello
------------
Hello, me!
(1 row)
drop function if exists hello;
delimiter /
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
BEGIN
RETURN CONCAT('Hello, ', s, '!');
END/
delimiter ;
select hello('me');
hello
------------
Hello, me!
(1 row)
drop function if exists func(int);
NOTICE: function func(pg_catalog.int4) does not exist, skipping
create function func(n int) returns varchar(50) return (select (n+1)::text);
-- test for function options
drop function if exists currval(varchar(50));
delimiter /
create function currval(seq_name varchar(50))
returns integer
language sql
deterministic
comment 'me'
begin
declare value integer;
set value = 0;
select current_value into value from test11 where name = seq_name;
return value;
end/
delimiter ;
-- test for invalid function options
drop function if exists currval(varchar(50));
delimiter /
create function currval(seq_name varchar(50)) returns integer
strict
comment 'test'
immutable
return 1/
ERROR: invalid option input
delimiter ;
-- testcase for mysql
drop table if exists t1;
NOTICE: table "t1" does not exist, skipping
create table t1(c int);
delimiter |
create function m_func(n int) returns int(11)
begin
insert into t1 values (null);
return n;
end|
delimiter ;
call m_func(6);
ERROR: Only support procedure in muiti result call statement
drop function if exists m_func(int);
insert into t1 values(1);
delimiter |
create function m_func() returns int
begin
declare j int;
select c from t1 where c = 1 into j;
return j;
end|
delimiter ;
call m_func();
ERROR: Only support procedure in muiti result call statement
drop function if exists m_func();
drop table if exists t1;
create table t1(c int);
CREATE OR REPLACE PROCEDURE proc1()
AS
BEGIN
INSERT INTO t1 VALUES (1);
END;
/
delimiter |
create function func_call_proc1(a int) returns int
begin
call proc1();
return 1;
end|
delimiter ;
call func_call_proc1(1);
ERROR: Only support procedure in muiti result call statement
drop function if exists func_call_proc1(int);
-- test for begin-end surrounded body statement without return
drop table if exists t_create_begin_end_func_no_return;
NOTICE: table "t_create_begin_end_func_no_return" does not exist, skipping
create table t_create_begin_end_func_no_return(c1 int);
drop function if exists t_create_begin_end_func_no_return;
NOTICE: function t_create_begin_end_func_no_return() does not exist, skipping
delimiter |
create function t_create_begin_end_func_no_return(num int) returns int
no sql
BEGIN
declare i int;
set i = 0;
while i < num do
insert into t_create_begin_end_func_no_return values(i);
set i = i + 1;
end while;
end|
ERROR: no RETURN found in function body
delimiter ;
-- test for flow control body statement without return
delimiter |
drop function if exists t_create_flow_control_func_no_return|
NOTICE: function t_create_flow_control_func_no_return() does not exist, skipping
create function t_create_flow_control_func_no_return(b int) returns int
repeat
set b = b + 10;
until b > 10 end repeat|
ERROR: no RETURN found in function body
delimiter ;
drop table if exists test_table_030;
NOTICE: table "test_table_030" does not exist, skipping
create table test_table_030(ID int,NAME text);
insert into test_table_030 values(1,'a'),(2,'b');
drop function if exists test_function_030;
NOTICE: function test_function_030() does not exist, skipping
DELIMITER |
CREATE FUNCTION test_function_030(canshu VARCHAR(16))
returns INT READS SQL DATA
BEGIN
DECLARE test_table_030a int;
SELECT ID into test_table_030a FROM test_table_030 WHERE NAME = canshu;
return test_table_030a;
END |
DELIMITER ;
--test trigger
drop function if exists f_create_function_0033;
NOTICE: function f_create_function_0033() does not exist, skipping
delimiter |
create function f_create_function_0033() returns trigger
begin
insert into t_create_function_0033 values(new.id1, new.id2, new.id3);
return new;
end|
delimiter ;
CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
delimiter /
CREATE OR REPLACE FUNCTION tri_insert_funcx() RETURNS TRIGGER
BEGIN
INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
RETURN NEW;
END
/
delimiter ;
CREATE TRIGGER insert_triggerx
BEFORE INSERT ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_funcx();
insert into test_trigger_src_tbl values(1,2,3);
select * from test_trigger_src_tbl;
id1 | id2 | id3
-----+-----+-----
1 | 2 | 3
(1 row)
select * from test_trigger_des_tbl;
id1 | id2 | id3
-----+-----+-----
1 | 2 | 3
(1 row)
drop table test_trigger_src_tbl cascade;
drop table test_trigger_des_tbl cascade;
select test_function_030('aaa');
test_function_030
-------------------
(1 row)
select test_function_030('aaa') is null;
?column?
----------
t
(1 row)
drop table if exists test_table_030;
drop function if exists test_function_030;
CREATE PROCEDURE p_log_job_info_bak()
AS DECLARE
begin
raise notice '1';
commit;
END;
/
create procedure p_gen_rpt_init_self_recovery()
AS
DECLARE
temp_wtg_id BIGINT;
CURSOR crs_ar FOR (SELECT wtg_id
FROM temp_init_sr_previous
ORDER BY 1);
BEGIN
CREATE TEMPORARY TABLE temp_init_sr_previous(wtg_id BIGINT);
OPEN crs_ar;
ar_loop:LOOP
FETCH crs_ar INTO temp_wtg_id;
IF NOT FOUND THEN
leave ar_loop;
END IF;
END LOOP;
CLOSE crs_ar;
DROP TEMPORARY TABLE temp_init_sr_previous;
CALL p_log_job_info_bak();
END;
/
set b_format_behavior_compat_options = '';
call p_gen_rpt_init_self_recovery();
NOTICE: 1
CONTEXT: SQL statement "CALL p_log_job_info_bak()"
PL/pgSQL function p_gen_rpt_init_self_recovery() line 18 at SQL statement
set b_format_behavior_compat_options = 'fetch';
call p_gen_rpt_init_self_recovery();
ERROR: No data - zero rows fetched, selected, or processed
CONTEXT: PL/pgSQL function p_gen_rpt_init_self_recovery() line 10 at FETCH
drop procedure p_log_job_info_bak;
DROP PROCEDURE p_gen_rpt_init_self_recovery;
-- expect error: there is no parameter $1, other error is wrong!!!
select :lable;
ERROR: there is no parameter $1
LINE 1: select :lable;
^
select :loop;
ERROR: there is no parameter $1
LINE 1: select :loop;
^
select :repeat;
ERROR: there is no parameter $1
LINE 1: select :repeat;
^
select :while;
ERROR: there is no parameter $1
LINE 1: select :while;
^
DELIMITER //
CREATE FUNCTION fn_grade_label(grade varchar(20) CHARSET utf8mb4 collate utf8mb4_bin)
RETURNS VARCHAR(20) CHARSET utf8mb4 collate utf8mb4_bin
DETERMINISTIC
BEGIN
IF grade = 'A' THEN
RETURN 'Excellent';
ELSEIF grade = 'B' THEN
RETURN 'Good';
ELSEIF grade = 'C' THEN
RETURN 'Pass';
ELSE
RETURN 'Unknown';
END IF;
END //
DELIMITER ;
SELECT fn_grade_label('A') AS a,
fn_grade_label('B') AS b,
fn_grade_label('C') AS c,
fn_grade_label('a') AS lower_a,
fn_grade_label('X') AS x;
a | b | c | lower_a | x
-----------+------+------+---------+---------
Excellent | Good | Pass | Unknown | Unknown
(1 row)
DROP FUNCTION fn_grade_label;
DELIMITER //
CREATE FUNCTION fn_grade_label(grade varchar(20) CHARSET utf8mb4)
RETURNS VARCHAR(20) CHARSET utf8mb4
DETERMINISTIC
BEGIN
IF grade = 'A' THEN
RETURN 'Excellent';
ELSEIF grade = 'B' THEN
RETURN 'Good';
ELSEIF grade = 'C' THEN
RETURN 'Pass';
ELSE
RETURN 'Unknown';
END IF;
END //
DELIMITER ;
SELECT fn_grade_label('A') AS a,
fn_grade_label('B') AS b,
fn_grade_label('C') AS c,
fn_grade_label('a') AS lower_a,
fn_grade_label('X') AS x;
a | b | c | lower_a | x
-----------+------+------+---------+---------
Excellent | Good | Pass | Unknown | Unknown
(1 row)
DROP FUNCTION fn_grade_label;
drop schema b_compat_create_func cascade;
NOTICE: drop cascades to 20 other objects
DETAIL: drop cascades to table test11
drop cascades to function func_ddl_0011(integer,integer)
drop cascades to function simplecompare(integer,integer)
drop cascades to function func1(integer,integer)
drop cascades to function func2(integer)
drop cascades to function func3(integer)
drop cascades to function func4(integer)
drop cascades to function func5(integer)
drop cascades to function func6(integer)
drop cascades to function func7(integer)
drop cascades to function func8(integer)
drop cascades to function func9(integer)
drop cascades to function func10(integer)
drop cascades to function hello(character)
drop cascades to function func(integer)
drop cascades to table t1
drop cascades to function proc1()
drop cascades to table t_create_begin_end_func_no_return
drop cascades to function f_create_function_0033()
drop cascades to function tri_insert_funcx()
reset current_schema;
reset role;
drop role us1;