4 CREATE FUNCTION nested_call_one(a text) RETURNS text
6 'q = "SELECT nested_call_two(''%s'')" % a
10 CREATE FUNCTION nested_call_two(a text) RETURNS text
12 'q = "SELECT nested_call_three(''%s'')" % a
16 CREATE FUNCTION nested_call_three(a text) RETURNS text
21 CREATE FUNCTION spi_prepared_plan_test_one(a text) RETURNS text
23 'if "myplan" not in SD:
24 q = "SELECT count(*) FROM users WHERE lname = $1"
25 SD["myplan"] = plpy.prepare(q, [ "text" ])
27 rv = plpy.execute(SD["myplan"], [a])
28 return "there are " + str(rv[0]["count"]) + " " + str(a) + "s"
34 CREATE FUNCTION spi_prepared_plan_test_nested(a text) RETURNS text
36 'if "myplan" not in SD:
37 q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % a
38 SD["myplan"] = plpy.prepare(q)
40 rv = plpy.execute(SD["myplan"])
48 CREATE FUNCTION join_sequences(s sequences) RETURNS text
50 'if not s["multipart"]:
52 q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % s["pid"]
56 seq = seq + r["sequence"]
60 CREATE FUNCTION spi_recursive_sum(a int) RETURNS int
64 r = plpy.execute("SELECT spi_recursive_sum(%d) as a" % (a-1))[0]["a"]
69 -- spi and nested calls
71 select nested_call_one('pass this along');
73 -----------------------------------------------------------------
74 {'nested_call_two': "{'nested_call_three': 'pass this along'}"}
77 select spi_prepared_plan_test_one('doe');
78 spi_prepared_plan_test_one
79 ----------------------------
83 select spi_prepared_plan_test_one('smith');
84 spi_prepared_plan_test_one
85 ----------------------------
89 select spi_prepared_plan_test_nested('smith');
90 spi_prepared_plan_test_nested
91 -------------------------------
95 SELECT join_sequences(sequences) FROM sequences;
106 SELECT join_sequences(sequences) FROM sequences
107 WHERE join_sequences(sequences) ~* '^A';
118 SELECT join_sequences(sequences) FROM sequences
119 WHERE join_sequences(sequences) ~* '^B';
124 SELECT spi_recursive_sum(10);
131 -- plan and result objects
133 CREATE FUNCTION result_metadata_test(cmd text) RETURNS int
135 plan = plpy.prepare(cmd)
136 plpy.info(plan.status()) # not really documented or useful
137 result = plpy.execute(plan)
138 if result.status() > 0:
139 plpy.info(result.colnames())
140 plpy.info(result.coltypes())
141 plpy.info(result.coltypmods())
142 return result.nrows()
145 $$ LANGUAGE plpythonu;
146 SELECT result_metadata_test($$SELECT 1 AS foo, '11'::text AS bar UNION SELECT 2, '22'$$);
152 ----------------------
156 SELECT result_metadata_test($$CREATE TEMPORARY TABLE foo1 (a int, b text)$$);
158 ERROR: plpy.Error: command did not produce a result set
159 CONTEXT: Traceback (most recent call last):
160 PL/Python function "result_metadata_test", line 6, in <module>
161 plpy.info(result.colnames())
162 PL/Python function "result_metadata_test"
163 CREATE FUNCTION result_nrows_test(cmd text) RETURNS int
165 result = plpy.execute(cmd)
166 return result.nrows()
167 $$ LANGUAGE plpythonu;
168 SELECT result_nrows_test($$SELECT 1$$);
174 SELECT result_nrows_test($$CREATE TEMPORARY TABLE foo2 (a int, b text)$$);
180 SELECT result_nrows_test($$INSERT INTO foo2 VALUES (1, 'one'), (2, 'two')$$);
186 SELECT result_nrows_test($$UPDATE foo2 SET b = '' WHERE a = 2$$);
192 CREATE FUNCTION result_len_test(cmd text) RETURNS int
194 result = plpy.execute(cmd)
196 $$ LANGUAGE plpythonu;
197 SELECT result_len_test($$SELECT 1$$);
203 SELECT result_len_test($$CREATE TEMPORARY TABLE foo3 (a int, b text)$$);
209 SELECT result_len_test($$INSERT INTO foo3 VALUES (1, 'one'), (2, 'two')$$);
215 SELECT result_len_test($$UPDATE foo3 SET b= '' WHERE a = 2$$);
221 CREATE FUNCTION result_subscript_test() RETURNS void
223 result = plpy.execute("SELECT 1 AS c UNION SELECT 2 "
224 "UNION SELECT 3 UNION SELECT 4")
226 plpy.info(result[1]['c'])
227 plpy.info(result[-1]['c'])
229 plpy.info([item['c'] for item in result[1:3]])
230 plpy.info([item['c'] for item in result[::2]])
232 result[-1] = {'c': 1000}
233 result[:2] = [{'c': 10}, {'c': 100}]
234 plpy.info([item['c'] for item in result[:]])
236 # raises TypeError, but the message differs on Python 2.6, so silence it
238 plpy.info(result['foo'])
242 assert False, "TypeError not raised"
244 $$ LANGUAGE plpythonu;
245 SELECT result_subscript_test();
250 INFO: [10, 100, 3, 1000]
251 result_subscript_test
252 -----------------------
256 CREATE FUNCTION result_empty_test() RETURNS void
258 result = plpy.execute("select 1 where false")
262 $$ LANGUAGE plpythonu;
263 SELECT result_empty_test();
270 CREATE FUNCTION result_str_test(cmd text) RETURNS text
272 plan = plpy.prepare(cmd)
273 result = plpy.execute(plan)
275 $$ LANGUAGE plpythonu;
276 SELECT result_str_test($$SELECT 1 AS foo UNION SELECT 2$$);
278 ------------------------------------------------------------
279 <PLyResult status=5 nrows=2 rows=[{'foo': 1}, {'foo': 2}]>
282 SELECT result_str_test($$CREATE TEMPORARY TABLE foo1 (a int, b text)$$);
284 --------------------------------------
285 <PLyResult status=4 nrows=0 rows=[]>
289 CREATE FUNCTION simple_cursor_test() RETURNS int AS $$
290 res = plpy.cursor("select fname, lname from users")
293 if row['lname'] == 'doe':
296 $$ LANGUAGE plpythonu;
297 CREATE FUNCTION double_cursor_close() RETURNS int AS $$
298 res = plpy.cursor("select fname, lname from users")
301 $$ LANGUAGE plpythonu;
302 CREATE FUNCTION cursor_fetch() RETURNS int AS $$
303 res = plpy.cursor("select fname, lname from users")
304 assert len(res.fetch(3)) == 3
305 assert len(res.fetch(3)) == 1
306 assert len(res.fetch(3)) == 0
307 assert len(res.fetch(3)) == 0
309 # use next() or __next__(), the method name changed in
310 # http://www.python.org/dev/peps/pep-3114/
313 except AttributeError:
315 except StopIteration:
318 assert False, "StopIteration not raised"
319 $$ LANGUAGE plpythonu;
320 CREATE FUNCTION cursor_mix_next_and_fetch() RETURNS int AS $$
321 res = plpy.cursor("select fname, lname from users order by fname")
322 assert len(res.fetch(2)) == 2
327 except AttributeError:
328 item = res.__next__()
329 assert item['fname'] == 'rick'
331 assert len(res.fetch(2)) == 1
332 $$ LANGUAGE plpythonu;
333 CREATE FUNCTION fetch_after_close() RETURNS int AS $$
334 res = plpy.cursor("select fname, lname from users")
341 assert False, "ValueError not raised"
342 $$ LANGUAGE plpythonu;
343 CREATE FUNCTION next_after_close() RETURNS int AS $$
344 res = plpy.cursor("select fname, lname from users")
349 except AttributeError:
354 assert False, "ValueError not raised"
355 $$ LANGUAGE plpythonu;
356 CREATE FUNCTION cursor_fetch_next_empty() RETURNS int AS $$
357 res = plpy.cursor("select fname, lname from users where false")
358 assert len(res.fetch(1)) == 0
362 except AttributeError:
364 except StopIteration:
367 assert False, "StopIteration not raised"
368 $$ LANGUAGE plpythonu;
369 CREATE FUNCTION cursor_plan() RETURNS SETOF text AS $$
371 "select fname, lname from users where fname like $1 || '%' order by fname",
373 for row in plpy.cursor(plan, ["w"]):
375 for row in plpy.cursor(plan, ["j"]):
377 $$ LANGUAGE plpythonu;
378 CREATE FUNCTION cursor_plan_wrong_args() RETURNS SETOF text AS $$
379 plan = plpy.prepare("select fname, lname from users where fname like $1 || '%'",
381 c = plpy.cursor(plan, ["a", "b"])
382 $$ LANGUAGE plpythonu;
383 CREATE TYPE test_composite_type AS (
387 CREATE OR REPLACE FUNCTION plan_composite_args() RETURNS test_composite_type AS $$
388 plan = plpy.prepare("select $1 as c1", ["test_composite_type"])
389 res = plpy.execute(plan, [{"a1": 3, "a2": "label"}])
391 $$ LANGUAGE plpythonu;
392 SELECT simple_cursor_test();
398 SELECT double_cursor_close();
400 ---------------------
404 SELECT cursor_fetch();
410 SELECT cursor_mix_next_and_fetch();
411 cursor_mix_next_and_fetch
412 ---------------------------
416 SELECT fetch_after_close();
422 SELECT next_after_close();
428 SELECT cursor_fetch_next_empty();
429 cursor_fetch_next_empty
430 -------------------------
434 SELECT cursor_plan();
442 SELECT cursor_plan_wrong_args();
443 ERROR: TypeError: Expected sequence of 1 argument, got 2: ['a', 'b']
444 CONTEXT: Traceback (most recent call last):
445 PL/Python function "cursor_plan_wrong_args", line 4, in <module>
446 c = plpy.cursor(plan, ["a", "b"])
447 PL/Python function "cursor_plan_wrong_args"
448 SELECT plan_composite_args();
450 ---------------------