Project

General

Profile

Documentation » History » Revision 13

Revision 12 (Álvaro Herrera, 12/02/2005 07:32 AM) → Revision 13/20 (Álvaro Herrera, 12/02/2005 09:50 AM)

= PL/php - PHP Procedural Language for PostgreSQL = 

 [[PageOutline(2-3,Contents,inline)]] 

 == What is PL/php? == 

 PL/php is a procedural language with hooks into the PostgreSQL 
 database sytem, intended to allow writing of PHP functions for 
 use as functions inside the PostgreSQL database. 
 It was written by Command Prompt, Inc. and has since 
 been open sourced and licensed under the PHP and PostgreSQL (BSD) 
 licenses. 

 == Download and Installation == 

 Please see [wiki:InstallDocs10 the installation documentation] for instructions on 
 how to install PL/php 1.0.    To install the new code, which only works with PostgreSQL 
 8.0 and 8.1 and is currently in development, see [wiki:InstallDocs this page instead]. 

 == Creating the PL/php language == 

 Please see the documentation on [wiki:CreateLang80 how to create the language in a database] 
 once the library is installed.    If you are using PostgreSQL 8.1 you must follow 
 [wiki:CreateLang these other instructions] instead. 

 == Functions and Arguments == 

 To create a function, use the standard syntax: 


 {{{ 
 CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$ 
    # plphp function body here 
 $$ LANGUAGE 'plphp'; 
 }}} 


 Arguments are passed in the $args array and the result value is returned 
 with the 'return' statement. 


 {{{ 
 CREATE FUNCTION plphp_max(integer, integer) RETURNS integer AS $$ 
    if ($args[0] > $args[1]) { 
       return $args[0]; 
    } else { 
       return $args[1]; 
    }      
 $$ STRICT LANGUAGE 'plphp' 
 }}} 


 NOTE: The use of the STRICT clause saves us from having to 
 think about NULL input values to our function.    If a NULL value is 
 passed, the function will not be run at all, but will rather 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). 


 == Data Types and Returns == 

 The arguments passed into your PL/php function are converted to text 
 so you can manipulate them with the loose typing enjoyed in typical 
 PHP scripts.    Conversely, the return command will accept any string 
 that is acceptable input format for the function's declared return 
 type. 


 == Global Shared Variable == 

 There is a global variable that can be used to store data between 
 function calls, called $_SHARED. 

 {{{ 
 CREATE FUNCTION set_var(text) RETURNS text AS $$ 
    global $_SHARED; 
    $_SHARED['first']=$args[0]; 
    return 'ok'; 
 $$ LANGUAGE 'plphp'; 

 CREATE FUNCTION get_var() RETURNS text AS $$ 
    global $_SHARED; 
    return $_SHARED['first']; 
 $$ LANGUAGE 'plphp'; 

 SELECT set_var('hello plphp'); 
 SELECT get_var(); -- will return 'hello plphp' 
 }}} 

 NOTE: The shared global variable is connection-specific.    This is 
 useful for passing information around a single script execution, 
 but it is wiped when the connection is closed. 


 == PostgreSQL Array Support == 

 There is support for multi-dimensional arrays. 

 For example: 

 {{{ 
 CREATE FUNCTION php_array() RETURNS text[][] AS $$ 
    $return = array(array("Steven", "Klassen"), 
                    array("Jonathan", "Daugherty")); 

    return $return; 
 $$ LANGUAGE 'plphp'; 
 }}} 


 {{{ 
 sklassen=# select php_array(); 
                 php_array                 
 ----------------------------------------- 
  {{Steven,Klassen},{Jonathan,Daugherty}} 
 (1 row) 
 }}} 


 == Polymorphic Arguments and Return Types == 

 Functions may be declared to accept and return the polymorphic types 
 'anyelement', 'anyarray', and 'anyrecord'.    See the PostgreSQL 
 documentation section 33.2.5 for a more detailed explanation of 
 polymorphic functions. 

 For example, 

 {{{ 
 CREATE FUNCTION array_three_values(anyelement, anyelement, anyelement) RETURNS anyarray AS ' 
    $ret[0] = $args[0]; 
    $ret[1] = $args[1]; 
    $ret[2] = $args[2]; 
    return $ret; 
 ' LANGUAGE 'plphp'; 

 SELECT array_three_values(3,2,1); 
  array_three_values 
 -------------------- 
  {3,2,1} 
 (1 row) 

 CREATE OR REPLACE FUNCTION php_row(integer) RETURNS record AS ' 
    $ret[f1]=$args[0]; 
    $ret[f2]="hello"; 
    $ret[f3]="world"; 
    return $ret; 
 ' LANGUAGE 'plphp'; 

 select * FROM php_row(1) AS (f1 integer, f2 text, f3 text); 
 }}} 


 == Database Access (SPI) == 

 Some functions are provided for database access. 

  1. spi_exec - Execute a query with optional limit. 
 {{{ 
 resource spi_exec(string query[, int limit]) 
 }}} 
  1. spi_fetch_row - Return an associative array of the row's results. 
 {{{ 
 array spi_fetch_row(resource result) 
 }}} 
  1. spi_status - Return the status of a previous query.    If this is SPI_OK_SELECT you can obtain tuples from the result using spi_fetch_row. 
 {{{ 
 string spi_status(resource result) 
 }}} 
  1. spi_processed - Return the number of tuples in a result. 
 {{{ 
 int spi_processed(resource result) 
 }}} 
  1. spi_rewind - Put the row cursor at the beginning of the result, so spi_fetch_row will continue fetching tuples from the beginning of the result. 
 {{{ 
 void spi_rewind(resource result) 
 }}} 

 For example: 

 This isn't a particularly useful function, but it will illustrate the 
 above-described access functions.    You provide an integer id and it 
 returns the username text field. 


 {{{ 
 CREATE FUNCTION get_username(integer) RETURNS text AS $$ 
    # Assign the query to a variable. 
    $query = "SELECT username FROM users WHERE id = " .$args[0]; 

    # Run the query and get the $result object. 
    $result = spi_exec_query($query); 

    # Fetch the row from the $result. 
    $row = spi_fetch_row($result); 

    return $row['username']; 
 $$ LANGUAGE 'plphp'; 

 sklassen=# select get_username(1); 
  get_username  
 -------------- 
  sklassen 
 (1 row) 
 }}} 

 == Triggers == 

 When a function is being used to return a trigger, the 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.    Important note: 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.    Important note: Fields that are NULL will not 
    appear in the array! 
  $_TD["name"]:: 
    Contains the trigger name itself. 
  $_TD["event"]:: 
    Contains one of the values: "INSERT", "UPDATE", "DELETE". 
  $_TD["when"]:: 
    Contains one of the values: "BEFORE", "AFTER". 
  $_TD["level"]:: 
    Contains one of the values: "ROW", "STATEMENT". 
  $_TD["relid"]:: 
    Contains the relation ID of the table on which the trigger occured. 
  $_TD["relname"]:: 
    Contains the relation name. 
  $_TD["args"]:: 
    An array of arguments passed to the trigger, if any.    They can be 
    accessed as $_TD["args"][idx].    For example, $_TD["args"]![0]. 
  $_TD["argc"]:: 
   The number of arguments passed to the trigger, 0 if none. 

 === Example of an AFTER INSERT trigger === 

 Suppose you have a users table with the typical columns and an 
 activity table that you're using to track page accesses.    On row 

 INSERT to the activity table, you want to update the last_seen field 
 of the appropriate user's record. 

 Consider the following table definitions: 

 {{{ 
 CREATE TABLE users ( 
     id serial PRIMARY KEY NOT NULL, 
     username text NOT NULL, 
     email text, 
     last_seen timestamp without time zone, 
     active boolean DEFAULT true NOT NULL 
 ); 

 CREATE TABLE activity ( 
     id serial PRIMARY KEY NOT NULL, 
     users_id integer NOT NULL, 
     file_accessed text NOT NULL, 
     stamp timestamp without time zone DEFAULT now() NOT NULL, 
     CONSTRAINT users_id_exists FOREIGN KEY (users_id) REFERENCES users(id) 
 ); 

 CREATE FUNCTION update_lastseen() RETURNS trigger AS $$ ' 
     $new =& $_TD['new']; $_TD[''new'']; 

     if (isset($new['users_id']) (isset($new[''users_id'']) && isset($new['stamp'])) isset($new[''stamp''])) { 
         $query = "UPDATE users SET last_seen = '" .$new['stamp']. ''" .$new[''stamp'']. 
                  "' "'' WHERE id = " .$new['users_id']; .$new[''users_id'']; 

         $rv = spi_exec($query); spi_exec_query($query); 
     } 

     return; 
 $$ ' LANGUAGE 'plphp'; 

 CREATE TRIGGER after_update_lastseen_trigger 
     AFTER INSERT ON activity FOR EACH ROW EXECUTE PROCEDURE update_lastseen(); 

 }}} 

 '''1. We'll insert a new user row.''' 


 {{{ 
 sklassen=# insert into users (username, email) values ('sklassen','sklassen@commandprompt.com'); 
 INSERT 1 

 sklassen=# select * from users where username = 'sklassen'; 

  id | username |             email              | last_seen | active  
 ----+----------+----------------------------+-----------+-------- 
   1 | sklassen | sklassen@commandprompt.com |             | t 
 (1 row) 
 }}} 


 '''2. Insert a new row into the activity table.''' 


 {{{ 
 sklassen=# insert into activity (users_id, file_accessed) values (1,'index.html'); 
 INSERT 1 
 }}} 

 '''3. Check and make sure our trigger fired as expected.''' 

 {{{ 
 sklassen=# select * from users where username = 'sklassen'; 
  id | username |             email              |           last_seen            | active  
 ----+----------+----------------------------+----------------------------+-------- 
   1 | sklassen | sklassen@commandprompt.com | 2005-01-10 09:48:57.191595 | t 
 (1 row) 
 }}} 

 === Example of a BEFORE INSERT OR UPDATE trigger === 

 Let's say we have a user named admin that we want to prevent the 
 application from modifying.    We'll create a BEFORE DELETE trigger that 
 prevents them from deleting the row and a BEFORE UPDATE trigger that 
 prevents them modifying the username on which the previous trigger 
 depends. 

 {{{ 
 CREATE TABLE users (username text, email text); 
 INSERT INTO users VALUES ('admin', 'admin@commandprompt.com'); 
 INSERT INTO users VALUES ('darcy', 'darcy@example.com'); 

 CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$ 
    # The record may not be deleted if the username is "admin". 
    if ($_TD['old']['username'] == 'admin') { 
       pg_raise('notice', "You cannot delete the admin user"); 
       return 'SKIP'; 
    } 
    return; 
 $$ LANGUAGE 'plphp'; 

 CREATE TRIGGER before_delete_immortal_trigger BEFORE DELETE ON users 
 FOR EACH ROW EXECUTE PROCEDURE immortal(); 

 CREATE OR REPLACE FUNCTION protect_admin() RETURNS trigger AS $$ 
    # Do not let them modify the username of the admin account. 
    $oldUsername = $_TD['old']['username']; 
    $newUsername = $_TD['new']['username']; 

    if ($oldUsername == 'admin' && ($oldUsername != $newUsername)) { 
       pg_raise('notice', "You cannot change the admin username."); 
       return 'SKIP'; 
    } else { 
       return; 
    } 
 $$ LANGUAGE 'plphp'; 

 CREATE TRIGGER before_update_protect_admin_trigger BEFORE UPDATE ON 
 users FOR EACH ROW EXECUTE PROCEDURE protect_admin(); 
 }}} 

 Now the user admin cannot be deleted, nor its username can be changed: 

 {{{ 
 pl_regression=# select * from users; 
  username |            email           
 ----------+------------------------- 
  admin      | admin@commandprompt.com 
  darcy      | darcy@example.com 
 (2 rows) 

 pl_regression=# update users set username = 'foobar'; 
 NOTICE:    plphp: You cannot change the admin username. 
 UPDATE 1 

 pl_regression=# select * from users; 
  username |            email           
 ----------+------------------------- 
  admin      | admin@commandprompt.com 
  foobar     | darcy@example.com 
 (2 rows) 

 pl_regression=# delete from users; 
 NOTICE:    plphp: You cannot delete the admin user 
 DELETE 1 

 pl_regression=# select * from users; 
  username |            email           
 ----------+------------------------- 
  admin      | admin@commandprompt.com 
 (1 row) 
 }}} 


 == Trusted vs. Untrusted == 

 Normally, PL/php is installed as a "trusted" procedural language named 
 'plphp'.    In this configuration, PHP will run in "safe mode".    Read 
 more about the restrictions here: 

 http://www.php.net/manual/en/features.safe-mode.functions.php 

 In general, the operations that are restricted are those that interact 
 with the environment.    This includes file operations, require, and use 
 (for external modules). 

 Since there is no way to gain access to the internals of the database 
 backend process or the operating system itself, any unprivileged 
 database user may use functions written in this language. 

 An example of a NON-working function due to security constraints: 

 {{{ 
 CREATE FUNCTION read_passwd_file() RETURNS text AS ' 
    readfile("/etc/passwd"); 
    return 0; 
 ' LANGUAGE 'plphp'; 
 }}} 

 It will appear to execute, but depending on your log level, you'll may 
 see something like the following: 

 {{{ 
 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.    In this case, you can create the language as 'plphpu' to 
 enable the previously unavailable functions. 
  
 {{{ 
  CREATE LANGUAGE plphpu; 
 }}} 


 == Composite Type Arguments == 

 Composite-type arguments are passed to the function as associative 
 array. The keys of the array are the attribute names of 
 the composite type. Here is an example: 

 {{{ 
 CREATE TABLE employee ( 
    name text, 
    basesalary integer, 
    bonus integer 
 ); 

 CREATE FUNCTION empcomp(employee) RETURNS integer AS ' 
    return $args[0][''basesalary''] + $args[0][''bonus'']; 
 ' LANGUAGE 'plphp'; 

 INSERT INTO employee values ('Josh', 1000, 10); 
 INSERT INTO employee values ('Darcy', 500, 20); 

 SELECT name, empcomp(employee) FROM employee; 
 }}} 

 This example results in the following output: 

 {{{ 
  name    | empcomp  
 -------+--------- 
  Josh    |      1010 
  Darcy |       520 
 (2 rows) 
 }}} 

 == Returning A Row (composite type) == 

 To return a row or composite-type value from a PL/php-language 
 function, you can use an indexed array: 

 {{{ 
 CREATE TYPE php_row AS (f1 integer, f2 text, f3 text); 

 CREATE OR REPLACE FUNCTION php_row(integer) RETURNS php_row AS $$ 
    $ret['f1'] = $args[0]; 
    $ret['f3'] = "world"; 
    $ret['f2'] = "hello"; 
    return $ret; 
 $$ LANGUAGE 'plphp'; 

 SELECT * FROM php_row(1); 
 }}} 

 Will return: 
 {{{ 
  f1 |    f2     |    f3 
 ----+-------+------- 
   1 | hello | world 
 }}} 


 == Returning multiple rows (SRF functions) == 

 SRF stands for "set-returning functions".    This means you can return 
 multiple rows, like in the following example: 

 {{{ 
 CREATE TYPE __testsetphp AS (f1 integer, f2 text, f3 text); 

 CREATE OR REPLACE FUNCTION php_set(integer) RETURNS SETOF __testsetphp AS ' 
    $ret[0][f1]=$args[0]; 
    $ret[0][f2]="hello"; 
    $ret[0][f3]="world"; 

    $ret[1][f1]=2*$args[0]; 
    $ret[1][f2]="hello"; 
    $ret[1][f3]="postgres"; 

    $ret[2][f1]=3*$args[0]; 
    $ret[2][f2]="hello"; 
    $ret[2][f3]="plphp"; 
    return $ret; 
 ' LANGUAGE 'plphp'; 

 SELECT * FROM php_set(1); 

 Will return: 
  f1 |    f2     |      f3 
 ----+-------+---------- 
   1 | hello | world 
   2 | hello | postgres 
   3 | hello | plphp 
 }}} 

 == Limitations == 

 PL/php functions cannot call each other directly because their names 
 are mangled.