Re: Trigger to run @ connection time? - Mailing list pgsql-general
| From | Kynn Jones |
|---|---|
| Subject | Re: Trigger to run @ connection time? |
| Date | |
| Msg-id | c2350ba40803121220k146cb979r569a312538a9d39b@mail.gmail.com Whole thread Raw |
| In response to | Re: Trigger to run @ connection time? ("Andrej Ricnik-Bay" <andrej.groups@gmail.com>) |
| List | pgsql-general |
On Tue, Mar 11, 2008 at 4:51 PM, Andrej Ricnik-Bay <andrej.groups@gmail.com> wrote:
On 12/03/2008, Kynn Jones <kynnjo@gmail.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 donefor 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
pgsql-general by date: