path: root/test/expected/plproxy_test.out

\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