Beta 3 of plPHP released

PL/PHP - PHP Procedural Language
Copyright 2003 Command Prompt, Inc.
http://www.commandprompt.com/
+1 503 222 2783 info(at)commandprompt(dot)com

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 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
The most reliable support for the most reliable Open Source database.