Project

General

Profile

Documentation » History » Version 10

Álvaro Herrera, 11/17/2005 03:28 AM

1 6 Álvaro Herrera
= PL/php - PHP Procedural Language for PostgreSQL =
2 1 bford -
3 4 Álvaro Herrera
[[PageOutline(2-3,Contents,inline)]]
4 1 bford -
5 6 Álvaro Herrera
== What is PL/php? ==
6 1 bford -
7 6 Álvaro Herrera
PL/php is a procedural language with hooks into the PostgreSQL
8 8 Álvaro Herrera
database sytem, intended to allow writing of PHP functions for
9
use as functions inside the PostgreSQL database.
10
It was written by Command Prompt, Inc. and has since
11 1 bford -
been open sourced and licensed under the PHP and PostgreSQL (BSD)
12
licenses.
13
14
== Download and Installation ==
15 3 Álvaro Herrera
16 8 Álvaro Herrera
Please see [wiki:InstallDocs10 the installation documentation] for instructions on
17
how to install PL/php 1.0.  To install the new code, which only works with PostgreSQL
18
8.0 and 8.1 and is currently in development, see [wiki:InstallDocs this page instead].
19 7 Álvaro Herrera
20 1 bford -
== Creating the PL/php language ==
21 7 Álvaro Herrera
22 8 Álvaro Herrera
Please see the documentation on [wiki:CreateLang80 how to create the language in a database]
23
once the library is installed.  If you are using PostgreSQL 8.1 you must follow
24
[wiki:CreateLang these other instructions] instead.
25 7 Álvaro Herrera
26 2 Álvaro Herrera
== Functions and Arguments ==
27 3 Álvaro Herrera
28 1 bford -
To create a function, use the standard syntax:
29
30
31
{{{
32 6 Álvaro Herrera
CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$
33 1 bford -
   # plphp function body here
34 6 Álvaro Herrera
$$ LANGUAGE 'plphp';
35 1 bford -
}}}
36
37
38
Arguments are passed in the $args array and the result value is returned
39
with the 'return' statement.
40
41 2 Álvaro Herrera
42 1 bford -
{{{
43 6 Álvaro Herrera
CREATE FUNCTION plphp_max(integer, integer) RETURNS integer AS $$
44 1 bford -
   if ($args[0] > $args[1]) {
45
      return $args[0];
46
   } else {
47
      return $args[1];
48
   }     
49 6 Álvaro Herrera
$$ STRICT LANGUAGE 'plphp'
50 1 bford -
}}}
51
52 2 Álvaro Herrera
53 1 bford -
NOTE: The use of the STRICT clause saves us from having to
54
think about NULL input values to our function.  If a NULL value is
55 2 Álvaro Herrera
passed, the function will not be run at all, but will rather just
56 1 bford -
return a NULL result automatically.
57
58
In a non-strict function, if the actual value of an argument is NULL,
59
the corresponding $args[n-1] variable will be set to an empty string
60
(unset).
61
62
63
== Data Types and Returns ==
64
65 9 Álvaro Herrera
The arguments passed into your PL/php function are converted to text
66 3 Álvaro Herrera
so you can manipulate them with the loose typing enjoyed in typical
67 1 bford -
PHP scripts.  Conversely, the return command will accept any string
68
that is acceptable input format for the function's declared return
69
type.
70
71
72 3 Álvaro Herrera
== Global Shared Variable ==
73 1 bford -
74
There is a global variable that can be used to store data between
75
function calls, called $_SHARED.
76
77 2 Álvaro Herrera
78 1 bford -
{{{
79 6 Álvaro Herrera
CREATE FUNCTION set_var(text) RETURNS text AS $$
80 1 bford -
   global $_SHARED;
81 6 Álvaro Herrera
   $_SHARED['first']=$args[0];
82
   return 'ok';
83
$$ LANGUAGE 'plphp';
84 1 bford -
85 6 Álvaro Herrera
CREATE FUNCTION get_var() RETURNS text AS $$
86 1 bford -
   global $_SHARED;
87 6 Álvaro Herrera
   return $_SHARED['first'];
88
$$ LANGUAGE 'plphp';
89 1 bford -
90
SELECT set_var('hello plphp');
91
SELECT get_var(); -- will return 'hello plphp'
92
93
}}}
94
95
NOTE: The shared global variable is connection-specific.  This is
96
useful for passing information around a single script execution,
97 2 Álvaro Herrera
but it is wiped when the connection is closed.
98
99
100 3 Álvaro Herrera
== PostgreSQL Array Support ==
101 1 bford -
102
There is support for multi-dimensional arrays.
103
104
For example:
105
106
107
{{{
108 6 Álvaro Herrera
CREATE FUNCTION php_array() RETURNS text[][] AS $$
109 1 bford -
   $return = array(array("Steven", "Klassen"),
110
                   array("Jonathan", "Daugherty"));
111
112
   return $return;
113 6 Álvaro Herrera
$$ LANGUAGE 'plphp';
114 1 bford -
}}}
115
116
117
{{{
118
sklassen=# select php_array();
119
                php_array                
120
-----------------------------------------
121
 {{Steven,Klassen},{Jonathan,Daugherty}}
122
(1 row)
123
}}}
124
125
126 3 Álvaro Herrera
== Polymorphic Arguments and Return Types ==
127 1 bford -
128
Functions may be declared to accept and return the polymorphic types
129
'anyelement', 'anyarray', and 'anyrecord'.  See the PostgreSQL
130
documentation section 33.2.5 for a more detailed explanation of
131
polymorphic functions.
132
133
For example,
134
135
{{{
136
CREATE FUNCTION array_three_values(anyelement, anyelement, anyelement) RETURNS anyarray AS '
137
   $ret[0] = $args[0];
138
   $ret[1] = $args[1];
139
   $ret[2] = $args[2];
140
   return $ret;
141
' LANGUAGE 'plphp';
142
143
SELECT array_three_values(3,2,1);
144
 array_three_values
145
--------------------
146
 {3,2,1}
147
(1 row)
148
149
CREATE OR REPLACE FUNCTION php_row(integer) RETURNS record AS '
150
   $ret[f1]=$args[0];
151
   $ret[f2]="hello";
152
   $ret[f3]="world";
153
   return $ret;
154
' LANGUAGE 'plphp';
155
156
select * FROM php_row(1) AS (f1 integer, f2 text, f3 text);
157
158
}}}
159
160
161 3 Álvaro Herrera
== Database Access (SPI) ==
162 1 bford -
163
Two functions are provided for database access.
164
165 6 Álvaro Herrera
 1. spi_exec_query - Execute a query with optional limit.
