blob: 9cef3f2bb0b43a33c3d34412b9d939c449fbc51f (
plain)
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
|
-- 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)
|
