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"]
301
CREATE OR REPLACE FUNCTION read_file(text) RETURNS text AS '
302
return open(args[0]).read()
303
' LANGUAGE plpythonu;
305
CREATE OR REPLACE FUNCTION write_file(text,text) RETURNS text AS '
306
open(args[0],"w").write(args[1])
307
return "Wrote to file: %s" % args[0]
308
' LANGUAGE plpythonu;
311
-- Universal Newline Support
314
CREATE OR REPLACE FUNCTION newline_lf() RETURNS integer AS
315
'x = 100\ny = 23\nreturn x + y\n'
318
CREATE OR REPLACE FUNCTION newline_cr() RETURNS integer AS
319
'x = 100\ry = 23\rreturn x + y\r'
322
CREATE OR REPLACE FUNCTION newline_crlf() RETURNS integer AS
323
'x = 100\r\ny = 23\r\nreturn x + y\r\n'