166
{{{
167
resource spi_exec_query(string query[, int limit])
168
}}}
169
 1. spi_fetch_row - Return an associative array of the row's results.
170
{{{
171
array spi_fetch_row(resource result)
172
}}}
173 1 bford -
174
For example:
175
176
This isn't a particularly useful function, but it will illustrate the
177
above-described access functions.  You provide an integer id and it
178
returns the username text field.
179
180
181
{{{
182
CREATE FUNCTION get_username(integer) RETURNS text AS '
183
   # Assign the query to a variable.
184
   $query = "SELECT username FROM users WHERE id = " .$args[0];
185
186
   # Run the query and get the $result object.
187
   $result = spi_exec_query($query);
188
189
   # Fetch the row from the $result.
190
   $row = spi_fetch_row($result);
191
192
   return $row[''username''];
193
' LANGUAGE 'plphp';
194
195
sklassen=# select get_username(1);
196
 get_username 
197
--------------
198
 sklassen
199
(1 row)
200
201 3 Álvaro Herrera
}}}
202 1 bford -
203
== Triggers ==
204
205
When a function is being used to return a trigger, the associative
206
array $_TD contains trigger-related values.
207
208 4 Álvaro Herrera
 $_TD["new"]::
209 1 bford -
   An associative array containing the values of the new table row for
