Skip site navigation (1) Skip section navigation (2)

Beta 3 of plPHP released

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: pgsql-announce(at)postgresql(dot)org
Subject: Beta 3 of plPHP released
Date: 2003-09-27 00:28:20
Message-ID: 3F74D9A4.7020808@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-announce
        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.



pgsql-announce by date

Next:From: David SchweikertDate: 2003-09-27 08:47:16
Subject: ANNOUNCE: Gedafe 1.2.0
Previous:From: Robert TreatDate: 2003-09-26 19:13:42
Subject: == PostgreSQL Weekly News - September 26th 2003 ==

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group