create schema multi_select_proc;
set current_schema to 'multi_select_proc';
--open parameter sql_mode
set dolphin.sql_mode = 'block_return_multi_results';
create table test_1(a int,b int);
create table t (a int);
insert into test_1 values(1,2),(3,4);
insert into t values(123),(789);
--one select
CREATE PROCEDURE proc_a_1 () as
begin
select * from t;
end;
/
call proc_a_1();
a
-----
123
789
(2 rows)
-- two select
CREATE PROCEDURE proc_a_2 () as
begin
select * from t;
select * from test_1;
end;
/
call proc_a_2();
a
-----
123
789
(2 rows)
a | b
---+---
1 | 2
3 | 4
(2 rows)
-- with input params
CREATE PROCEDURE proc_a_3 (aa int) as
begin
select aa from t;
select * from test_1;
end;
/
call proc_a_3(1);
aa
----
1
1
(2 rows)
a | b
---+---
1 | 2
3 | 4
(2 rows)
-- with while and out param
CREATE PROCEDURE proc_b_1 (aa int, out re1 int,out re2 int) as
declare i int default 1;
begin
re1 = aa +100;
re2 = aa + 1000;
while i<=2 do
i := i+1;
select aa + 1,a from test_1;
end while;
select * from t;
end;
/
--user var
set b_format_behavior_compat_options = 'enable_set_variables';
call proc_b_1(1,@a,@b);
?column? | a
----------+---
2 | 1
2 | 3
(2 rows)
?column? | a
----------+---
2 | 1
2 | 3
(2 rows)
a
-----
123
789
(2 rows)
set b_format_behavior_compat_options = '';
set enable_set_variable_b_format = off;
call proc_b_1(1,@a,@b);
ERROR: out args must be uservar type.
set enable_set_variable_b_format = 1;
call proc_b_1(1,@a,@b);
?column? | a
----------+---
2 | 1
2 | 3
(2 rows)
?column? | a
----------+---
2 | 1
2 | 3
(2 rows)
a
-----
123
789
(2 rows)
select @a;
@a
-----
101
(1 row)
select @b;
@b
------
1001
(1 row)
CREATE PROCEDURE proc_b_2 (aa int, out re1 int,out re2 int) as
declare i int default 1;
begin
re1 = aa +100;
re2 = aa + 1000;
while i<=2 do
i := i+1;
insert into test_1 values(6,7);
end while;
select * from test_1;
end;
/
call proc_b_2 (102,@c,@d);
a | b
---+---
1 | 2
3 | 4
6 | 7
6 | 7
(4 rows)
--check params
select @c;
@c
-----
202
(1 row)
select @d;
@d
------
1102
(1 row)
set enable_set_variable_b_format = 0;
--half wrong
create table tab_1145173(id int,pid int,a1 char(8));
create table a_1145173(id int,a1 char(8));
create table b_1145173(id int,a1 char(8));
--insert;
insert into tab_1145173 values(1,2,'s'),(2,3,'b'),(3,4,'c'),(4,5,'d');
insert into a_1145173 values(1,'s'),(2,'b');
insert into b_1145173 values(2,'s'),(3,'b');
create or replace procedure pro_1145173()
as
begin
select * from a_1145173 union select * from b_1145173 order by id;
select * from tab_1145173;
select tt_114;
end;
/
call pro_1145173();
id | a1
----+----
1 | s
2 | s
2 | b
3 | b
(4 rows)
id | pid | a1
----+-----+----
1 | 2 | s
2 | 3 | b
3 | 4 | c
4 | 5 | d
(4 rows)
ERROR: column "tt_114" does not exist
LINE 1: select tt_114
^
QUERY: select tt_114
CONTEXT: referenced column: tt_114
PL/pgSQL function pro_1145173() line 4 at SQL statement
create table tab_1144052(id int,pid int,a1 char(8));
--insert;
insert into tab_1144052 values(1,2,'s'),(2,3,'b'),(3,4,'c'),(4,5,'d');
--proc;
create or replace procedure pro_1144052()
as
begin
with temp_1144052(a1,a2) as (select id,a1 from tab_1144052 where id > 1) select * from temp_1144052;
select * from tab_1144052 start with pid = 4 connect by prior id = pid order by a1;
select avg(id),a1 from tab_1144052 group by a1 having avg(id) > 1;
end;
/
--func;
create or replace function fun_1144052()return int
as
b int;
begin
select count(*) into b from tab_1144052;
return b;
end;
/
call pro_1144052();
a1 | a2
----+----
2 | b
3 | c
4 | d
(3 rows)
id | pid | a1
----+-----+----
2 | 3 | b
3 | 4 | c
1 | 2 | s
(3 rows)
avg | a1
--------------------+----
2.0000000000000000 | b
3.0000000000000000 | c
4.0000000000000000 | d
(3 rows)
call fun_1144052();
ERROR: Only support procedure in muiti result call statement
select fun_1144052();
fun_1144052
-------------
4
(1 row)
select pro_1144052();
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function pro_1144052() line 2 at SQL statement
referenced column: pro_1144052
-- mysql format
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length,block_return_multi_results';
delimiter //
CREATE PROCEDURE proc_a_m ()
begin
select * from t;
end
//
delimiter ;
call proc_a_m();
a
-----
123
789
(2 rows)
create table testtyp (a int8, b varchar ,c date,d bytea);
insert into testtyp values(123,'abv','2020-01-01','a');
insert into testtyp values(1123,'abcv','2022-01-01','c');
insert into testtyp values(NULL,NULL,NULL,NULL);
delimiter //
CREATE PROCEDURE proc_a_m1 ()
begin
select a,b,c,d from testtyp;
select a,c,d,b from testtyp;
end
//
delimiter ;
call proc_a_m1();
a | b | c | d
------+------+------------+------
123 | abv | 2020-01-01 | \x61
1123 | abcv | 2022-01-01 | \x63
| | |
(3 rows)
a | c | d | b
------+------------+------+------
123 | 2020-01-01 | \x61 | abv
1123 | 2022-01-01 | \x63 | abcv
| | |
(3 rows)
create or replace procedure pro_11451713()
as
begin
end;
/
call pro_11451713();
--bug fix in call in call procedure
set dolphin.sql_mode=block_return_multi_results;
create table tab_1145533(id int,pid int,a1 char(8));
--insert;
insert into tab_1145533 values(1,2,'s'),(2,3,'b'),(3,4,'c'),(4,5,'d');
--create proc;
create or replace procedure pro_1145533()
as
begin
select * from tab_1145533 order by id;
end;
/
create or replace procedure pro1_1145533(n in int)
as
begin
if n>3 then
call pro_1145533();
else
select * from tab_1145533 where id >2;
end if;
end;
/
create or replace procedure pro2_1145533(n in int)
as
begin
call pro1_1145533(n);
end;
/
call pro2_1145533(2);
id | pid | a1
----+-----+----
3 | 4 | c
4 | 5 | d
(2 rows)
call pro2_1145533(5);
id | pid | a1
----+-----+----
1 | 2 | s
2 | 3 | b
3 | 4 | c
4 | 5 | d
(4 rows)
--fill up default value in the end
create procedure proc_def_1(a int , b int = 1) as
begin
select b,a ;
end;
/
call proc_def_1(2);
b | a
---+---
1 | 2
(1 row)
set enable_set_variable_b_format = 1;
create procedure proc_def_2(a out int , b int = 1) as
begin
a = 1234 + b;
select b,a ;
end;
/
set @out = 123;
call proc_def_2(@out);
b | a
---+------
1 | 1235
(1 row)
call proc_def_2(@out,11);
b | a
----+------
11 | 1245
(1 row)
set b_format_behavior_compat_options=enable_set_variables;
drop table if exists t_tinyint0009 cascade;
NOTICE: table "t_tinyint0009" does not exist, skipping
create table t_tinyint0009 (
c1 tinyint auto_increment primary key,
c2 tinyint(1) default '0',
c3 tinyint(10) not null default '0',
c4 int default '0',
c5 text
);
NOTICE: CREATE TABLE will create implicit sequence "t_tinyint0009_c1_seq" for serial column "t_tinyint0009.c1"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_tinyint0009_pkey" for table "t_tinyint0009"
drop procedure if exists insertdata;
NOTICE: function insertdata() does not exist, skipping
create procedure insertdata(num int) as
begin
set @x = 1;
truncate t_tinyint0009;
repeat
set @c1=@x;
set @c2=floor(0.1*(127-18+1))+18;
set @c3=floor(0.1*(127-100+1))+100;
set @c4=floor(0.1*(10000-127+1))+127;
set @c5=concat('amy', @x);
select @c1;
select @c2;
select @c3;
select @c4;
select @c5;
insert into t_tinyint0009 values (@c1, @c2, @c3, @c4, @c5);
set @x=@x+1;
select @x;
until @x > num end repeat;
end;
/
call insertdata(4);
@c1
-----
1
(1 row)
@c2
-----
29
(1 row)
@c3
-----
102
(1 row)
@c4
------
1114
(1 row)
@c5
------
amy1
(1 row)
@x
----
2
(1 row)
@c1
-----
2
(1 row)
@c2
-----
29
(1 row)
@c3
-----
102
(1 row)
@c4
------
1114
(1 row)
@c5
------
amy2
(1 row)
@x
----
3
(1 row)
@c1
-----
3
(1 row)
@c2
-----
29
(1 row)
@c3
-----
102
(1 row)
@c4
------
1114
(1 row)
@c5
------
amy3
(1 row)
@x
----
4
(1 row)
@c1
-----
4
(1 row)
@c2
-----
29
(1 row)
@c3
-----
102
(1 row)
@c4
------
1114
(1 row)
@c5
------
amy4
(1 row)
@x
----
5
(1 row)
call insertdata(4);
@c1
-----
1
(1 row)
@c2
-----
29
(1 row)
@c3
-----
102
(1 row)
@c4
------
1114
(1 row)
@c5
------
amy1
(1 row)
@x
----
2
(1 row)
@c1
-----
2
(1 row)
@c2
-----
29
(1 row)
@c3
-----
102
(1 row)
@c4
------
1114
(1 row)
@c5
------
amy2
(1 row)
@x
----
3
(1 row)
@c1
-----
3
(1 row)
@c2
-----
29
(1 row)
@c3
-----
102
(1 row)
@c4
------
1114
(1 row)
@c5
------
amy3
(1 row)
@x
----
4
(1 row)
@c1
-----
4
(1 row)
@c2
-----
29
(1 row)
@c3
-----
102
(1 row)
@c4
------
1114
(1 row)
@c5
------
amy4
(1 row)
@x
----
5
(1 row)
select * from t_tinyint0009 order by c2;
c1 | c2 | c3 | c4 | c5
----+----+-----+------+------
1 | 29 | 102 | 1114 | amy1
2 | 29 | 102 | 1114 | amy2
3 | 29 | 102 | 1114 | amy3
4 | 29 | 102 | 1114 | amy4
(4 rows)
set dolphin.sql_mode=default;
set b_format_behavior_compat_options=enable_set_variables;
create table string_tables(input_string varchar(64));
DROP PROCEDURE IF EXISTS p_define_config_check;
NOTICE: function p_define_config_check() does not exist, skipping
CREATE PROCEDURE p_define_config_check(v_wtg_alias VARCHAR(64))
LANGUAGE 'plpgsql'
AS
DECLARE
v_tmp BIGINT(10) DEFAULT -1;
v_controller_id BIGINT(10) DEFAULT -1;
v_tmp_alias VARCHAR(64);
v_controller_name VARCHAR(64) DEFAULT -1;
BEGIN
-- WGEN.GenActivePW
SELECT COUNT(*) INTO v_tmp
FROM string_tables WHERE input_string = CONCAT(v_wtg_alias, '.WGEN.GenActivePW');
END;
/
set @alias = 'F011';
CALL p_define_config_check(@alias);
p_define_config_check
-----------------------
(1 row)
drop table string_tables;
DROP PROCEDURE IF EXISTS p_define_config_check;
CREATE TABLE point_tbl(f1 point);
SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
count
-------
0
(1 row)
drop table point_tbl;
SELECT @-@ path '((0,0),(1,0))' AS RESULT;
RESULT
--------
2
(1 row)
SELECT point '(1,1)' <@ circle '((0,0),2)' AS RESULT;
RESULT
--------
t
(1 row)
set dolphin.sql_mode='sql_mode_strict,sql_mode_full_group,no_zero_date,block_return_multi_results,error_for_division_by_zero,escape_quotes,disable_escape_bytea';
drop table if exists t_autocommit_0038;
NOTICE: table "t_autocommit_0038" does not exist, skipping
create table t_autocommit_0038(a int, b int);
insert into t_autocommit_0038 values(1, 2);
create or replace procedure p_autocommit_0038_1(a int,b int)
as
declare
num3 int := a;
num4 int := b;
pragma autonomous_transaction;
begin
insert into t_autocommit_0038 values(num3, num4);
end;
/
set autocommit = 0;
drop procedure if exists p_autocommit_0038_2;
NOTICE: function p_autocommit_0038_2() does not exist, skipping
create or replace procedure p_autocommit_0038_2(a int, b int) as
declare
begin
insert into t_autocommit_0038 values(666, 666);
p_autocommit_0038_1(a, b);
rollback;
END;
commit;
/
set autocommit = 0;
select p_autocommit_0038_2(11, 22);
p_autocommit_0038_2
---------------------
(1 row)
select * from t_autocommit_0038 order by a;
a | b
----+----
1 | 2
11 | 22
(2 rows)
rollback;
select * from t_autocommit_0038 order by a;
a | b
----+----
1 | 2
11 | 22
(2 rows)
call p_autocommit_0038_2(11, 22);
commit;
drop table if exists t_autocommit_0038;
drop procedure p_autocommit_0038_1(a int,b int);
drop procedure p_autocommit_0038_2(a int, b int);
reset dolphin.sql_mode;
set dolphin.sql_mode='sql_mode_strict,sql_mode_full_group,no_zero_date,error_for_division_by_zero,escape_quotes,disable_escape_bytea';
set b_format_behavior_compat_options = 'enable_set_variables';
set enable_set_variable_b_format = on;
delimiter //
create or replace procedure test_set(out res int)
begin
declare a int;
declare b int;
declare c int;
set a=1, b=2, c=3;
set res=a+b+c;
end;
//
delimiter ;
call test_set(@res);
select @res;
@res
------
6
(1 row)
drop procedure test_set(out res int);
reset dolphin.sql_mode;
drop schema multi_select_proc cascade;
NOTICE: drop cascades to 26 other objects
DETAIL: drop cascades to table test_1
drop cascades to table t
drop cascades to function proc_a_1()
drop cascades to function proc_a_2()
drop cascades to function proc_a_3(integer)
drop cascades to function proc_b_1(integer)
drop cascades to function proc_b_2(integer)
drop cascades to table tab_1145173
drop cascades to table a_1145173
drop cascades to table b_1145173
drop cascades to function pro_1145173()
drop cascades to table tab_1144052
drop cascades to function pro_1144052()
drop cascades to function fun_1144052()
drop cascades to function proc_a_m()
drop cascades to table testtyp
drop cascades to function proc_a_m1()
drop cascades to function pro_11451713()
drop cascades to table tab_1145533
drop cascades to function pro_1145533()
drop cascades to function pro1_1145533(integer)
drop cascades to function pro2_1145533(integer)
drop cascades to function proc_def_1(integer,integer)
drop cascades to function proc_def_2(integer)
drop cascades to table t_tinyint0009
drop cascades to function insertdata(integer)
reset current_schema;