210
   INSERT/UPDATE actions, or empty for DELETE.  The array is indexed
211
   by field name.  Important note: Fields that are NULL will not
212
   appear in the array!
213
214 4 Álvaro Herrera
 $_TD["old"]::
215 1 bford -
   An associative array containing the values of the old table row for
216
   UPDATE/DELETE actions, or empty for INSERT.  The array is indexed
217
   by field name.  Important note: Fields that are NULL will not
218
   appear in the array!
219
220 4 Álvaro Herrera
 $_TD["name"]::
221 1 bford -
   Contains the trigger name itself.
222
223
 $_TD["event"]::
224 9 Álvaro Herrera
   Contains one of the values: "INSERT", "UPDATE", "DELETE".
225 1 bford -
226
 $_TD["when"]::
227 9 Álvaro Herrera
   Contains one of the values: "BEFORE", "AFTER".
228 1 bford -
229 4 Álvaro Herrera
 $_TD["level"]::
230 9 Álvaro Herrera
   Contains one of the values: "ROW", "STATEMENT".
231 1 bford -
232 4 Álvaro Herrera
 $_TD["relid"]::
233 1 bford -
   Contains the relation ID of the table on which the trigger occured.
234
235 4 Álvaro Herrera
 $_TD["relname"]::
236 1 bford -
   Contains the relation name.
237
238 4 Álvaro Herrera
 $_TD["args"]::
239 1 bford -
   An array of arguments passed to the trigger, if any.  They can be
240 10 Álvaro Herrera
   accessed as $_TD["args"][idx].  Example, $_TD["args"]![0].
241 1 bford -
242 4 Álvaro Herrera
 $_TD["argc"]::
243 1 bford -
  The number of arguments passed to the trigger, if any.
