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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
|
-- first some tests of basic functionality
--
-- better succeed
--
select stupid();
stupid
--------
zarkon
(1 row)
-- check static and global data
--
SELECT static_test();
static_test
-------------
1
(1 row)
SELECT static_test();
static_test
-------------
2
(1 row)
SELECT global_test_one();
global_test_one
--------------------------------------------------------
SD: set by global_test_one, GD: set by global_test_one
(1 row)
SELECT global_test_two();
global_test_two
--------------------------------------------------------
SD: set by global_test_two, GD: set by global_test_one
(1 row)
-- import python modules
--
SELECT import_fail();
NOTICE: ('import socket failed -- No module named foosocket',)
import_fail
--------------------
failed as expected
(1 row)
SELECT import_succeed();
import_succeed
------------------------
succeeded, as expected
(1 row)
-- test import and simple argument handling
--
SELECT import_test_one('sha hash of this string');
import_test_one
------------------------------------------
a04e23cb9b1a09cd1051a04a7c571aae0f90346c
(1 row)
-- test import and tuple argument handling
--
select import_test_two(users) from users where fname = 'willem';
import_test_two
-------------------------------------------------------------------
sha hash of willemdoe is 3cde6b574953b0ca937b4d76ebc40d534d910759
(1 row)
-- test multiple arguments
--
select argument_test_one(users, fname, lname) from users where lname = 'doe' order by 1;
argument_test_one
-----------------------------------------------------------------------
jane doe => {fname: jane, lname: doe, userid: 1, username: j_doe}
john doe => {fname: john, lname: doe, userid: 2, username: johnd}
willem doe => {fname: willem, lname: doe, userid: 3, username: w_doe}
(3 rows)
-- spi and nested calls
--
select nested_call_one('pass this along');
nested_call_one
-----------------------------------------------------------------
{'nested_call_two': "{'nested_call_three': 'pass this along'}"}
(1 row)
select spi_prepared_plan_test_one('doe');
spi_prepared_plan_test_one
----------------------------
there are 3 does
(1 row)
select spi_prepared_plan_test_one('smith');
spi_prepared_plan_test_one
----------------------------
there are 1 smiths
(1 row)
select spi_prepared_plan_test_nested('smith');
spi_prepared_plan_test_nested
-------------------------------
there are 1 smiths
(1 row)
-- quick peek at the table
--
SELECT * FROM users;
fname | lname | username | userid
--------+-------+----------+--------
jane | doe | j_doe | 1
john | doe | johnd | 2
willem | doe | w_doe | 3
rick | smith | slash | 4
(4 rows)
-- should fail
--
UPDATE users SET fname = 'william' WHERE fname = 'willem';
-- should modify william to willem and create username
--
INSERT INTO users (fname, lname) VALUES ('william', 'smith');
INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle');
SELECT * FROM users;
fname | lname | username | userid
---------+--------+----------+--------
jane | doe | j_doe | 1
john | doe | johnd | 2
willem | doe | w_doe | 3
rick | smith | slash | 4
willem | smith | w_smith | 5
charles | darwin | beagle | 6
(6 rows)
SELECT join_sequences(sequences) FROM sequences;
join_sequences
----------------
ABCDEFGHIJKL
ABCDEF
ABCDEF
ABCDEF
ABCDEF
ABCDEF
(6 rows)
SELECT join_sequences(sequences) FROM sequences
WHERE join_sequences(sequences) ~* '^A';
join_sequences
----------------
ABCDEFGHIJKL
ABCDEF
ABCDEF
ABCDEF
ABCDEF
ABCDEF
(6 rows)
SELECT join_sequences(sequences) FROM sequences
WHERE join_sequences(sequences) ~* '^B';
join_sequences
----------------
(0 rows)
-- error in trigger
--
--
-- Check Universal Newline Support
--
SELECT newline_lf();
newline_lf
------------
123
(1 row)
SELECT newline_cr();
newline_cr
------------
123
(1 row)
SELECT newline_crlf();
newline_crlf
--------------
123
(1 row)
|
