On Fri, Feb 12, 2010 at 11:10:15PM +0000, Tim Bunce wrote:
> I've appended the POD documentation and attached the (rough but working)
> test script.
Oops. Here's the test script.
Tim.
create or replace function test_call() returns text language plperlu as $func$
use lib "/Users/timbo/pg/PostgreSQL-PLPerl-Call/lib";
use PostgreSQL::PLPerl::Call;
use Test::More 'no_plan';
my $row;
my @ary;
# ====== single-value single-row function ======
# --- no arguments
like call('pi()'), qr/^3.14159/;
# bad calls
eval { call('pi()', 42) };
like $@, qr/expected 0 argument/;
eval { call('pi', 42) };
like $@, qr/Can't parse 'pi'/; # error from call() itself
# --- one argument, simple types
is call('abs(int)', -42), 42;
is call('abs(float)', -42.5), '42.5';
is call('bit_length(text)', 'jose'), 32;
# --- one argument, multi-word types
is call('abs(double precision)', -42.5), '42.5';
is call('bit_length(character varying(90))', 'jose'), 32;
# --- lock calls
call('pg_try_advisory_lock_shared(bigint)', 1234);
call('pg_advisory_unlock_all()');
# bad calls
eval { call('abs(int)', -42.5) };
like $@, qr/invalid input syntax for integer/;
eval { call('abs(text)', -42.5) };
like $@, qr/function abs\(text\) does not exist/;
eval { call('abs(nonesuchtype)', -42.5) };
like $@, qr/type "nonesuchtype" does not exist/;
# --- multi-argument, simple types
is call('trunc(numeric,int)', 42.4382, 2), '42.43';
# --- unusual types from strings
is call('host(inet)', '192.168.1.5/24'), '192.168.1.5';
is call('network(inet)', '192.168.1.5/24'), '192.168.1.0/24';
is call('abbrev(cidr)', '10.1.0.0/16'), '10.1/16';
is call('numnode(tsquery)', '(fat & rat) | cat'), 5;
spi_exec_query('create temp sequence seqn1 start with 42');
is call('nextval(regclass)', 'seqn1'), 42;
is call('nextval(text)', 'seqn1'), 43;
is call('string_to_array(text, text)', 'xx~^~yy~^~zz', '~^~'), '{xx,yy,zz}';
# --- array and array reference handling
is call('array_dims(text[])', '{a,b,c}'), '[1:3]';
is call('array_dims(text[])', [qw(a b c)]), '[1:3]';
is call('array_dims(text[])', [[1,2,3], [4,5,6]]), '[1:2][1:3]';
is call('array_cat(int[], int[])', [1,2,3], [2,1]), '{1,2,3,2,1}';
# ====== single-value multi-row function ======
@ary = call('unnest(int[])', '{11,12,13}');
is scalar @ary, 3;
is_deeply \...@ary, [ 11, 12, 13 ];
@ary = call('generate_series(int,int)', 10, 19);
is scalar @ary, 10;
is_deeply \...@ary, [ 10..19 ];
@ary = call('generate_series(int,int,int)', 10, 19, 4);
is_deeply \...@ary, [ 10, 14, 18 ];
@ary = call('generate_series(timestamp,timestamp,interval)', '2008-03-01',
'2008-03-02', '12 hours');
is_deeply \...@ary, [ '2008-03-01 00:00:00', '2008-03-01 12:00:00', '2008-03-02
00:00:00' ];
# bad calls
eval { scalar call('generate_series(int,int)', 10, 19) };
like $@, qr/returned more than one row/;
# ====== multi-value (record) returning functions ======
@ary = call('pg_get_keywords()');
cmp_ok scalar @ary, '>', 200;
ok $row = $ary[0];
is ref $row, 'HASH';
ok exists $row->{word}, 'should contain a word column';
ok exists $row->{catcode}, 'should contain a catcode column';
ok exists $row->{catdesc}, 'should contain a catdesc column';
# single-record
spi_exec_query(q{
create or replace function f1(out r1 text, out r2 int) language plperl
as $$
return { r1=>10, r2=>11 };
$$
});
@ary = call('f1()');
is scalar @ary, 1;
ok $row = $ary[0];
is $row->{r1}, 10;
is $row->{r2}, 11;
spi_exec_query('drop function f1()');
# multi-record
spi_exec_query(q{
create or replace function f2() returns table (r1 text, r2 int)
language plperl as $$
return_next { r1 => $_, r2 => $_+1 } for 1..5;
return undef;
$$
});
@ary = call('f2()');
is scalar @ary, 5;
is $ary[-1]->{r1}, 5;
is $ary[-1]->{r2}, 6;
spi_exec_query('drop function f2()');
# ====== functions with defaults or varargs ======
spi_exec_query(q{
create or replace function f3(int default 42) returns int language
plperl as $$
return shift() + 1;
$$
});
is call('f3()'), 43;
spi_exec_query('drop function f3(int)');
spi_exec_query(q{
create or replace function f4(VARIADIC numeric[]) returns float
language plperlu as $$
use PostgreSQL::PLPerl::Call;
my $sum;
$sum += $_ for call('unnest(numeric[])', $_[0]);
return $sum;
$$
});
# call varadic with explicit number of args in the signature
is call('f4(numeric, numeric)', 10,11 ), 21;
is call('f4(numeric, numeric, numeric)', 10,11,12), 33;
# call varadic using '...' in the signature
is call('f4(numeric, numeric ...)', 10,11,12), 33;
is call('f4(numeric ...)', 10,11,12), 33;
is call('f4(numeric ...)', 10,11 ), 21;
is call('f4(numeric ...)', 10 ), 10;
# XXX doesn't work with no args - possibly natural consequence
#is call('f4(numeric ...)' ), undef;
spi_exec_query('drop function f4(varadic numeric[])');
# === finish up
Test::More->builder->_ending;
return undef;
$func$;
select * from test_call();
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
