--
-- nested calls
--
CREATE FUNCTION nested_call_one(a text) RETURNS text
	AS
'q = "SELECT nested_call_two(''%s'')" % a
r = plpy.execute(q)
return r[0]'
	LANGUAGE plpythonu ;
CREATE FUNCTION nested_call_two(a text) RETURNS text
	AS
'q = "SELECT nested_call_three(''%s'')" % a
r = plpy.execute(q)
return r[0]'
	LANGUAGE plpythonu ;
CREATE FUNCTION nested_call_three(a text) RETURNS text
	AS
'return a'
	LANGUAGE plpythonu ;
-- some spi stuff
CREATE FUNCTION spi_prepared_plan_test_one(a text) RETURNS text
	AS
'if "myplan" not in SD:
	q = "SELECT count(*) FROM users WHERE lname = $1"
	SD["myplan"] = plpy.prepare(q, [ "text" ])
try:
	rv = plpy.execute(SD["myplan"], [a])
	return "there are " + str(rv[0]["count"]) + " " + str(a) + "s"
except Exception, ex:
	plpy.error(str(ex))
return None
'
	LANGUAGE plpythonu;
CREATE FUNCTION spi_prepared_plan_test_nested(a text) RETURNS text
	AS
'if "myplan" not in SD:
	q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % a
	SD["myplan"] = plpy.prepare(q)
try:
	rv = plpy.execute(SD["myplan"])
	if len(rv):
		return rv[0]["count"]
except Exception, ex:
	plpy.error(str(ex))
return None
'
	LANGUAGE plpythonu;
CREATE FUNCTION join_sequences(s sequences) RETURNS text
	AS
'if not s["multipart"]:
	return s["sequence"]
q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % s["pid"]
rv = plpy.execute(q)
seq = s["sequence"]
for r in rv:
	seq = seq + r["sequence"]
return seq
'
	LANGUAGE plpythonu;
-- 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)

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)

--
-- plan and result objects
--
CREATE FUNCTION result_nrows_test() RETURNS int
AS $$
plan = plpy.prepare("SELECT 1 UNION SELECT 2")
plpy.info(plan.status()) # not really documented or useful
result = plpy.execute(plan)
if result.status() > 0:
   return result.nrows()
else:
   return None
$$ LANGUAGE plpythonu;
SELECT result_nrows_test();
INFO:  True
CONTEXT:  PL/Python function "result_nrows_test"
 result_nrows_test 
-------------------
                 2
(1 row)