\set VERBOSITY terse -- test normal function create function testfunc(username text, id integer, data text) returns text as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create function testfunc(username text, id integer, data text) returns text as $$ begin return 'username=' || username; end; $$ language plpgsql; \c regression select * from testfunc('user', 1, 'foo'); testfunc --------------- username=user (1 row) select * from testfunc('user', 1, 'foo'); testfunc --------------- username=user (1 row) select * from testfunc('user', 1, 'foo'); testfunc --------------- username=user (1 row) -- test setof text create function test_set(username text, num integer) returns setof text as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create function test_set(username text, num integer) returns setof text as $$ declare i integer; begin i := 0; while i < num loop return next 'username=' || username || ' row=' || i; i := i + 1; end loop; return; end; $$ language plpgsql; \c regression select * from test_set('user', 1); test_set --------------------- username=user row=0 (1 row) select * from test_set('user', 0); test_set ---------- (0 rows) select * from test_set('user', 3); test_set --------------------- username=user row=0 username=user row=1 username=user row=2 (3 rows) -- test record create type ret_test_rec as ( id integer, dat text); create function test_record(username text, num integer) returns ret_test_rec as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create type ret_test_rec as ( id integer, dat text); create function test_record(username text, num integer) returns ret_test_rec as $$ declare ret ret_test_rec%rowtype; begin ret := (num, username); return ret; end; $$ language plpgsql; \c regression select * from test_record('user', 3); id | dat ----+------ 3 | user (1 row) -- test setof record create function test_record_set(username text, num integer) returns setof ret_test_rec as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create function test_record_set(username text, num integer) returns setof ret_test_rec as $$ declare ret ret_test_rec%rowtype; i integer; begin i := 0; while i < num loop ret := (i, username); i := i + 1; return next ret; end loop; return; end; $$ language plpgsql; \c regression select * from test_record_set('user', 1); id | dat ----+------ 0 | user (1 row) select * from test_record_set('user', 0); id | dat ----+----- (0 rows) select * from test_record_set('user', 3); id | dat ----+------ 0 | user 1 | user 2 | user (3 rows) -- test void create function test_void(username text, num integer) returns void as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create function test_void(username text, num integer) returns void as $$ begin return; end; $$ language plpgsql; -- look what void actually looks select * from test_void('void', 2); test_void ----------- (1 row) select test_void('void', 2); test_void ----------- (1 row) \c regression select * from test_void('user', 1); test_void ----------- (1 row) select * from test_void('user', 3); test_void ----------- (1 row) select test_void('user', 3); test_void ----------- (1 row) select test_void('user', 3); test_void ----------- (1 row) -- test normal outargs create function test_out1(username text, id integer, out data text) as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create function test_out1(username text, id integer, out data text) returns text as $$ begin data := 'username=' || username; return; end; $$ language plpgsql; \c regression select * from test_out1('user', 1); data --------------- username=user (1 row) -- test complicated outargs create function test_out2(username text, id integer, out out_id integer, xdata text, inout xdata2 text, out odata text) as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create function test_out2(username text, id integer, out out_id integer, xdata text, inout xdata2 text, out odata text) as $$ begin out_id = id; xdata2 := xdata2 || xdata; odata := 'username=' || username; return; end; $$ language plpgsql; \c regression select * from test_out2('user', 1, 'xdata', 'xdata2'); out_id | xdata2 | odata --------+-------------+--------------- 1 | xdata2xdata | username=user (1 row) -- test various types create function test_types(username text, inout vbool boolean, inout xdate timestamp, inout bin bytea) as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create function test_types(username text, inout vbool boolean, inout xdate timestamp, inout bin bytea) as $$ begin return; end; $$ language plpgsql; \c regression select 1 from (select set_config(name, 'escape', false) as ignore from pg_settings where name = 'bytea_output') x where x.ignore = 'foo'; ?column? ---------- (0 rows) select * from test_types('types', true, '2009-11-04 12:12:02', E'a\\000\\001\\002b'); vbool | xdate | bin -------+--------------------------+---------------- t | Wed Nov 04 12:12:02 2009 | a\000\001\002b (1 row) select * from test_types('types', NULL, NULL, NULL); vbool | xdate | bin -------+-------+----- | | (1 row) -- test user defined types create domain posint as int4 check (value > 0); create type struct as (id int4, data text); create function test_types2(username text, inout v_posint posint, inout v_struct struct, inout arr int8[]) as $$ cluster 'testcluster'; $$ language plproxy; \c test_part create domain posint as int4 check (value > 0); create type struct as (id int4, data text); create function test_types2(username text, inout v_posint posint, inout v_struct struct, inout arr int8[]) as $$ begin return; end; $$ language plpgsql; \c regression select * from test_types2('types', 4, (2, 'asd'), array[1,2,3]); v_posint | v_struct | arr ----------+----------+--------- 4 | (2,asd) | {1,2,3} (1 row) select * from test_types2('types', NULL, NULL, NULL); v_posint | v_struct | arr ----------+----------+----- | | (1 row) -- test CONNECT create function test_connect1() returns text as $$ connect 'dbname=test_part'; select current_database(); $$ language plproxy; select * from test_connect1(); test_connect1 --------------- test_part (1 row) -- test CONNECT $argument create function test_connect2(connstr text) returns text as $$ connect connstr; select current_database(); $$ language plproxy; select * from test_connect2('dbname=test_part'); test_connect2 --------------- test_part (1 row) -- test CONNECT function($argument) create function test_connect3(connstr text) returns text as $$ connect text(connstr); select current_database(); $$ language plproxy; select * from test_connect3('dbname=test_part'); test_connect3 --------------- test_part (1 row) -- test quoting function create type "RetWeird" as ( "ColId" int4, "ColData" text ); create function "testQuoting"(username text, id integer, data text) returns "RetWeird" as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create type "RetWeird" as ( "ColId" int4, "ColData" text ); create function "testQuoting"(username text, id integer, data text) returns "RetWeird" as $$ select 1::int4, 'BazOoka'::text $$ language sql; \c regression select * from "testQuoting"('user', '1', 'dat'); ColId | ColData -------+--------- 1 | BazOoka (1 row) -- test arg type quoting create domain "bad type" as text; create function test_argq(username text, "some arg" integer, "other arg" "bad type", out "bad out" text, out "bad out2" "bad type") as $$ cluster 'testcluster'; run on hashtext(username); $$ language plproxy; \c test_part create domain "bad type" as text; create function test_argq(username text, "some arg" integer, "other arg" "bad type", out "bad out" text, out "bad out2" "bad type") as $$ begin return; end; $$ language plpgsql; \c regression select * from test_argq('user', 1, 'q'); bad out | bad out2 ---------+---------- | (1 row) -- test hash types function create or replace function t_hash16(int4) returns int2 as $$ declare res int2; begin res = $1::int2; return res; end; $$ language plpgsql; create or replace function t_hash64(int4) returns int8 as $$ declare res int8; begin res = $1; return res; end; $$ language plpgsql; create function test_hash16(id integer, data text) returns text as $$ cluster 'testcluster'; run on t_hash16(id); select data; $$ language plproxy; select * from test_hash16('0', 'hash16'); test_hash16 ------------- hash16 (1 row) create function test_hash64(id integer, data text) returns text as $$ cluster 'testcluster'; run on t_hash64(id); select data; $$ language plproxy; select * from test_hash64('0', 'hash64'); test_hash64 ------------- hash64 (1 row) -- test argument difference \c test_part create function test_difftypes(username text, out val1 int2, out val2 float8) as $$ begin val1 = 1; val2 = 3;return; end; $$ language plpgsql; \c regression create function test_difftypes(username text, out val1 int4, out val2 float4) as $$ cluster 'testcluster'; run on 0; $$ language plproxy; select * from test_difftypes('types'); val1 | val2 ------+------ 1 | 3 (1 row) -- test simple hash \c test_part create function test_simple(partno int4) returns int4 as $$ begin return $1; end; $$ language plpgsql; \c regression create function test_simple(partno int4) returns int4 as $$ cluster 'testcluster'; run on $1; $$ language plproxy; select * from test_simple(0); test_simple ------------- 0 (1 row) drop function test_simple(int4); create function test_simple(partno int4) returns int4 as $$ cluster 'testcluster'; run on partno; $$ language plproxy; select * from test_simple(0); test_simple ------------- 0 (1 row) -- test error passing \c test_part create function test_error1() returns int4 as $$ begin select line2err; return 0; end; $$ language plpgsql; \c regression create function test_error1() returns int4 as $$ cluster 'testcluster'; run on 0; $$ language plproxy; select * from test_error1(); ERROR: public.test_error1(0): [test_part] REMOTE ERROR: column "line2err" does not exist at character 8 create function test_error2() returns int4 as $$ cluster 'testcluster'; run on 0; select err; $$ language plproxy; select * from test_error2(); NOTICE: PL/Proxy: dropping stale conn ERROR: public.test_error2(0): [test_part] REMOTE ERROR: column "err" does not exist at character 8 create function test_error3() returns int4 as $$ connect 'dbname=test_part'; $$ language plproxy; select * from test_error3(); ERROR: public.test_error3(0): [test_part] REMOTE ERROR: function public.test_error3() does not exist at character 21 -- test invalid db create function test_bad_db() returns int4 as $$ cluster 'badcluster'; $$ language plproxy; select * from test_bad_db(); ERROR: PL/Proxy function public.test_bad_db(0): [nonex_db] PQconnectPoll: FATAL: database "nonex_db" does not exist create function test_bad_db2() returns int4 as $$ connect 'dbname=wrong_name_db'; $$ language plproxy; select * from test_bad_db2(); ERROR: PL/Proxy function public.test_bad_db2(0): [wrong_name_db] PQconnectPoll: FATAL: database "wrong_name_db" does not exist