NAME

SP::Fn - stored functions in PostgreSQL

SYNOPSIS

   use SP::Fn;
   SP::set_conn(DBI->connect("dbi:Pg:", '', '', { RaiseError => 1 }));

   my $fn = SP::Fn->new(name => 'my_stored_function');
   printf("Arguments and return type are:\n%s\n", $fn->to_s);

   my $result = $fn->call($arg1, $arg2);
   printf("The call returned %d rows.\n", scalar(@{$result}));

DESCRIPTION

SP::Fn provides a convenient way of acquiring information about stored functions and an easy way to call them.

Outline Usage

First tell SP::Fn's supporting module, SP, what your database connection is by calling SP::set_conn().

Then you can create a stored function object by calling SP::Fn->new(). Once you have an object, you can call methods to examine its arguments and return types; to see what SQL you might use to call the function; and to call the function and get its results.

Methods

`SP::Fn->new()`

The constructor takes one of two forms of argument, a name or an oid:

   my $fn = SP::Fn->new(name => 'my_stored_function');
   my $fn = SP::Fn->new(oid => 2071);

While using a name is more natural, it might also be ambiguous due to overloading; in that case, use the oid instead. The constructor will helpfully tell you if you try to use an ambiguous name, for example

   my $fn = SP::Fn->new(name => 'f3');
   =>
   oid 238019: f3 (
      i_a integer,
      i_b integer
      ) RETURNS integer
   oid 238020: f3 (
      i_a character varying,
      i_b character varying
      ) RETURNS character varying
   Ambiguous name, instead use one of the oids above.

If you decide you want the varchar version, construct with SP::Fn->new(oid => 238020).

`$fn->to_s()`

Returns human-readable text showing the function's oid, name, arguments and return type. The constructor uses the same underlying code in response to an ambiguous function name, so the example above includes two examples of output from $fn->to_s().

`$fn->call_sql()`

Returns the SQL that the function would send to PostgreSQL to call the function. Example:

  $fn->call_sql('2010-11-03')
  => "SELECT f5(CAST('2010-11-03' AS DATE))"

`$fn->call()`

Calls the function and returns an arrayref to the returned row (or rows, if a SETOF function). Example returning one row:

  $fn->call('2010-11-03')
  => [ [ '2010-11-04' ] ]

Example returning three rows:

  $fn->call()
  => [ [ 1 ], [ 2 ], [ 3 ] ]