316 lines
7.6 KiB
SQL
316 lines
7.6 KiB
SQL
CREATE EXTENSION gms_sql;
|
|
set gms_sql_max_open_cursor_count = 501;
|
|
reset gms_sql_max_open_cursor_count;
|
|
show gms_sql_max_open_cursor_count;
|
|
do $$
|
|
declare
|
|
c int;
|
|
strval varchar;
|
|
intval int;
|
|
nrows int default 30;
|
|
begin
|
|
c := gms_sql.open_cursor();
|
|
gms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, :nrows) g(i)', gms_sql.v6);
|
|
gms_sql.bind_variable(c, 'nrows', nrows);
|
|
gms_sql.define_column(c, 1, strval);
|
|
gms_sql.define_column(c, 2, intval);
|
|
perform gms_sql.execute(c);
|
|
while gms_sql.fetch_rows(c) > 0
|
|
loop
|
|
gms_sql.column_value(c, 1, strval);
|
|
gms_sql.column_value(c, 2, intval);
|
|
raise notice 'c1: %, c2: %', strval, intval;
|
|
end loop;
|
|
gms_sql.close_cursor(c);
|
|
end;
|
|
$$;
|
|
|
|
do $$
|
|
declare
|
|
c int;
|
|
strval varchar;
|
|
intval int;
|
|
nrows int default 30;
|
|
begin
|
|
c := gms_sql.open_cursor();
|
|
gms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, :nrows) g(i)', gms_sql.v7);
|
|
gms_sql.bind_variable(c, 'nrows', nrows);
|
|
gms_sql.define_column(c, 1, strval);
|
|
gms_sql.define_column(c, 2, intval);
|
|
perform gms_sql.execute(c);
|
|
while gms_sql.fetch_rows(c) > 0
|
|
loop
|
|
strval := gms_sql.column_value_f(c, 1, strval);
|
|
intval := gms_sql.column_value_f(c, 2, intval);
|
|
raise notice 'c1: %, c2: %', strval, intval;
|
|
end loop;
|
|
gms_sql.close_cursor(c);
|
|
end;
|
|
$$;
|
|
|
|
drop table if exists foo;
|
|
|
|
create table foo(a int, b varchar, c numeric);
|
|
|
|
do $$
|
|
declare c int;
|
|
begin
|
|
c := gms_sql.open_cursor();
|
|
gms_sql.parse(c, 'insert into foo values(:a, :b, :c)', gms_sql.native);
|
|
for i in 1..100
|
|
loop
|
|
gms_sql.bind_variable(c, 'a', i);
|
|
gms_sql.bind_variable(c, 'b', 'Ahoj ' || i);
|
|
gms_sql.bind_variable(c, 'c', i + 0.033);
|
|
perform gms_sql.execute(c);
|
|
end loop;
|
|
gms_sql.close_cursor(c);
|
|
end;
|
|
$$;
|
|
|
|
select * from foo;
|
|
truncate foo;
|
|
|
|
do $$
|
|
declare c int;
|
|
begin
|
|
c := gms_sql.open_cursor();
|
|
gms_sql.parse(c, 'insert into foo values(:a, :b, :c)', gms_sql.native);
|
|
for i in 1..100
|
|
loop
|
|
gms_sql.bind_variable_f(c, 'a', i);
|
|
gms_sql.bind_variable_f(c, 'b', 'Ahoj ' || i);
|
|
gms_sql.bind_variable_f(c, 'c', i + 0.033);
|
|
perform gms_sql.execute(c);
|
|
end loop;
|
|
gms_sql.close_cursor(c);
|
|
end;
|
|
$$;
|
|
|
|
select * from foo;
|
|
truncate foo;
|
|
|
|
do $$
|
|
declare
|
|
c int;
|
|
a int[];
|
|
b varchar[];
|
|
ca numeric[];
|
|
begin
|
|
c := gms_sql.open_cursor();
|
|
gms_sql.parse(c, 'insert into foo values(:a, :b, :c)', gms_sql.v6);
|
|
a := ARRAY[1, 2, 3, 4, 5];
|
|
b := ARRAY['Ahoj', 'Nazdar', 'Bazar'];
|
|
ca := ARRAY[3.14, 2.22, 3.8, 4];
|
|
|
|
perform gms_sql.bind_array(c, 'a', a);
|
|
perform gms_sql.bind_array(c, 'b', b);
|
|
perform gms_sql.bind_array(c, 'c', ca);
|
|
raise notice 'inserted rows %d', gms_sql.execute(c);
|
|
gms_sql.close_cursor(c);
|
|
end;
|
|
$$;
|
|
|
|
select * from foo;
|
|
truncate foo;
|
|
|
|
do $$
|
|
declare
|
|
c int;
|
|
a int[];
|
|
b varchar[];
|
|
ca numeric[];
|
|
begin
|
|
c := gms_sql.open_cursor();
|
|
gms_sql.parse(c, 'insert into foo values(:a, :b, :c)', gms_sql.v7);
|
|
a := ARRAY[1, 2, 3, 4, 5];
|
|
b := ARRAY['Ahoj', 'Nazdar', 'Bazar'];
|
|
ca := ARRAY[3.14, 2.22, 3.8, 4];
|
|
|
|
perform gms_sql.bind_array(c, 'a', a, 2, 3);
|
|
perform gms_sql.bind_array(c, 'b', b, 3, 4);
|
|
perform gms_sql.bind_array(c, 'c', ca);
|
|
raise notice 'inserted rows %d', gms_sql.execute(c);
|
|
gms_sql.close_cursor(c);
|
|
end;
|
|
$$;
|
|
|
|
select * from foo;
|
|
truncate foo;
|
|
|
|
do $$
|
|
declare
|
|
c int;
|
|
a int[];
|
|
b varchar[];
|
|
ca numeric[];
|
|
begin
|
|
c := gms_sql.open_cursor();
|
|
gms_sql.parse(c, 'select i, ''Ahoj'' || i, i + 0.003 from generate_series(1, 35) g(i)', 0);
|
|
gms_sql.define_array(c, 1, a, 10, 1);
|
|
gms_sql.define_array(c, 2, b, 10, 1);
|
|
gms_sql.define_array(c, 3, ca, 10, 1);
|
|
|
|
perform gms_sql.execute(c);
|
|
while gms_sql.fetch_rows(c) > 0
|
|
loop
|
|
gms_sql.column_value(c, 1, a);
|
|
gms_sql.column_value(c, 2, b);
|
|
gms_sql.column_value(c, 3, ca);
|
|
raise notice 'a = %', a;
|
|
raise notice 'b = %', b;
|
|
raise notice 'c = %', ca;
|
|
end loop;
|
|
gms_sql.close_cursor(c);
|
|
end;
|
|
$$;
|
|
|
|
drop table foo;
|
|
|
|
do $$
|
|
declare
|
|
l_curid int;
|
|
l_cnt int;
|
|
l_desctab gms_sql.desc_tab;
|
|
l_sqltext varchar(2000);
|
|
begin
|
|
l_sqltext='select * from pg_object;';
|
|
l_curid := gms_sql.open_cursor();
|
|
gms_sql.parse(l_curid, l_sqltext, 0);
|
|
gms_sql.describe_columns(l_curid, l_cnt, l_desctab);
|
|
for i in 1 .. l_desctab.count loop
|
|
raise notice '%,% ', l_desctab(i).col_name,l_desctab(i).col_type;
|
|
end loop;
|
|
gms_sql.close_cursor(l_curid);
|
|
end;
|
|
$$;
|
|
|
|
create table t1(id int, name varchar(20));
|
|
insert into t1 select generate_series(1,3), 'abcddd';
|
|
create table t2(a int, b date);
|
|
insert into t2 values(1, '2022-12-11 10:00:01.123');
|
|
insert into t2 values(3, '2022-12-12 12:00:11.13');
|
|
|
|
do $$
|
|
declare
|
|
c1 refcursor;
|
|
c2 refcursor;
|
|
begin
|
|
open c1 for select * from t1;
|
|
gms_sql.return_result(c1);
|
|
open c2 for select * from t2;
|
|
gms_sql.return_result(c2);
|
|
end;
|
|
$$;
|
|
|
|
|
|
create procedure test_result() as
|
|
declare
|
|
c1 refcursor;
|
|
c2 refcursor;
|
|
begin
|
|
open c1 for select * from t1;
|
|
gms_sql.return_result(c1);
|
|
open c2 for select * from t2;
|
|
gms_sql.return_result(c2);
|
|
end;
|
|
/
|
|
call test_result();
|
|
drop procedure test_result;
|
|
|
|
create procedure aam() as
|
|
declare
|
|
id1 int;
|
|
id2 int;
|
|
begin
|
|
id1 :=gms_sql.open_cursor();
|
|
gms_sql.parse(id1,'select * from t1', 1);
|
|
perform gms_sql.execute(id1);
|
|
gms_sql.return_result(id1);
|
|
gms_sql.close_cursor(id1);
|
|
id2 :=gms_sql.open_cursor();
|
|
gms_sql.parse(id2,'select * from t2', 2);
|
|
perform gms_sql.execute(id2);
|
|
gms_sql.return_result(id2);
|
|
gms_sql.close_cursor(id2);
|
|
end;
|
|
/
|
|
call aam();
|
|
drop procedure aam;
|
|
create table col_name_too_long(aaaaabbbbbcccccdddddeeeeefffffggg int, col2 text);
|
|
|
|
do $$
|
|
declare
|
|
l_curid int;
|
|
l_cnt int;
|
|
l_desctab gms_sql.desc_tab;
|
|
l_desctab2 gms_sql.desc_tab2;
|
|
l_sqltext varchar(2000);
|
|
begin
|
|
l_sqltext='select * from t1;';
|
|
l_curid := gms_sql.open_cursor();
|
|
gms_sql.parse(l_curid, l_sqltext, 1);
|
|
gms_sql.describe_columns(l_curid, l_cnt, l_desctab);
|
|
for i in 1 .. l_desctab.count loop
|
|
raise notice '%', l_desctab(i).col_name;
|
|
end loop;
|
|
-- output col_name
|
|
l_sqltext='select * from col_name_too_long;';
|
|
gms_sql.parse(l_curid, l_sqltext, 1);
|
|
gms_sql.describe_columns2(l_curid, l_cnt, l_desctab2);
|
|
for i in 1 .. l_desctab2.count loop
|
|
raise notice '%', l_desctab2(i).col_name;
|
|
end loop;
|
|
-- error
|
|
l_sqltext='select * from col_name_too_long;';
|
|
gms_sql.parse(l_curid, l_sqltext, 1);
|
|
gms_sql.describe_columns(l_curid, l_cnt, l_desctab);
|
|
for i in 1 .. l_desctab.count loop
|
|
raise notice '%', l_desctab(i).col_name;
|
|
end loop;
|
|
end;
|
|
$$;
|
|
select gms_sql.is_open(0);
|
|
select gms_sql.close_cursor(0);
|
|
do $$
|
|
declare
|
|
l_curid int;
|
|
l_cnt int;
|
|
l_desctab3 gms_sql.desc_tab3;
|
|
l_desctab4 gms_sql.desc_tab4;
|
|
l_sqltext varchar(2000);
|
|
begin
|
|
l_sqltext='select * from col_name_too_long;';
|
|
l_curid := gms_sql.open_cursor();
|
|
gms_sql.parse(l_curid, l_sqltext, 1);
|
|
gms_sql.describe_columns3(l_curid, l_cnt, l_desctab3);
|
|
for i in 1 .. l_desctab3.count loop
|
|
raise notice '%,%,%', l_desctab3(i).col_type,l_desctab3(i).col_type_name,l_desctab3(i).col_name;
|
|
end loop;
|
|
gms_sql.parse(l_curid, l_sqltext, 1);
|
|
gms_sql.describe_columns3(l_curid, l_cnt, l_desctab4);
|
|
for i in 1 .. l_desctab4.count loop
|
|
raise notice '%,%,%,%', l_desctab3(i).col_type,l_desctab4(i).col_type_name,l_desctab4(i).col_type_name_len,l_desctab4(i).col_name_len;
|
|
end loop;
|
|
gms_sql.close_cursor(l_curid);
|
|
end;
|
|
$$;
|
|
|
|
drop table t1,t2, col_name_too_long;
|
|
|
|
select gms_sql.open_cursor();
|
|
select gms_sql.is_open(0);
|
|
select gms_sql.open_cursor();
|
|
select gms_sql.is_open(1);
|
|
select gms_sql.open_cursor();
|
|
select gms_sql.is_open(2);
|
|
select gms_sql.open_cursor();
|
|
select gms_sql.is_open(3);
|
|
select gms_sql.close_cursor(0);
|
|
select gms_sql.close_cursor(1);
|
|
select gms_sql.close_cursor(2);
|
|
select gms_sql.close_cursor(3);
|
|
select gms_sql.is_open(3);
|
|
select gms_sql.close_cursor(10000);
|
|
select gms_sql.close_cursor(-1);
|