1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
CREATE TABLE test_tbl (i integer, s text);
CREATE FUNCTION test_sql() RETURNS integer AS
$$
// for name[] conversion test, add current_schemas()
let rows = pljs.execute("SELECT i, 's' || i AS s, current_schemas(true) AS c FROM generate_series(1, 4) AS t(i)");
for (let r = 0; r < rows.length; r++)
{
let result = pljs.execute("INSERT INTO test_tbl VALUES(" + rows[r].i + ",'" + rows[r].s + "')");
pljs.elog(NOTICE, JSON.stringify(rows[r]), result);
}
return rows.length;
$$
LANGUAGE pljs;
SELECT test_sql();
NOTICE: {"i":1,"s":"s1","c":["pg_catalog","public"]} 1
NOTICE: {"i":2,"s":"s2","c":["pg_catalog","public"]} 1
NOTICE: {"i":3,"s":"s3","c":["pg_catalog","public"]} 1
NOTICE: {"i":4,"s":"s4","c":["pg_catalog","public"]} 1
test_sql
----------
4
(1 row)
SELECT * FROM test_tbl;
i | s
---+----
1 | s1
2 | s2
3 | s3
4 | s4
(4 rows)
-- SPI operations
CREATE FUNCTION prep1() RETURNS void AS $$
let plan = pljs.prepare("SELECT * FROM test_tbl");
pljs.elog(INFO, plan.toString());
let rows = plan.execute();
for(let i = 0; i < rows.length; i++) {
pljs.elog(INFO, JSON.stringify(rows[i]));
}
let cursor = plan.cursor();
pljs.elog(INFO, cursor.toString());
let row;
while(row = cursor.fetch()) {
pljs.elog(INFO, JSON.stringify(row));
}
cursor.close();
cursor = plan.cursor();
rows = cursor.fetch(2);
pljs.elog(INFO, JSON.stringify(rows));
rows = cursor.fetch(-2);
pljs.elog(INFO, JSON.stringify(rows));
cursor.move(1);
rows = cursor.fetch(3);
pljs.elog(INFO, JSON.stringify(rows));
cursor.move(-2);
rows = cursor.fetch(3);
pljs.elog(INFO, JSON.stringify(rows));
cursor.close();
plan.free();
plan = pljs.prepare("SELECT * FROM test_tbl WHERE i = $1 and s = $2", ["int", "text"]);
rows = plan.execute([2, "s2"]);
pljs.elog(INFO, "rows.length = ", rows.length);
cursor = plan.cursor([2, "s2"]);
pljs.elog(INFO, JSON.stringify(cursor.fetch()));
cursor.close();
plan.free();
try{
plan = pljs.prepare("SELECT * FROM test_tbl");
plan.free();
plan.execute();
}catch(e){
pljs.elog(WARNING, e);
}
try{
plan = pljs.prepare("SELECT * FROM test_tbl");
cursor = plan.cursor();
cursor.close();
cursor.fetch();
}catch(e){
pljs.elog(WARNING, e);
}
$$ LANGUAGE pljs STRICT;
SELECT prep1();
INFO: [object Plan]
INFO: {"i":1,"s":"s1"}
INFO: {"i":2,"s":"s2"}
INFO: {"i":3,"s":"s3"}
INFO: {"i":4,"s":"s4"}
INFO: [object Cursor]
INFO: {"i":1,"s":"s1"}
INFO: {"i":2,"s":"s2"}
INFO: {"i":3,"s":"s3"}
INFO: {"i":4,"s":"s4"}
INFO: 2
INFO: 1
INFO: 3
INFO: 2
INFO: rows.length = 1
INFO: {"i":2,"s":"s2"}
WARNING: Error: Invalid plan
WARNING: Error: Unable to find cursor
prep1
-------
(1 row)
|
