3 CREATE FUNCTION global_test_one() returns text
5 'if not SD.has_key("global_test"):
6 SD["global_test"] = "set by global_test_one"
7 if not GD.has_key("global_test"):
8 GD["global_test"] = "set by global_test_one"
9 return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]'
12 CREATE FUNCTION global_test_two() returns text
14 'if not SD.has_key("global_test"):
15 SD["global_test"] = "set by global_test_two"
16 if not GD.has_key("global_test"):
17 GD["global_test"] = "set by global_test_two"
18 return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]'
22 CREATE FUNCTION static_test() returns int4
24 'if SD.has_key("call"):
25 SD["call"] = SD["call"] + 1
32 -- import python modules
34 CREATE FUNCTION import_fail() returns text
39 plpy.notice("import socket failed -- %s" % str(ex))
40 return "failed as expected"
41 return "succeeded, that wasn''t supposed to happen"'
45 CREATE FUNCTION import_succeed() returns text
63 plpy.notice("import failed -- %s" % str(ex))
64 return "failed, that wasn''t supposed to happen"
65 return "succeeded, as expected"'
68 CREATE FUNCTION import_test_one(text) RETURNS text
71 digest = sha.new(args[0])
72 return digest.hexdigest()'
75 CREATE FUNCTION import_test_two(users) RETURNS text
78 plain = args[0]["fname"] + args[0]["lname"]
79 digest = sha.new(plain);
80 return "sha hash of " + plain + " is " + digest.hexdigest()'
83 CREATE FUNCTION argument_test_one(users, text, text) RETURNS text
85 'keys = args[0].keys()
89 out.append("%s: %s" % (key, args[0][key]))
90 words = args[1] + " " + args[2] + " => {" + ", ".join(out) + "}"
95 -- these triggers are dedicated to HPHC of RI who
96 -- decided that my kid's name was william not willem, and
97 -- vigorously resisted all efforts at correction. they have
98 -- since gone bankrupt...
100 CREATE FUNCTION users_insert() returns trigger
102 'if TD["new"]["fname"] == None or TD["new"]["lname"] == None:
104 if TD["new"]["username"] == None:
105 TD["new"]["username"] = TD["new"]["fname"][:1] + "_" + TD["new"]["lname"]
109 if TD["new"]["fname"] == "william":
110 TD["new"]["fname"] = TD["args"][0]
116 CREATE FUNCTION users_update() returns trigger
118 'if TD["event"] == "UPDATE":
119 if TD["old"]["fname"] != TD["new"]["fname"] and TD["old"]["fname"] == TD["args"][0]:
125 CREATE FUNCTION users_delete() RETURNS trigger
127 'if TD["old"]["fname"] == TD["args"][0]:
133 CREATE TRIGGER users_insert_trig BEFORE INSERT ON users FOR EACH ROW
134 EXECUTE PROCEDURE users_insert ('willem');
136 CREATE TRIGGER users_update_trig BEFORE UPDATE ON users FOR EACH ROW
137 EXECUTE PROCEDURE users_update ('willem');
139 CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW
140 EXECUTE PROCEDURE users_delete ('willem');
146 CREATE FUNCTION nested_call_one(text) RETURNS text
148 'q = "SELECT nested_call_two(''%s'')" % args[0]
153 CREATE FUNCTION nested_call_two(text) RETURNS text
155 'q = "SELECT nested_call_three(''%s'')" % args[0]
160 CREATE FUNCTION nested_call_three(text) RETURNS text
167 CREATE FUNCTION spi_prepared_plan_test_one(text) RETURNS text
169 'if not SD.has_key("myplan"):
170 q = "SELECT count(*) FROM users WHERE lname = $1"
171 SD["myplan"] = plpy.prepare(q, [ "text" ])
173 rv = plpy.execute(SD["myplan"], [args[0]])
174 return "there are " + str(rv[0]["count"]) + " " + str(args[0]) + "s"
175 except Exception, ex:
181 CREATE FUNCTION spi_prepared_plan_test_nested(text) RETURNS text
183 'if not SD.has_key("myplan"):
184 q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % args[0]
185 SD["myplan"] = plpy.prepare(q)
187 rv = plpy.execute(SD["myplan"])
189 return rv[0]["count"]
190 except Exception, ex:
197 /* really stupid function just to get the module loaded
199 CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpythonu;
203 CREATE FUNCTION invalid_type_uncaught(text) RETURNS text
205 'if not SD.has_key("plan"):
206 q = "SELECT fname FROM users WHERE lname = $1"
207 SD["plan"] = plpy.prepare(q, [ "test" ])
208 rv = plpy.execute(SD["plan"], [ args[0] ])
210 return rv[0]["fname"]
215 /* for what it's worth catch the exception generated by
216 * the typo, and return None
218 CREATE FUNCTION invalid_type_caught(text) RETURNS text
220 'if not SD.has_key("plan"):
221 q = "SELECT fname FROM users WHERE lname = $1"
223 SD["plan"] = plpy.prepare(q, [ "test" ])
224 except plpy.SPIError, ex:
227 rv = plpy.execute(SD["plan"], [ args[0] ])
229 return rv[0]["fname"]
234 /* for what it's worth catch the exception generated by
235 * the typo, and reraise it as a plain error
237 CREATE FUNCTION invalid_type_reraised(text) RETURNS text
239 'if not SD.has_key("plan"):
240 q = "SELECT fname FROM users WHERE lname = $1"
242 SD["plan"] = plpy.prepare(q, [ "test" ])
243 except plpy.SPIError, ex:
245 rv = plpy.execute(SD["plan"], [ args[0] ])
247 return rv[0]["fname"]
253 /* no typo no messing about
255 CREATE FUNCTION valid_type(text) RETURNS text
257 'if not SD.has_key("plan"):
258 SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ])
259 rv = plpy.execute(SD["plan"], [ args[0] ])
261 return rv[0]["fname"]
265 /* Flat out syntax error
267 CREATE FUNCTION sql_syntax_error() RETURNS text
269 'plpy.execute("syntax error")'
272 /* check the handling of uncaught python exceptions
274 CREATE FUNCTION exception_index_invalid(text) RETURNS text
279 /* check handling of nested exceptions
281 CREATE FUNCTION exception_index_invalid_nested() RETURNS text
283 'rv = plpy.execute("SELECT test5(''foo'')")
288 CREATE FUNCTION join_sequences(sequences) RETURNS text
290 'if not args[0]["multipart"]:
291 return args[0]["sequence"]
292 q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % args[0]["pid"]
294 seq = args[0]["sequence"]
296 seq = seq + r["sequence"]
302 -- Universal Newline Support
305 CREATE OR REPLACE FUNCTION newline_lf() RETURNS integer AS
306 'x = 100\ny = 23\nreturn x + y\n'
309 CREATE OR REPLACE FUNCTION newline_cr() RETURNS integer AS
310 'x = 100\ry = 23\rreturn x + y\r'
313 CREATE OR REPLACE FUNCTION newline_crlf() RETURNS integer AS
314 'x = 100\r\ny = 23\r\nreturn x + y\r\n'