From c4bab5f29bacc1fa95e9cac245d188e8a811386f Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 25 Jan 2002 19:13:15 +0000 Subject: [PATCH] Overhaul plperl documentation. --- doc/src/sgml/plperl.sgml | 355 ++++++++++++++++++++++++++------------- 1 file changed, 240 insertions(+), 115 deletions(-) diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 1d4d31337f..8fb6f36074 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -1,60 +1,75 @@ - - PL/Perl - Perl Procedural Language - - - PL/Perl - - - - Perl - - - - Introduction - - - PL/Perl allows you to write functions in the Perl programming language that may - be used in SQL queries as if they were built into - PostgreSQL. - - - - The PL/Perl interpreter (when installed as trusted interpreter with - default name plperl) is a full Perl interpreter. However, certain - operations have been disabled in order to maintain the security of - the system. In general, the operations that are restricted are - those that interact with the environment. This includes file handle - operations, require, and use - (for external modules). It should be noted that this security is - not absolute. Indeed, several Denial-of-Service attacks are still - possible - memory exhaustion and endless loops are two examples. - - - - When PL/Perl is installed as untrusted interpreter (with name plperlu), - everything is permitted, and any Perl code can be loaded (by a superuser only). - + + PL/Perl - Perl Procedural Language + + + PL/Perl + + + + Perl + + + + PL/Perl is a loadable procedural language + that enables the Perl programming + language to be used to write + PostgreSQL functions. + + + + + + Overview + + + Normally, PL/Perl is installed as a trusted programming + language named plperl. In this setup, certain Perl + operations are disabled to preserve security. In general, the operations + that are restricted are those that interact with the environment. This + includes file handle operations, require, and + use (for external modules). + There is no way to access internals of the + database backend or to gain OS-level access under the permissions of the + PostgreSQL user ID, as a C function can do. + Thus, any unprivileged database user may be + permitted to use this language. + + + Sometimes it is desirable to write Perl functions that are not restricted + --- for example, one might want a Perl function that sends + mail. To handle these cases, PL/Perl can also be installed as an + untrusted language (usually named plperlu). + In this case the full Perl language is available. The writer of a PL/PerlU + function must take care that the function cannot be used to do anything + unwanted, since it will be able to do anything that could be done by + a user logged in as the database administrator. Note that the database + system allows only database superusers to create functions in untrusted + languages. + - Building and Installing + Building and Installing PL/Perl - In order to build and install PL/Perl if you are installing - PostgreSQL from source then the - must be supplied to the + If the option was supplied to the configure - configure script. PL/Perl requires that, when - Perl was installed, the + configure script, + the PostgreSQL build process will attempt to + build the PL/Perl shared library and install it in the + PostgreSQL library directory. + + + + On most platforms, since PL/Perl is a shared library, the libperl - libperl library was build as a shared object. - At the time of this writing, this is almost never the case in the - Perl packages that are distributed with the operating systems. A + libperl library must be a shared library also. + At the time of this writing, this is almost never the case in prebuilt + Perl packages. If this difficulty arises in your situation, a message like this will appear during the build to point out this fact: @@ -64,14 +79,14 @@ $Header: /cvsroot/pgsql/doc/src/sgml/plperl.sgml,v 2.14 2002/01/08 16:13:41 pete *** the documentation for details. - Therefore it is likely that you will have to re-build and install + If you see this, you will have to re-build and install Perl manually to be able to build - PL/Perl. + PL/Perl. During the configuration process for + Perl, request a shared library. - When you want to retry to build PL/Perl after having reinstalled - Perl, then change to the directory + After having reinstalled Perl, change to the directory src/pl/plperl in the PostgreSQL source tree and issue the commands @@ -79,88 +94,131 @@ gmake clean gmake all gmake install + to complete the build and installation of the PL/Perl shared library. - - The createlang command is used to install the - language into a database. - -$ createlang plperl template1 - - Alternatively, to create untrusted interpreter (where functions can only -be created by a superuser, but the functions are not restricted), use: - -$ createlang plperlu template1 - - If it is installed into template1, all future databases will have - the language installed automatically. - - + + To install + PL/Perl and/or PL/PerlU in a particular database, use the + createlang script, for example + createlang plperl dbname or + createlang plperlu dbname. + - - Using PL/Perl + + + If a language is installed into template1, all subsequently + created databases will have the language installed automatically. + + + - - Assume you have the following table: - + + + + Description + + + PL/Perl Functions and Arguments + + + To create a function in the PL/Perl language, use the standard syntax + + +CREATE FUNCTION funcname (argument-types) RETURNS return-type AS ' + # PL/Perl function body +' LANGUAGE plperl; + + + PL/PerlU is the same, except that the language should be specified as + plperlu. + + + + The body of the function is ordinary Perl code. Arguments and + results are handled as in any other Perl subroutine: arguments + are passed in @_, and a result value is returned + with return or as the last expression evaluated in the + function. For example, a function + returning the greater of two integer values could be defined as: + + +CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS ' + if ($_[0] > $_[1]) { return $_[0]; } + return $_[1]; +' LANGUAGE plperl; + + + If a NULL is passed to a function, the argument value will appear + as undefined in Perl. The above function definition will + not behave very nicely with NULL inputs (in fact, it will act as + though they are zeroes). We could add WITH (isStrict) + to the function definition to make PostgreSQL + do something more reasonable: if a NULL is passed, the + function will not be called at all, but will just return a NULL + result automatically. Alternatively, we could check for undefined + inputs in the function body. For example, suppose that we wanted perl_max + with one null and one non-null argument to return the non-null + argument, rather than NULL: + + +CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS ' + my ($a,$b) = @_; + if (! defined $a) { + if (! defined $b) { return undef; } + return $b; + } + if (! defined $b) { return $a; } + if ($a > $b) { return $a; } + return $b; +' LANGUAGE plperl; + + + + + As shown above, + to return a NULL from a PL/Perl function, return an undefined + value. This can be done whether the function is strict or not. + + + + Composite-type arguments are passed to the function as references to + hashes. The keys of the hash are the attribute names of the composite + type. Here is an example: + + CREATE TABLE employee ( name text, basesalary integer, bonus integer ); - - - In order to get the total compensation (base + bonus) we could - define a function as follows: - -CREATE FUNCTION totalcomp(integer, integer) RETURNS integer - AS 'return $_[0] + $_[1]' - LANGUAGE plperl; - - Notice that the arguments to the function are passed in - @_ as might be expected. - - - - We can now use our function like so: - -SELECT name, totalcomp(basesalary, bonus) FROM employee; - - - - - But, we can also pass entire tuples to our functions: - CREATE FUNCTION empcomp(employee) RETURNS integer AS ' - my $emp = shift; + my ($emp) = @_; return $emp->{''basesalary''} + $emp->{''bonus''}; ' LANGUAGE plperl; - - A tuple is passed as a reference to a hash. The keys are the names - of the fields in the tuples. The hash values are values of the - corresponding fields in the tuple. - + +SELECT name, empcomp(employee) FROM employee; + + + + + There is not currently any support for returning a composite-type + result value. + Because the function body is passed as an SQL string literal to - CREATE FUNCTION you have to escape single - quotes within your Perl source, either by doubling them as shown - above, or by using the extended quoting functions + CREATE FUNCTION, you have to escape single + quotes and backslashes within your Perl source, typically by doubling them + as shown in the above example. Another possible approach is to + avoid writing single quotes by using Perl's extended quoting functions (q[], qq[], - qw[]). Backslashes must be escaped by doubling - them. + qw[]). - - The new function empcomp can be used like: - -SELECT name, empcomp(employee) FROM employee; - - - Here is an example of a function that will not work because file system operations are not allowed for security reasons: @@ -174,9 +232,29 @@ CREATE FUNCTION badfunc() RETURNS integer AS ' The creation of the function will succeed, but executing it will not. - Note that if same function was created by superuser using language + Note that if the same function was created by a superuser using language plperlu, execution would succeed. + + + + + Data Values in PL/Perl + + + The argument values supplied to a PL/Perl function's script are simply + the input arguments converted to text form (just as if they had been + displayed by a SELECT statement). Conversely, the return + command will accept any string that is acceptable input format for + the function's declared return type. So, the PL/Perl programmer can + manipulate data values as if they were just text. + + + + + + Database Access from PL/Perl + Access to the database itself from your Perl function can be done via an experimental module DBI syntax. - - + + PL/Perl itself presently provides only one additional Perl command: + + + + + + elog + + elog level, msg + + + Emit a log or error message. Possible levels are DEBUG, + NOTICE, and ERROR. + DEBUG and NOTICE simply emit the given message + into the postmaster log (and send it to the client too, in the case of + NOTICE). ERROR raises an error condition: + further execution of the function is abandoned, and the current + transaction is aborted. + + + + + + + + + + Missing Features + + + PL/Perl functions cannot call each other directly (because they + are anonymous subroutines inside Perl). There's presently + no way for them to share global variables, either. + + + + PL/Perl cannot currently be used to write trigger functions. + + + + DBD::PgSPI or similar capability should be integrated + into the standard PostgreSQL distribution. + + + + + +