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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
-- test regular sql
create function test_select(xuser text, tmp boolean)
returns integer as $x$
cluster 'testcluster';
run on hashtext(xuser);
select /*********
junk ;
********** ****/ id from sel_test where username = xuser
and ';' <> 'as;d''a ; sd'
and $tmp$ ; 'a' $tmp$ <> 'as;d''a ; sd'
and $tmp$ $ $$ $foo$tmp$ <> 'x';
$x$ language plproxy;
\c test_part
create table sel_test (
id integer,
username text
);
insert into sel_test values ( 1, 'user');
\c regression
select * from test_select('user', true);
test_select
-------------
1
(1 row)
select * from test_select('xuser', false);
ERROR: PL/Proxy function public.test_select(2): Non-SETOF function requires 1 row from remote query, got 0
-- test errors
create function test_select_err(xuser text, tmp boolean)
returns integer as $$
cluster 'testcluster';
run on hashtext(xuser);
select id from sel_test where username = xuser;
select id from sel_test where username = xuser;
$$ language plproxy;
ERROR: PL/Proxy function public.test_select_err(2): Compile error at line 5: Only one SELECT statement allowed
select * from test_select_err('user', true);
ERROR: function test_select_err(unknown, boolean) does not exist
LINE 1: select * from test_select_err('user', true);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
create function get_zero()
returns setof integer as $x$
cluster 'testcluster';
run on all;
select (0*0);
$x$ language plproxy;
select * from get_zero();
get_zero
----------
0
(1 row)
\c test_part
create table numbers (
num int,
name text
);
insert into numbers values (1, 'one');
insert into numbers values (2, 'two');
create function ret_numtuple(int)
returns numbers as $x$
select num, name from numbers where num = $1;
$x$ language sql;
\c regression
create type numbers_type as (num int, name text);
create function get_one()
returns setof numbers_type as $x$
cluster 'testcluster';
run on all;
select (ret_numtuple(1)).num, (ret_numtuple(1)).name;
$x$ language plproxy;
select * from get_one();
num | name
-----+------
1 | one
(1 row)
\c test_part
create function remote_func(a varchar, b varchar, c varchar)
returns void as $$
begin
return;
end;
$$ language plpgsql;
\c regression
CREATE OR REPLACE FUNCTION test1(x integer, a varchar, b varchar, c varchar)
RETURNS void AS $$
CLUSTER 'testcluster';
RUN ON 0;
SELECT * FROM remote_func(a, b, c);
$$ LANGUAGE plproxy;
select * from test1(1, 'a', NULL,NULL);
test1
-------
(1 row)
select * from test1(1, NULL, NULL,NULL);
test1
-------
(1 row)
CREATE OR REPLACE FUNCTION test2(a varchar, b varchar, c varchar)
RETURNS void AS $$
CLUSTER 'testcluster';
RUN ON 0;
SELECT * FROM remote_func(a, b, c);
$$ LANGUAGE plproxy;
select * from test2(NULL, NULL, NULL);
test2
-------
(1 row)
select * from test2('a', NULL, NULL);
test2
-------
(1 row)
CREATE OR REPLACE FUNCTION test3(a varchar, b varchar, c varchar)
RETURNS void AS $$
CLUSTER 'testcluster';
RUN ON 0;
SELECT * FROM remote_func(a, c, b);
$$ LANGUAGE plproxy;
select * from test3(NULL,NULL, 'a');
test3
-------
(1 row)
select * from test3('a', NULL,NULL);
test3
-------
(1 row)
|