244
245 4 Álvaro Herrera
=== Example of an AFTER INSERT trigger ===
246 1 bford -
247 2 Álvaro Herrera
Suppose you have a users table with the typical columns and an
248 1 bford -
activity table that you're using to track page accesses.  On row
249 3 Álvaro Herrera
250 1 bford -
INSERT to the activity table, you want to update the last_seen field
251
of the appropriate user's record.
252
253
Consider the following table definitions:
254
255
256
{{{
257
CREATE TABLE users (
258
    id serial PRIMARY KEY NOT NULL,
259
    username text NOT NULL,
260
    email text,
261
    last_seen timestamp without time zone,
262
    active boolean DEFAULT true NOT NULL
263
);
264
265
CREATE TABLE activity (
266
    id serial PRIMARY KEY NOT NULL,
267
    users_id integer NOT NULL,
268
    file_accessed text NOT NULL,
269
    stamp timestamp without time zone DEFAULT now() NOT NULL,
270
    CONSTRAINT users_id_exists FOREIGN KEY (users_id) REFERENCES users(id)
271
);
272
273
CREATE FUNCTION update_lastseen() RETURNS trigger AS '
274
    $new =& $_TD[''new''];
275
276
    if (isset($new[''users_id'']) && isset($new[''stamp''])) {
277
        $query = "UPDATE users SET last_seen = ''" .$new[''stamp''].
278
                 "'' WHERE id = " .$new[''users_id''];
279
280
        $rv = spi_exec_query($query);
281
    }
282
283
    return;
284
' LANGUAGE 'plphp';
285
286
CREATE TRIGGER after_update_lastseen_trigger
287
    AFTER INSERT ON activity FOR EACH ROW EXECUTE PROCEDURE update_lastseen();
288
289
}}}
290
291
'''1. We'll insert a new user row.'''
292
293
294
{{{
295
sklassen=# insert into users (username, email) values ('sklassen','sklassen@commandprompt.com');
296
INSERT 1
297
298
sklassen=# select * from users where username = 'sklassen';
299
300
 id | username |           email            | last_seen | active 
301
----+----------+----------------------------+-----------+--------
302
  1 | sklassen | sklassen@commandprompt.com |           | t
303
(1 row)
304
}}}
305
306
307
'''2. Insert a new row into the activity table.'''
308
309
310
{{{
311
sklassen=# insert into activity (users_id, file_accessed) values (1,'index.html');
312
INSERT 1
313 10 Álvaro Herrera
}}}
314 1 bford -
315
'''3. Check and make sure our trigger fired as expected.'''
316
317 10 Álvaro Herrera
{{{
318 1 bford -
sklassen=# select * from users where username = 'sklassen';
319
 id | username |           email            |         last_seen          | active 
320
----+----------+----------------------------+----------------------------+--------
321
  1 | sklassen | sklassen@commandprompt.com | 2005-01-10 09:48:57.191595 | t
322
(1 row)
323
}}}
324
325 4 Álvaro Herrera
=== Example of a BEFORE INSERT OR UPDATE trigger ===
326 1 bford -
327
Let's say we have a user named admin that we want to prevent the
328
pplication from modifying.  We'll create a BEFORE DELETE trigger that
329
prevents them from deleting the row and a BEFORE UPDATE trigger that
330
prevents them modifying the username on which the previous trigger
331
depends.
332
333
334
{{{
335
CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS '
336
   # The record may not be deleted if the username is "admin".
337
   echo "You cannot delete the admin user.\n";
338
   return ($_TD[''old''][''username''] == ''admin'') ? "SKIP" :
339
   "MODIFY";
340
' LANGUAGE 'plphp';
341
342
CREATE TRIGGER before_delete_immortal_trigger BEFORE DELETE ON users
343
FOR EACH ROW EXECUTE PROCEDURE immortal();
344
345
CREATE OR REPLACE FUNCTION protect_admin() RETURNS trigger AS '
346
   # Do not let them modify the username of the admin account.
347
   $oldUsername =& $_TD[''old''][''username''];
348
   $newUsername =& $_TD[''new''][''username''];
349
350
   if ($oldUsername == ''admin'' && ($oldUsername != $newUsername)) {
351
      echo "You cannot change the admin username.\n";
352
      return ''SKIP'';
353
   } else {
354
      return ''MODIFY'';
355
   }
356
' LANGUAGE 'plphp';
357
358
CREATE TRIGGER before_update_protect_admin_trigger BEFORE UPDATE ON
359
users FOR EACH ROW EXECUTE PROCEDURE protect_admin();
360
361
sklassen=> select * from users;
362
 id | username |           email            
363
----+----------+----------------------------
364
  1 | sklassen | sklassen@commandprompt.com
365
  2 | admin    | admin@yourhost.com
366
(2 rows)
367
368
sklassen=> update users set username = 'frobotz' where id = 2::bigint;
369
You cannot change the admin username.
370
UPDATE 0
371
372
sklassen=> delete from users where username = 'admin';
373
You cannot delete the admin user.
374
DELETE 0
375
376
}}}
377
378
379
== Trusted vs. Untrusted ==
380
381 9 Álvaro Herrera
Normally, PL/php is installed as a "trusted" procedural language named
382 1 bford -
'plphp'.  In this configuration, PHP will run in "safe mode".  Read
383 3 Álvaro Herrera
more about the restrictions here:
384 1 bford -
385
http://www.php.net/manual/en/features.safe-mode.functions.php
386
387
In general, the operations that are restricted are those that interact
388
with the environment.  This includes file operations, require, and use
389
(for external modules).
390
391
Since there is no way to gain access to the internals of the database
392
backend process or the operating system itself, any unprivileged
393
database user may use functions written in this language.
394
395
An example of a NON-working function due to security constraints:
396
397
398
{{{
399
CREATE FUNCTION read_passwd_file() RETURNS text AS '
400
   readfile("/etc/passwd");
401
   return 0;
402
' LANGUAGE 'plphp';
403
404
}}}
405
406
It will appear to execute, but depending on your log level, you'll may
407
see something like the following:
408
409 4 Álvaro Herrera
{{{
410
Warning: readfile(): SAFE MODE Restriction in effect. The script
411 1 bford -
whose uid is 500 is not allowed to access /etc/passwd owned by uid 0
412
in Command line code on line 3
413
414
Warning: readfile(/etc/passwd): failed to open stream: Success in
415
plphp function source on line 3
416
}}}
417
418 4 Álvaro Herrera
Sometimes it is desirable to write PHP functions that are not
419 1 bford -
restricted.  In this case, you can create the language as 'plphpu' to
420
enable the previously unavailable functions.
421
422
  
