754 lines
18 KiB
Plaintext
754 lines
18 KiB
Plaintext
CREATE EXTENSION gms_sql;
|
|
set gms_sql_max_open_cursor_count = 501;
|
|
ERROR: 501 is outside the valid range for parameter "gms_sql_max_open_cursor_count" (10 .. 500)
|
|
reset gms_sql_max_open_cursor_count;
|
|
show gms_sql_max_open_cursor_count;
|
|
gms_sql_max_open_cursor_count
|
|
-------------------------------
|
|
100
|
|
(1 row)
|
|
|
|
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;
|
|
$$;
|
|
NOTICE: c1: ahoj1, c2: 1
|
|
NOTICE: c1: ahoj2, c2: 2
|
|
NOTICE: c1: ahoj3, c2: 3
|
|
NOTICE: c1: ahoj4, c2: 4
|
|
NOTICE: c1: ahoj5, c2: 5
|
|
NOTICE: c1: ahoj6, c2: 6
|
|
NOTICE: c1: ahoj7, c2: 7
|
|
NOTICE: c1: ahoj8, c2: 8
|
|
NOTICE: c1: ahoj9, c2: 9
|
|
NOTICE: c1: ahoj10, c2: 10
|
|
NOTICE: c1: ahoj11, c2: 11
|
|
NOTICE: c1: ahoj12, c2: 12
|
|
NOTICE: c1: ahoj13, c2: 13
|
|
NOTICE: c1: ahoj14, c2: 14
|
|
NOTICE: c1: ahoj15, c2: 15
|
|
NOTICE: c1: ahoj16, c2: 16
|
|
NOTICE: c1: ahoj17, c2: 17
|
|
NOTICE: c1: ahoj18, c2: 18
|
|
NOTICE: c1: ahoj19, c2: 19
|
|
NOTICE: c1: ahoj20, c2: 20
|
|
NOTICE: c1: ahoj21, c2: 21
|
|
NOTICE: c1: ahoj22, c2: 22
|
|
NOTICE: c1: ahoj23, c2: 23
|
|
NOTICE: c1: ahoj24, c2: 24
|
|
NOTICE: c1: ahoj25, c2: 25
|
|
NOTICE: c1: ahoj26, c2: 26
|
|
NOTICE: c1: ahoj27, c2: 27
|
|
NOTICE: c1: ahoj28, c2: 28
|
|
NOTICE: c1: ahoj29, c2: 29
|
|
NOTICE: c1: ahoj30, c2: 30
|
|
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;
|
|
$$;
|
|
NOTICE: c1: ahoj1, c2: 1
|
|
NOTICE: c1: ahoj2, c2: 2
|
|
NOTICE: c1: ahoj3, c2: 3
|
|
NOTICE: c1: ahoj4, c2: 4
|
|
NOTICE: c1: ahoj5, c2: 5
|
|
NOTICE: c1: ahoj6, c2: 6
|
|
NOTICE: c1: ahoj7, c2: 7
|
|
NOTICE: c1: ahoj8, c2: 8
|
|
NOTICE: c1: ahoj9, c2: 9
|
|
NOTICE: c1: ahoj10, c2: 10
|
|
NOTICE: c1: ahoj11, c2: 11
|
|
NOTICE: c1: ahoj12, c2: 12
|
|
NOTICE: c1: ahoj13, c2: 13
|
|
NOTICE: c1: ahoj14, c2: 14
|
|
NOTICE: c1: ahoj15, c2: 15
|
|
NOTICE: c1: ahoj16, c2: 16
|
|
NOTICE: c1: ahoj17, c2: 17
|
|
NOTICE: c1: ahoj18, c2: 18
|
|
NOTICE: c1: ahoj19, c2: 19
|
|
NOTICE: c1: ahoj20, c2: 20
|
|
NOTICE: c1: ahoj21, c2: 21
|
|
NOTICE: c1: ahoj22, c2: 22
|
|
NOTICE: c1: ahoj23, c2: 23
|
|
NOTICE: c1: ahoj24, c2: 24
|
|
NOTICE: c1: ahoj25, c2: 25
|
|
NOTICE: c1: ahoj26, c2: 26
|
|
NOTICE: c1: ahoj27, c2: 27
|
|
NOTICE: c1: ahoj28, c2: 28
|
|
NOTICE: c1: ahoj29, c2: 29
|
|
NOTICE: c1: ahoj30, c2: 30
|
|
drop table if exists foo;
|
|
NOTICE: table "foo" does not exist, skipping
|
|
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;
|
|
a | b | c
|
|
-----+----------+---------
|
|
1 | Ahoj 1 | 1.033
|
|
2 | Ahoj 2 | 2.033
|
|
3 | Ahoj 3 | 3.033
|
|
4 | Ahoj 4 | 4.033
|
|
5 | Ahoj 5 | 5.033
|
|
6 | Ahoj 6 | 6.033
|
|
7 | Ahoj 7 | 7.033
|
|
8 | Ahoj 8 | 8.033
|
|
9 | Ahoj 9 | 9.033
|
|
10 | Ahoj 10 | 10.033
|
|
11 | Ahoj 11 | 11.033
|
|
12 | Ahoj 12 | 12.033
|
|
13 | Ahoj 13 | 13.033
|
|
14 | Ahoj 14 | 14.033
|
|
15 | Ahoj 15 | 15.033
|
|
16 | Ahoj 16 | 16.033
|
|
17 | Ahoj 17 | 17.033
|
|
18 | Ahoj 18 | 18.033
|
|
19 | Ahoj 19 | 19.033
|
|
20 | Ahoj 20 | 20.033
|
|
21 | Ahoj 21 | 21.033
|
|
22 | Ahoj 22 | 22.033
|
|
23 | Ahoj 23 | 23.033
|
|
24 | Ahoj 24 | 24.033
|
|
25 | Ahoj 25 | 25.033
|
|
26 | Ahoj 26 | 26.033
|
|
27 | Ahoj 27 | 27.033
|
|
28 | Ahoj 28 | 28.033
|
|
29 | Ahoj 29 | 29.033
|
|
30 | Ahoj 30 | 30.033
|
|
31 | Ahoj 31 | 31.033
|
|
32 | Ahoj 32 | 32.033
|
|
33 | Ahoj 33 | 33.033
|
|
34 | Ahoj 34 | 34.033
|
|
35 | Ahoj 35 | 35.033
|
|
36 | Ahoj 36 | 36.033
|
|
37 | Ahoj 37 | 37.033
|
|
38 | Ahoj 38 | 38.033
|
|
39 | Ahoj 39 | 39.033
|
|
40 | Ahoj 40 | 40.033
|
|
41 | Ahoj 41 | 41.033
|
|
42 | Ahoj 42 | 42.033
|
|
43 | Ahoj 43 | 43.033
|
|
44 | Ahoj 44 | 44.033
|
|
45 | Ahoj 45 | 45.033
|
|
46 | Ahoj 46 | 46.033
|
|
47 | Ahoj 47 | 47.033
|
|
48 | Ahoj 48 | 48.033
|
|
49 | Ahoj 49 | 49.033
|
|
50 | Ahoj 50 | 50.033
|
|
51 | Ahoj 51 | 51.033
|
|
52 | Ahoj 52 | 52.033
|
|
53 | Ahoj 53 | 53.033
|
|
54 | Ahoj 54 | 54.033
|
|
55 | Ahoj 55 | 55.033
|
|
56 | Ahoj 56 | 56.033
|
|
57 | Ahoj 57 | 57.033
|
|
58 | Ahoj 58 | 58.033
|
|
59 | Ahoj 59 | 59.033
|
|
60 | Ahoj 60 | 60.033
|
|
61 | Ahoj 61 | 61.033
|
|
62 | Ahoj 62 | 62.033
|
|
63 | Ahoj 63 | 63.033
|
|
64 | Ahoj 64 | 64.033
|
|
65 | Ahoj 65 | 65.033
|
|
66 | Ahoj 66 | 66.033
|
|
67 | Ahoj 67 | 67.033
|
|
68 | Ahoj 68 | 68.033
|
|
69 | Ahoj 69 | 69.033
|
|
70 | Ahoj 70 | 70.033
|
|
71 | Ahoj 71 | 71.033
|
|
72 | Ahoj 72 | 72.033
|
|
73 | Ahoj 73 | 73.033
|
|
74 | Ahoj 74 | 74.033
|
|
75 | Ahoj 75 | 75.033
|
|
76 | Ahoj 76 | 76.033
|
|
77 | Ahoj 77 | 77.033
|
|
78 | Ahoj 78 | 78.033
|
|
79 | Ahoj 79 | 79.033
|
|
80 | Ahoj 80 | 80.033
|
|
81 | Ahoj 81 | 81.033
|
|
82 | Ahoj 82 | 82.033
|
|
83 | Ahoj 83 | 83.033
|
|
84 | Ahoj 84 | 84.033
|
|
85 | Ahoj 85 | 85.033
|
|
86 | Ahoj 86 | 86.033
|
|
87 | Ahoj 87 | 87.033
|
|
88 | Ahoj 88 | 88.033
|
|
89 | Ahoj 89 | 89.033
|
|
90 | Ahoj 90 | 90.033
|
|
91 | Ahoj 91 | 91.033
|
|
92 | Ahoj 92 | 92.033
|
|
93 | Ahoj 93 | 93.033
|
|
94 | Ahoj 94 | 94.033
|
|
95 | Ahoj 95 | 95.033
|
|
96 | Ahoj 96 | 96.033
|
|
97 | Ahoj 97 | 97.033
|
|
98 | Ahoj 98 | 98.033
|
|
99 | Ahoj 99 | 99.033
|
|
100 | Ahoj 100 | 100.033
|
|
(100 rows)
|
|
|
|
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;
|
|
a | b | c
|
|
-----+----------+---------
|
|
1 | Ahoj 1 | 1.033
|
|
2 | Ahoj 2 | 2.033
|
|
3 | Ahoj 3 | 3.033
|
|
4 | Ahoj 4 | 4.033
|
|
5 | Ahoj 5 | 5.033
|
|
6 | Ahoj 6 | 6.033
|
|
7 | Ahoj 7 | 7.033
|
|
8 | Ahoj 8 | 8.033
|
|
9 | Ahoj 9 | 9.033
|
|
10 | Ahoj 10 | 10.033
|
|
11 | Ahoj 11 | 11.033
|
|
12 | Ahoj 12 | 12.033
|
|
13 | Ahoj 13 | 13.033
|
|
14 | Ahoj 14 | 14.033
|
|
15 | Ahoj 15 | 15.033
|
|
16 | Ahoj 16 | 16.033
|
|
17 | Ahoj 17 | 17.033
|
|
18 | Ahoj 18 | 18.033
|
|
19 | Ahoj 19 | 19.033
|
|
20 | Ahoj 20 | 20.033
|
|
21 | Ahoj 21 | 21.033
|
|
22 | Ahoj 22 | 22.033
|
|
23 | Ahoj 23 | 23.033
|
|
24 | Ahoj 24 | 24.033
|
|
25 | Ahoj 25 | 25.033
|
|
26 | Ahoj 26 | 26.033
|
|
27 | Ahoj 27 | 27.033
|
|
28 | Ahoj 28 | 28.033
|
|
29 | Ahoj 29 | 29.033
|
|
30 | Ahoj 30 | 30.033
|
|
31 | Ahoj 31 | 31.033
|
|
32 | Ahoj 32 | 32.033
|
|
33 | Ahoj 33 | 33.033
|
|
34 | Ahoj 34 | 34.033
|
|
35 | Ahoj 35 | 35.033
|
|
36 | Ahoj 36 | 36.033
|
|
37 | Ahoj 37 | 37.033
|
|
38 | Ahoj 38 | 38.033
|
|
39 | Ahoj 39 | 39.033
|
|
40 | Ahoj 40 | 40.033
|
|
41 | Ahoj 41 | 41.033
|
|
42 | Ahoj 42 | 42.033
|
|
43 | Ahoj 43 | 43.033
|
|
44 | Ahoj 44 | 44.033
|
|
45 | Ahoj 45 | 45.033
|
|
46 | Ahoj 46 | 46.033
|
|
47 | Ahoj 47 | 47.033
|
|
48 | Ahoj 48 | 48.033
|
|
49 | Ahoj 49 | 49.033
|
|
50 | Ahoj 50 | 50.033
|
|
51 | Ahoj 51 | 51.033
|
|
52 | Ahoj 52 | 52.033
|
|
53 | Ahoj 53 | 53.033
|
|
54 | Ahoj 54 | 54.033
|
|
55 | Ahoj 55 | 55.033
|
|
56 | Ahoj 56 | 56.033
|
|
57 | Ahoj 57 | 57.033
|
|
58 | Ahoj 58 | 58.033
|
|
59 | Ahoj 59 | 59.033
|
|
60 | Ahoj 60 | 60.033
|
|
61 | Ahoj 61 | 61.033
|
|
62 | Ahoj 62 | 62.033
|
|
63 | Ahoj 63 | 63.033
|
|
64 | Ahoj 64 | 64.033
|
|
65 | Ahoj 65 | 65.033
|
|
66 | Ahoj 66 | 66.033
|
|
67 | Ahoj 67 | 67.033
|
|
68 | Ahoj 68 | 68.033
|
|
69 | Ahoj 69 | 69.033
|
|
70 | Ahoj 70 | 70.033
|
|
71 | Ahoj 71 | 71.033
|
|
72 | Ahoj 72 | 72.033
|
|
73 | Ahoj 73 | 73.033
|
|
74 | Ahoj 74 | 74.033
|
|
75 | Ahoj 75 | 75.033
|
|
76 | Ahoj 76 | 76.033
|
|
77 | Ahoj 77 | 77.033
|
|
78 | Ahoj 78 | 78.033
|
|
79 | Ahoj 79 | 79.033
|
|
80 | Ahoj 80 | 80.033
|
|
81 | Ahoj 81 | 81.033
|
|
82 | Ahoj 82 | 82.033
|
|
83 | Ahoj 83 | 83.033
|
|
84 | Ahoj 84 | 84.033
|
|
85 | Ahoj 85 | 85.033
|
|
86 | Ahoj 86 | 86.033
|
|
87 | Ahoj 87 | 87.033
|
|
88 | Ahoj 88 | 88.033
|
|
89 | Ahoj 89 | 89.033
|
|
90 | Ahoj 90 | 90.033
|
|
91 | Ahoj 91 | 91.033
|
|
92 | Ahoj 92 | 92.033
|
|
93 | Ahoj 93 | 93.033
|
|
94 | Ahoj 94 | 94.033
|
|
95 | Ahoj 95 | 95.033
|
|
96 | Ahoj 96 | 96.033
|
|
97 | Ahoj 97 | 97.033
|
|
98 | Ahoj 98 | 98.033
|
|
99 | Ahoj 99 | 99.033
|
|
100 | Ahoj 100 | 100.033
|
|
(100 rows)
|
|
|
|
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;
|
|
$$;
|
|
NOTICE: inserted rows 3d
|
|
select * from foo;
|
|
a | b | c
|
|
---+--------+------
|
|
1 | Ahoj | 3.14
|
|
2 | Nazdar | 2.22
|
|
3 | Bazar | 3.8
|
|
(3 rows)
|
|
|
|
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;
|
|
$$;
|
|
NOTICE: inserted rows 1d
|
|
select * from foo;
|
|
a | b | c
|
|
---+-------+-----
|
|
3 | Bazar | 3.8
|
|
(1 row)
|
|
|
|
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;
|
|
$$;
|
|
NOTICE: a = {1,2,3,4,5,6,7,8,9,10}
|
|
NOTICE: b = {Ahoj1,Ahoj2,Ahoj3,Ahoj4,Ahoj5,Ahoj6,Ahoj7,Ahoj8,Ahoj9,Ahoj10}
|
|
NOTICE: c = {1.003,2.003,3.003,4.003,5.003,6.003,7.003,8.003,9.003,10.003}
|
|
NOTICE: a = {11,12,13,14,15,16,17,18,19,20}
|
|
NOTICE: b = {Ahoj11,Ahoj12,Ahoj13,Ahoj14,Ahoj15,Ahoj16,Ahoj17,Ahoj18,Ahoj19,Ahoj20}
|
|
NOTICE: c = {11.003,12.003,13.003,14.003,15.003,16.003,17.003,18.003,19.003,20.003}
|
|
NOTICE: a = {21,22,23,24,25,26,27,28,29,30}
|
|
NOTICE: b = {Ahoj21,Ahoj22,Ahoj23,Ahoj24,Ahoj25,Ahoj26,Ahoj27,Ahoj28,Ahoj29,Ahoj30}
|
|
NOTICE: c = {21.003,22.003,23.003,24.003,25.003,26.003,27.003,28.003,29.003,30.003}
|
|
NOTICE: a = {31,32,33,34,35}
|
|
NOTICE: b = {Ahoj31,Ahoj32,Ahoj33,Ahoj34,Ahoj35}
|
|
NOTICE: c = {31.003,32.003,33.003,34.003,35.003}
|
|
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;
|
|
$$;
|
|
NOTICE: object_oid,109
|
|
NOTICE: object_type,96
|
|
NOTICE: creator,109
|
|
NOTICE: ctime,181
|
|
NOTICE: mtime,181
|
|
NOTICE: createcsn,2
|
|
NOTICE: changecsn,2
|
|
NOTICE: valid,109
|
|
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;
|
|
$$;
|
|
ResultSet #1
|
|
|
|
id | name
|
|
----+--------
|
|
1 | abcddd
|
|
2 | abcddd
|
|
3 | abcddd
|
|
(3 rows)
|
|
|
|
ResultSet #2
|
|
|
|
a | b
|
|
---+--------------------------
|
|
1 | Sun Dec 11 10:00:01 2022
|
|
3 | Mon Dec 12 12:00:11 2022
|
|
(2 rows)
|
|
|
|
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();
|
|
ResultSet #1
|
|
|
|
id | name
|
|
----+--------
|
|
1 | abcddd
|
|
2 | abcddd
|
|
3 | abcddd
|
|
(3 rows)
|
|
|
|
ResultSet #2
|
|
|
|
a | b
|
|
---+--------------------------
|
|
1 | Sun Dec 11 10:00:01 2022
|
|
3 | Mon Dec 12 12:00:11 2022
|
|
(2 rows)
|
|
|
|
test_result
|
|
-------------
|
|
|
|
(1 row)
|
|
|
|
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();
|
|
ResultSet #1
|
|
|
|
id | name
|
|
----+--------
|
|
1 | abcddd
|
|
2 | abcddd
|
|
3 | abcddd
|
|
(3 rows)
|
|
|
|
ResultSet #2
|
|
|
|
a | b
|
|
---+--------------------------
|
|
1 | Sun Dec 11 10:00:01 2022
|
|
3 | Mon Dec 12 12:00:11 2022
|
|
(2 rows)
|
|
|
|
aam
|
|
-----
|
|
|
|
(1 row)
|
|
|
|
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;
|
|
$$;
|
|
NOTICE: id
|
|
NOTICE: name
|
|
NOTICE: aaaaabbbbbcccccdddddeeeeefffffggg
|
|
NOTICE: col2
|
|
ERROR: desc_rec.col_name(33) is more than 32
|
|
CONTEXT: SQL statement "CALL gms_sql.describe_columns(l_curid,l_cnt,l_desctab)"
|
|
PL/pgSQL function inline_code_block line 26 at SQL statement
|
|
select gms_sql.is_open(0);
|
|
is_open
|
|
---------
|
|
t
|
|
(1 row)
|
|
|
|
select gms_sql.close_cursor(0);
|
|
close_cursor
|
|
--------------
|
|
|
|
(1 row)
|
|
|
|
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;
|
|
$$;
|
|
NOTICE: 2,<NULL>,aaaaabbbbbcccccdddddeeeeefffffggg
|
|
NOTICE: 109,text,col2
|
|
NOTICE: 2,<NULL>,<NULL>,33
|
|
NOTICE: 109,text,4,4
|
|
drop table t1,t2, col_name_too_long;
|
|
select gms_sql.open_cursor();
|
|
open_cursor
|
|
-------------
|
|
0
|
|
(1 row)
|
|
|
|
select gms_sql.is_open(0);
|
|
is_open
|
|
---------
|
|
t
|
|
(1 row)
|
|
|
|
select gms_sql.open_cursor();
|
|
open_cursor
|
|
-------------
|
|
1
|
|
(1 row)
|
|
|
|
select gms_sql.is_open(1);
|
|
is_open
|
|
---------
|
|
t
|
|
(1 row)
|
|
|
|
select gms_sql.open_cursor();
|
|
open_cursor
|
|
-------------
|
|
2
|
|
(1 row)
|
|
|
|
select gms_sql.is_open(2);
|
|
is_open
|
|
---------
|
|
t
|
|
(1 row)
|
|
|
|
select gms_sql.open_cursor();
|
|
open_cursor
|
|
-------------
|
|
3
|
|
(1 row)
|
|
|
|
select gms_sql.is_open(3);
|
|
is_open
|
|
---------
|
|
t
|
|
(1 row)
|
|
|
|
select gms_sql.close_cursor(0);
|
|
close_cursor
|
|
--------------
|
|
|
|
(1 row)
|
|
|
|
select gms_sql.close_cursor(1);
|
|
close_cursor
|
|
--------------
|
|
|
|
(1 row)
|
|
|
|
select gms_sql.close_cursor(2);
|
|
close_cursor
|
|
--------------
|
|
|
|
(1 row)
|
|
|
|
select gms_sql.close_cursor(3);
|
|
close_cursor
|
|
--------------
|
|
|
|
(1 row)
|
|
|
|
select gms_sql.is_open(3);
|
|
is_open
|
|
---------
|
|
f
|
|
(1 row)
|
|
|
|
select gms_sql.close_cursor(10000);
|
|
ERROR: cursor 10000 value of cursor id is out of range
|
|
CONTEXT: referenced column: close_cursor
|
|
select gms_sql.close_cursor(-1);
|
|
ERROR: cursor -1 value of cursor id is out of range
|
|
CONTEXT: referenced column: close_cursor
|