Beta 3 of plPHP has been released with full trigger support for PostgreSQL.
PL/PHP - PHP Procedural Language
Copyright 2003 Command Prompt, Inc.
http://www.commandprompt.com/
+1 503 222 2783 [EMAIL PROTECTED]
1. PL/PHP language installation notes 2. PL/PHP functions and arguments 3. Data values in PL/PHP 4. Database Access from PL/PHP 5. Trigger Procedures in PL/PHP 6. Trusted and Untrusted PL/PHP 7. PL/PHP Procedure Names 8. Missing features
PL/PHP is a loadable procedural language that enables you to write PostgreSQL functions
in the PHP programming language.
1. PL/PHP language installation notes
To install PL/PHP language Postgres DBA should have compiled shared library plphp.so in pgsql/lib/ driectory.
Then the following queries should be executed:
CREATE FUNCTION plphp_call_handler() RETURNS LANGUAGE_HANDLER AS 'plphp' LANGUAGE C;
CREATE TRUSTED LANGUAGE plphp HANDLER plphp_call_handler;
This should be done only once per each database where plphp language required.
2. PL/PHP Functions and Arguments
To create a function in the PL/PHP language, use the standard syntax:
CREATE FUNCTION funcname (argument-types) RETURNS return-type AS '
# plphp function body
' LANGUAGE 'plphp';The body of the function is ordinary PHP code.
Arguments and results are handled as in any other PHP subroutine.
Arguments are passed in $args array and a result value is returned with return operator.
For example, a function returning the greater of two integer values could be defined as:
CREATE FUNCTION plphp_max (integer, integer) RETURNS integer AS '
if ($args[0] > $args[1]){
return $args[0];
} else return $args[1];
' LANGUAGE 'plphp' WITH (isStrict);Note the clause WITH (isStrict), which saves us from having to think about NULL input
values: if a NULL is passed, the function will not be called at all, but will just
return a NULL result automatically. In a non-strict function, if the actual value of an
argument is NULL, the corresponding $args[n-1] variable will be set to an empty string (unset).
To detect whether a particular argument is NULL, use the function isset().
For example, suppose that we wanted plphp_max with one null and one non-null argument
to return the non-null argument, rather than NULL:
CREATE FUNCTION plphp_max (integer, integer) RETURNS integer AS '
if ( !isset($args[0]) ){
if ( !isset($args[1]) ){
return;
} else return $args[1];
}
if (!isset($args[1])){
return $args[0];
}
if ($args[0] > $args[1]){
return $args[0];
} else return $args[1];
' LANGUAGE 'plphp';As shown above, to return a NULL value from a PL/PHP function, just execute return.
This can be done whether the function is strict or not.
There is currently no support for accepting composite-type arguments and returning
a composite-type result value.
Tip: Because the function body is passed as an SQL string literal to CREATE FUNCTION,
you have to escape single quotes and backslashes within your PHP source, typically
by doubling them.
3. Data values in PL/PHP
The argument values supplied to a PL/PHP 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 plphp
programmer can manipulate data values as if they were just text.
4. Database Access from PL/PHP
There is currently no support for access the database from the body of a PL/PHP
procedure using SPI interface.
But you may use standard PHP interface for the database connections.
5. Trigger Procedures in PL/PHP
When a function is used in a trigger, the global associative array $_TD contains trigger-related values.
$_TD["new"]
An associative array containing the values of the new table row for INSERT/UPDATE
actions, or empty for DELETE. The array is indexed by field name. Fields that are
NULL will not appear in the array!
$_TD["old"]
An associative array containing the values of the old table row for UPDATE/DELETE
actions, or empty for INSERT. The array is indexed by field name. Fields that are
NULL will not appear in the array!
$_TD["name"] contains the trigger name.
$_TD["event"] contains the event as a string (INSERT, UPDATE, DELETE, or UNKNOWN).
$_TD["when"] contains one of BEFORE, AFTER, and UNKNOWN.
$_TD["level"] contains one of ROW, STATEMENT, and UNKNOWN.
$_TD["relid"] contains the relation ID of the table on which the trigger occurred.
$_TD["relname"] contains the relation name.
$_TD["argc"] contains the arguments count.
If the trigger was called with arguments they are available in $_TD["args"][0]
to $_TD["args"][($_TD["argc"]-1)].
Example:
5.1. Just to show $_TD structure:
CREATE TABLE test (
i int,
v varchar
) WITH OIDS;CREATE OR REPLACE FUNCTION echo_td() RETURNS trigger AS ' global $_TD; print_r($_TD); return 0; ' LANGUAGE 'plphp';
CREATE TRIGGER "before_rel_update" BEFORE UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE "echo_td"('example');INSERT INTO test (i, v) VALUES (1,'first line'); INSERT INTO test (i, v) VALUES (2,'second line');
UPDATE test SET i=3, v='new line' WHERE i=2;
This will show you the structure of associative array $_TD.
5.2. Modification control
CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS '
global $_TD;
if (($_TD["new"]["i"]>=100) || ($_TD["new"]["i"]<=0)){
echo "ERROR: The i key must be 0<i<100 \n";
return "SKIP";
} else return "MODIFY";
' LANGUAGE 'plphp';CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS '
global $_TD;
if ($_TD["old"]["v"]==$_TD["args"][0]){
echo "this field can not be deleted - ".$_TD["old"]["v"]."\n";
return "SKIP";
} else return "MODIFY";
' LANGUAGE 'plphp';
CREATE TRIGGER "test_valid_id_trig" BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE "valid_id"();
CREATE TRIGGER "immortal_trig" BEFORE DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE immortal('immortal');
Tip: If the TD["when"] is BEFORE, you may return "SKIP" to abort the event, or "MODIFY" to indicate you've modified the row.
6. Trusted and Untrusted PL/PHP
Normally, PL/PHP is installed as a "trusted" programming language named plphp. In this setup, certain PHP 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 process or to gain OS-level access with 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.
Here is an example of a function that will not work because file system operations are not allowed for security reasons:
CREATE OR REPLACE FUNCTION readpasswd() RETURNS integer AS '
readfile("/etc/passwd");
return 0;
' LANGUAGE 'plphp';The creation of the function will succeed, but executing it will fail with following error:
Warning: readfile(): SAFE MODE Restriction in effect. The script whose uid is 500 is not allowed to access /etc/passwd
owned by uid 0 in Command line code on line 3
Warning: readfile(/etc/passwd): failed to open stream: Success in plphp function source on line 3
Sometimes it is desirable to write PHP functions that are not restricted --- for example.
To handle these cases, PL/PHP can be set by DBA as an "untrusted" language:
CREATE LANGUAGE plphpu HANDLER plphp_call_handler;
In this case the full PHP language is available as language 'plphpu'.
If PL/PHP is set to "trusted" it use PHP "safe mode".
(Functions restricted/disabled by safe mode: http://www.php.net/manual/en/features.safe-mode.functions.php)
7. PL/PHP Procedure Names
In PostgreSQL, the same function name can be used for different functions as long as the number of arguments or
their types differ. PHP, however, requires all procedure names to be distinct. PL/PHP deals with this by making the internal
PHP procedure names contain the object ID of the procedure's pg_proc row as part of their name. Thus, PostgreSQL functions
with the same name and different argument types will be different PHP procedures too. This is not normally a concern for a
PL/PHP programmer, but it might be visible when debugging.
8. Missing features
9.1. PL/PHP compile function each time you execute it instead of first call this function;
9.2. PL/PHP functions cannot call each other directly (because they are anonymous subroutines inside PHP);
There's presently no way for them to share global variables, either;
9.3. PL/PHP missing functionality to access database using SPI interface;
9.4. There is currently no support for accepting composite-type arguments
and returning a composite-type result value;
9.5. Replace zend_error function calls to elog(*,*) function calls;
-- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com The most reliable support for the most reliable Open Source database.
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