423
{{{
424
 CREATE LANGUAGE plphpu HANDLER plphp_call_handler;
425
}}}
426
427 3 Álvaro Herrera
428 1 bford -
== Composite Type Arguments ==
429
430
Composite-type arguments are passed to the function as associative
431
array. The keys of the array are the attribute names of
432
the composite type. Here is an example:
433
434
435
{{{
436
CREATE TABLE employee (
437
   name text,
438
   basesalary integer,
439
   bonus integer
440
);
441
442
CREATE FUNCTION empcomp(employee) RETURNS integer AS '
443
   return $args[0][''basesalary''] + $args[0][''bonus''];
444
' LANGUAGE 'plphp';
445
446
SELECT name, empcomp(employee) FROM employee;
447
448
}}}
449
450 6 Álvaro Herrera
== Returning A Row (composite type) ==
451 3 Álvaro Herrera
452 6 Álvaro Herrera
To return a row or composite-type value from a PL/php-language
453
function, you can use an indexed array:
454 1 bford -
455
{{{
456
CREATE TYPE __testrowphp AS (f1 integer, f2 text, f3 text);
457
458
CREATE OR REPLACE FUNCTION php_row(integer) RETURNS __testrowphp AS '
459
   $ret[f1]=$args[0];
460
   $ret[f2]="hello";
461
   $ret[f3]="world";
462
   return $ret;
463
' LANGUAGE 'plphp';
464
465
select * FROM php_row(1);
466
467
Will return:
468
 f1 |  f2   |  f3
469
----+-------+-------
470
  1 | hello | world
471
}}}
472
473
474 6 Álvaro Herrera
== Returning multiple rows (SRF functions) ==
475 1 bford -
476 6 Álvaro Herrera
SRF stands for "set-returning functions".  This means you can return
477
multiple rows, like in the following example:
478 2 Álvaro Herrera
479 1 bford -
{{{
480
CREATE TYPE __testsetphp AS (f1 integer, f2 text, f3 text);
481
482
CREATE OR REPLACE FUNCTION php_set(integer) RETURNS SETOF __testsetphp AS '
483
   $ret[0][f1]=$args[0];
484
   $ret[0][f2]="hello";
485
   $ret[0][f3]="world";
486 2 Álvaro Herrera
487 1 bford -
   $ret[1][f1]=2*$args[0];
488
   $ret[1][f2]="hello";
489
   $ret[1][f3]="postgres";
490 2 Álvaro Herrera
491 1 bford -
   $ret[2][f1]=3*$args[0];
492
   $ret[2][f2]="hello";
493
   $ret[2][f3]="plphp";
494
   return $ret;
495
' LANGUAGE 'plphp';
496
497
SELECT * FROM php_set(1);
498
499
Will return:
500
 f1 |  f2   |    f3
501
----+-------+----------
502
  1 | hello | world
503
  2 | hello | postgres
504
  3 | hello | plphp
505
506
}}}
507
508 3 Álvaro Herrera
== Limitations ==
509 1 bford -
510
PL/php functions cannot call each other directly because they are
511
anonymous subroutines inside PHP.
512 3 Álvaro Herrera
513 10 Álvaro Herrera
There's no way to abort a transaction from within a PL/php function.
514
More generally, there's no way to log a message.