Re: Trigger to run @ connection time?

On Tue, Mar 11, 2008 at 4:51 PM, Andrej Ricnik-Bay <andrej(dot)groups(at)gmail(dot)com>
wrote:

> On 12/03/2008, Kynn Jones <kynnjo(at)gmail(dot)com> wrote:
> Of course I may not have quite
> understood how that "this procedure adds useful definitions,
> mostly subs, to Perl's main package. This needs to be done
> for each connection" is meant to work.
>

What I mean is illustrated by the following (extremely artificial and
clumsy) example:

CREATE OR REPLACE FUNCTION setup_perl () RETURNS void
AS $PERL$

# globals
$::TRUE = 1;
$::FALSE = 0;

{
my $leading_ws = qr/\A\s*/;
my $trailing_ws = qr/\s*\z/;

# The next assignment defines the Perl function main::trim();
# it has almost the same effect as writing
# sub trim { ... }
# at the top level scope (in the main package), except that
# the definition happens at run time rather than at compile
# time.
*trim = sub {
local $_ = shift;
s/$leading_ws//;
s/$trailing_ws//;
return $_;
};
}
$PERL$ LANGUAGE plperl IMMUTABLE;

CREATE OR REPLACE FUNCTION is_foo ( TEXT ) RETURNS BOOLEAN
AS $PERL$
return trim( shift ) eq 'foo' ? $::TRUE : $::FALSE;
$PERL$ LANGUAGE plperl IMMUTABLE;

CREATE OR REPLACE FUNCTION is_bar ( TEXT ) RETURNS BOOLEAN
AS $PERL$
return trim( shift ) eq 'bar' ? $::TRUE : $::FALSE;
$PERL$ LANGUAGE plperl IMMUTABLE;

Notice that is_foo() and is_bar() both rely on the *perl* function trim.
They also refer to the Perl global variables $::TRUE and $::FALSE. This
technique facilitates the reuse of Perl code in two ways. First, individual
Perl subroutines can be defined once and called from various PLPERL
procedures. Second, it simplifies the cut-and-paste porting of Perl code
(which often uses subroutines and global or file-scoped lexical variables)
straight into to PLPERL. (I wrote more about this technique recently, in
the post with the subject line "On defining Perl functions within PLPERL
code.")

(BTW, notice that, the function trim is actually a closure: it uses a couple
of lexical variables, $leading_ws and $trailing_ws, that are defined in the
enclosing scope; i.e. these definitions need to happen only once. Such
variables serve the same purpose as that of C static variables. The ease of
defining such closures is an added bonus of this technique. In this
artificial example, of course, this benefit is negligible, but when the
computation of such constants is time-consuming, this could be a useful
little optimization.)

Now, note that if we try to use is_foo() before invoking perl_setup(), it
will fail:

my_db=> select is_foo( ' foo ' );
ERROR: error from Perl function: Undefined subroutine &main::trim called at
line 2.

my_db=> select setup_perl();
setup_perl
------------

(1 row)

(BTW, is there a way to avoid the useless output above?)

my_db=> select is_foo( ' foo ' );
is_foo
--------
t
(1 row)

my_db=> select is_bar( ' foo ' );
is_bar
--------
f
(1 row)

That's why it would be nice to run perl_setup() automatically at the
beginning of each session. Granted, one workaround would be to include the
line

spi_query( 'SELECT setup_perl()' ) unless $::TRUE;

at the top of ever PLPERL function that required the definitions provided by
setup_perl(). Something like an ON CONNECT trigger would obviate this small
annoyance, but I guess that's not a possibility at the moment.

Kynn