Project

General

Profile

Documentation » History » Version 16

Alexey Klyukin, 09/17/2007 10:22 AM
Added a section about function argument names.

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
{{{
78 6 Álvaro Herrera
CREATE FUNCTION set_var(text) RETURNS text AS $$
79 1 bford -
   global $_SHARED;
80 6 Álvaro Herrera
   $_SHARED['first']=$args[0];
81
   return 'ok';
82
$$ LANGUAGE 'plphp';
83 1 bford -
84 6 Álvaro Herrera
CREATE FUNCTION get_var() RETURNS text AS $$
85 1 bford -
   global $_SHARED;
86 6 Álvaro Herrera
   return $_SHARED['first'];
87
$$ LANGUAGE 'plphp';
88 1 bford -
89
SELECT set_var('hello plphp');
90
SELECT get_var(); -- will return 'hello plphp'
91
}}}
92
93
NOTE: The shared global variable is connection-specific.  This is
94
useful for passing information around a single script execution,
95 2 Álvaro Herrera
but it is wiped when the connection is closed.
96
97
98 3 Álvaro Herrera
== PostgreSQL Array Support ==
99 1 bford -
100
There is support for multi-dimensional arrays.
101
102
For example:
103
104
{{{
105 6 Álvaro Herrera
CREATE FUNCTION php_array() RETURNS text[][] AS $$
106 1 bford -
   $return = array(array("Steven", "Klassen"),
107
                   array("Jonathan", "Daugherty"));
108
109
   return $return;
110 6 Álvaro Herrera
$$ LANGUAGE 'plphp';
111 1 bford -
}}}
112
113
114
{{{
115
sklassen=# select php_array();
116
                php_array                
117
-----------------------------------------
118
 {{Steven,Klassen},{Jonathan,Daugherty}}
119
(1 row)
120
}}}
121
122
123 3 Álvaro Herrera
== Polymorphic Arguments and Return Types ==
124 1 bford -
125
Functions may be declared to accept and return the polymorphic types
126
'anyelement', 'anyarray', and 'anyrecord'.  See the PostgreSQL
127
documentation section 33.2.5 for a more detailed explanation of
128
polymorphic functions.
129
130
For example,
131
132
{{{
133
CREATE FUNCTION array_three_values(anyelement, anyelement, anyelement) RETURNS anyarray AS '
134
   $ret[0] = $args[0];
135
   $ret[1] = $args[1];
136
   $ret[2] = $args[2];
137
   return $ret;
138
' LANGUAGE 'plphp';
139
140
SELECT array_three_values(3,2,1);
141
 array_three_values
142
--------------------
143
 {3,2,1}
144
(1 row)
145
146
CREATE OR REPLACE FUNCTION php_row(integer) RETURNS record AS '
147
   $ret[f1]=$args[0];
148
   $ret[f2]="hello";
149
   $ret[f3]="world";
150
   return $ret;
151
' LANGUAGE 'plphp';
152
153
select * FROM php_row(1) AS (f1 integer, f2 text, f3 text);
154
}}}
155
156
157
== Database Access (SPI) ==
158
159 12 Álvaro Herrera
Some functions are provided for database access.
160 1 bford -
161 12 Álvaro Herrera
 1. spi_exec - Execute a query with optional limit.
162 1 bford -
{{{
163 12 Álvaro Herrera
resource spi_exec(string query[, int limit])
164 1 bford -
}}}
165
 1. spi_fetch_row - Return an associative array of the row's results.
166
{{{
167
array spi_fetch_row(resource result)
168
}}}
169 12 Álvaro Herrera
 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.
170
{{{
171
string spi_status(resource result)
172
}}}
173
 1. spi_processed - Return the number of tuples in a result.
174
{{{
175
int spi_processed(resource result)
176
}}}
177
 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.
178
{{{
179
void spi_rewind(resource result)
180
}}}
181 1 bford -
182
For example:
183
184
This isn't a particularly useful function, but it will illustrate the
185
above-described access functions.  You provide an integer id and it
186
returns the username text field.
187
188
{{{
189 12 Álvaro Herrera
CREATE FUNCTION get_username(integer) RETURNS text AS $$
190 1 bford -
   # Assign the query to a variable.
191
   $query = "SELECT username FROM users WHERE id = " .$args[0];
192
193
   # Run the query and get the $result object.
194
   $result = spi_exec_query($query);
195
196
   # Fetch the row from the $result.
197
   $row = spi_fetch_row($result);
198
199 12 Álvaro Herrera
   return $row['username'];
200
$$ LANGUAGE 'plphp';
201 1 bford -
202
sklassen=# select get_username(1);
203
 get_username 
204
--------------
205
 sklassen
206
(1 row)
207
}}}
208
209 14 Álvaro Herrera
Note that results from spi_exec_query are allocated in RAM as a whole, so if you need to process huge result sets, you need to use a cursor.  This is not possible with the current interface however, so if you need to do this, bug the developers.
210
211 1 bford -
== Triggers ==
212 4 Álvaro Herrera
213 1 bford -
When a function is being used to return a trigger, the associative
214
array $_TD contains trigger-related values.
215
216 4 Álvaro Herrera
 $_TD["new"]::
217 1 bford -
   An associative array containing the values of the new table row for
218
   INSERT/UPDATE actions, or empty for DELETE.  The array is indexed
219
   by field name.  Important note: Fields that are NULL will not
220
   appear in the array!
221 4 Álvaro Herrera
 $_TD["old"]::
222 1 bford -
   An associative array containing the values of the old table row for
223 9 Álvaro Herrera
   UPDATE/DELETE actions, or empty for INSERT.  The array is indexed
224 1 bford -
   by field name.  Important note: Fields that are NULL will not
225
   appear in the array!
226 9 Álvaro Herrera
 $_TD["name"]::
227 1 bford -
   Contains the trigger name itself.
228 4 Álvaro Herrera
 $_TD["event"]::
229 9 Álvaro Herrera
   Contains one of the values: "INSERT", "UPDATE", "DELETE".
230 1 bford -
 $_TD["when"]::
231 4 Álvaro Herrera
   Contains one of the values: "BEFORE", "AFTER".
232 1 bford -
 $_TD["level"]::
233
   Contains one of the values: "ROW", "STATEMENT".
234
 $_TD["relid"]::
235
   Contains the relation ID of the table on which the trigger occured.
236 4 Álvaro Herrera
 $_TD["relname"]::
237 1 bford -
   Contains the relation name.
238 15 Álvaro Herrera
239 10 Álvaro Herrera
 $_TD["args"]::
240 1 bford -
   An array of arguments passed to the trigger, if any.  They can be
241 12 Álvaro Herrera
   accessed as $_TD["args"][idx].  For example, $_TD["args"]![0].
242 1 bford -
 $_TD["argc"]::
243 12 Álvaro Herrera
  The number of arguments passed to the trigger, 0 if none.
244 4 Álvaro Herrera
245 1 bford -
=== Example of an AFTER INSERT trigger ===
246 2 Álvaro Herrera
247 1 bford -
Suppose you have a users table with the typical columns and an
248 3 Álvaro Herrera
activity table that you're using to track page accesses.  On row
249 1 bford -
250
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
CREATE TABLE users (
257
    id serial PRIMARY KEY NOT NULL,
258
    username text NOT NULL,
259
    email text,
260
    last_seen timestamp without time zone,
261
    active boolean DEFAULT true NOT NULL
262
);
263
264
CREATE TABLE activity (
265
    id serial PRIMARY KEY NOT NULL,
266
    users_id integer NOT NULL,
267
    file_accessed text NOT NULL,
268
    stamp timestamp without time zone DEFAULT now() NOT NULL,
269
    CONSTRAINT users_id_exists FOREIGN KEY (users_id) REFERENCES users(id)
270
);
271
272 13 Álvaro Herrera
CREATE FUNCTION update_lastseen() RETURNS trigger AS $$
273
    $new =& $_TD['new'];
274 1 bford -
275 13 Álvaro Herrera
    if (isset($new['users_id']) && isset($new['stamp'])) {
276
        $query = "UPDATE users SET last_seen = '" .$new['stamp'].
277
                 "' WHERE id = " .$new['users_id'];
278 1 bford -
279 13 Álvaro Herrera
        $rv = spi_exec($query);
280 1 bford -
    }
281
282
    return;
283 13 Álvaro Herrera
$$ LANGUAGE 'plphp';
284 1 bford -
285
CREATE TRIGGER after_update_lastseen_trigger
286
    AFTER INSERT ON activity FOR EACH ROW EXECUTE PROCEDURE update_lastseen();
287
288
}}}
289
290
'''1. We'll insert a new user row.'''
291
292
293
{{{
294
sklassen=# insert into users (username, email) values ('sklassen','sklassen@commandprompt.com');
295
INSERT 1
296
297
sklassen=# select * from users where username = 'sklassen';
298
299
 id | username |           email            | last_seen | active 
300
----+----------+----------------------------+-----------+--------
301
  1 | sklassen | sklassen@commandprompt.com |           | t
302
(1 row)
303
}}}
304
305
306
'''2. Insert a new row into the activity table.'''
307
308
309
{{{
310
sklassen=# insert into activity (users_id, file_accessed) values (1,'index.html');
311
INSERT 1
312
}}}
313
314
'''3. Check and make sure our trigger fired as expected.'''
315
316
{{{
317
sklassen=# select * from users where username = 'sklassen';
318
 id | username |           email            |         last_seen          | active 
319
----+----------+----------------------------+----------------------------+--------
320
  1 | sklassen | sklassen@commandprompt.com | 2005-01-10 09:48:57.191595 | t
321
(1 row)
322
}}}
323
324
=== Example of a BEFORE INSERT OR UPDATE trigger ===
325
326
Let's say we have a user named admin that we want to prevent the
327 12 Álvaro Herrera
application from modifying.  We'll create a BEFORE DELETE trigger that
328 1 bford -
prevents them from deleting the row and a BEFORE UPDATE trigger that
329
prevents them modifying the username on which the previous trigger
330
depends.
331
332
{{{
333 12 Álvaro Herrera
CREATE TABLE users (username text, email text);
334
INSERT INTO users VALUES ('admin', 'admin@commandprompt.com');
335
INSERT INTO users VALUES ('darcy', 'darcy@example.com');
336
337
CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$
338 1 bford -
   # The record may not be deleted if the username is "admin".
339 12 Álvaro Herrera
   if ($_TD['old']['username'] == 'admin') {
340
      pg_raise('notice', "You cannot delete the admin user");
341
      return 'SKIP';
342
   }
343
   return;
344
$$ LANGUAGE 'plphp';
345 1 bford -
346
CREATE TRIGGER before_delete_immortal_trigger BEFORE DELETE ON users
347
FOR EACH ROW EXECUTE PROCEDURE immortal();
348
349 12 Álvaro Herrera
CREATE OR REPLACE FUNCTION protect_admin() RETURNS trigger AS $$
350 1 bford -
   # Do not let them modify the username of the admin account.
351 12 Álvaro Herrera
   $oldUsername = $_TD['old']['username'];
352
   $newUsername = $_TD['new']['username'];
353 1 bford -
354 12 Álvaro Herrera
   if ($oldUsername == 'admin' && ($oldUsername != $newUsername)) {
355
      pg_raise('notice', "You cannot change the admin username.");
356
      return 'SKIP';
357 1 bford -
   } else {
358 12 Álvaro Herrera
      return;
359 1 bford -
   }
360 12 Álvaro Herrera
$$ LANGUAGE 'plphp';
361 1 bford -
362
CREATE TRIGGER before_update_protect_admin_trigger BEFORE UPDATE ON
363
users FOR EACH ROW EXECUTE PROCEDURE protect_admin();
364 12 Álvaro Herrera
}}}
365 1 bford -
366 12 Álvaro Herrera
Now the user admin cannot be deleted, nor its username can be changed:
367
368
{{{
369
pl_regression=# select * from users;
370
 username |          email          
371
----------+-------------------------
372
 admin    | admin@commandprompt.com
373
 darcy    | darcy@example.com
374 1 bford -
(2 rows)
375
376 12 Álvaro Herrera
pl_regression=# update users set username = 'foobar';
377
NOTICE:  plphp: You cannot change the admin username.
378
UPDATE 1
379 1 bford -
380 12 Álvaro Herrera
pl_regression=# select * from users;
381
 username |          email          
382
----------+-------------------------
383
 admin    | admin@commandprompt.com
384
 foobar   | darcy@example.com
385
(2 rows)
386 1 bford -
387 12 Álvaro Herrera
pl_regression=# delete from users;
388
NOTICE:  plphp: You cannot delete the admin user
389
DELETE 1
390
391
pl_regression=# select * from users;
392
 username |          email          
393
----------+-------------------------
394
 admin    | admin@commandprompt.com
395
(1 row)
396 1 bford -
}}}
397
398
399
== Trusted vs. Untrusted ==
400
401
Normally, PL/php is installed as a "trusted" procedural language named
402
'plphp'.  In this configuration, PHP will run in "safe mode".  Read
403
more about the restrictions here:
404
405
http://www.php.net/manual/en/features.safe-mode.functions.php
406
407
In general, the operations that are restricted are those that interact
408
with the environment.  This includes file operations, require, and use
409
(for external modules).
410
411
Since there is no way to gain access to the internals of the database
412
backend process or the operating system itself, any unprivileged
413
database user may use functions written in this language.
414
415
An example of a NON-working function due to security constraints:
416
417
{{{
418
CREATE FUNCTION read_passwd_file() RETURNS text AS '
419
   readfile("/etc/passwd");
420
   return 0;
421
' LANGUAGE 'plphp';
422
}}}
423
424
It will appear to execute, but depending on your log level, you'll may
425
see something like the following:
426
427
{{{
428
Warning: readfile(): SAFE MODE Restriction in effect. The script
429
whose uid is 500 is not allowed to access /etc/passwd owned by uid 0
430
in Command line code on line 3
431
432
Warning: readfile(/etc/passwd): failed to open stream: Success in
433 4 Álvaro Herrera
plphp function source on line 3
434
}}}
435 1 bford -
436
Sometimes it is desirable to write PHP functions that are not
437
restricted.  In this case, you can create the language as 'plphpu' to
438
enable the previously unavailable functions.
439
  
440
{{{
441 12 Álvaro Herrera
 CREATE LANGUAGE plphpu;
442 1 bford -
}}}
443 4 Álvaro Herrera
444 1 bford -
445
== Composite Type Arguments ==
446
447
Composite-type arguments are passed to the function as associative
448
array. The keys of the array are the attribute names of
449
the composite type. Here is an example:
450
451
{{{
452
CREATE TABLE employee (
453
   name text,
454
   basesalary integer,
455
   bonus integer
456
);
457
458
CREATE FUNCTION empcomp(employee) RETURNS integer AS '
459
   return $args[0][''basesalary''] + $args[0][''bonus''];
460
' LANGUAGE 'plphp';
461
462 12 Álvaro Herrera
INSERT INTO employee values ('Josh', 1000, 10);
463
INSERT INTO employee values ('Darcy', 500, 20);
464
465 1 bford -
SELECT name, empcomp(employee) FROM employee;
466 12 Álvaro Herrera
}}}
467 1 bford -
468 12 Álvaro Herrera
This example results in the following output:
469
470
{{{
471
 name  | empcomp 
472
-------+---------
473
 Josh  |    1010
474
 Darcy |     520
475
(2 rows)
476 1 bford -
}}}
477
478
== Returning A Row (composite type) ==
479
480
To return a row or composite-type value from a PL/php-language
481
function, you can use an indexed array:
482
483
{{{
484 12 Álvaro Herrera
CREATE TYPE php_row AS (f1 integer, f2 text, f3 text);
485 1 bford -
486 12 Álvaro Herrera
CREATE OR REPLACE FUNCTION php_row(integer) RETURNS php_row AS $$
487
   $ret['f1'] = $args[0];
488
   $ret['f3'] = "world";
489
   $ret['f2'] = "hello";
490 1 bford -
   return $ret;
491 12 Álvaro Herrera
$$ LANGUAGE 'plphp';
492 1 bford -
493 12 Álvaro Herrera
SELECT * FROM php_row(1);
494
}}}
495 6 Álvaro Herrera
496 1 bford -
Will return:
497 12 Álvaro Herrera
{{{
498 6 Álvaro Herrera
 f1 |  f2   |  f3
499 2 Álvaro Herrera
----+-------+-------
500 1 bford -
  1 | hello | world
501
}}}
502
503
504
== Returning multiple rows (SRF functions) ==
505
506
SRF stands for "set-returning functions".  This means you can return
507 2 Álvaro Herrera
multiple rows, like in the following example:
508 1 bford -
509
{{{
510 15 Álvaro Herrera
CREATE TYPE php_row AS (f1 integer, f2 text, f3 text);
511 1 bford -
512 15 Álvaro Herrera
CREATE OR REPLACE FUNCTION php_set(integer) RETURNS SETOF php_row AS $$
513 14 Álvaro Herrera
   $ret['f1'] = $args[0];
514
   $ret['f2'] = "hello";
515
   $ret['f3'] = "world";
516
   return_next($ret);
517 1 bford -
518 14 Álvaro Herrera
   $ret['f1'] = 2 * $args[0];
519
   $ret['f2'] = "hello";
520
   $ret['f3'] = "postgres";
521
   return_next($ret);
522 1 bford -
523 14 Álvaro Herrera
   $ret['f1'] = 3 * $args[0];
524
   $ret['f2'] = "hello";
525
   $ret['f3'] = "plphp";
526
   return_next($ret);
527
$$ LANGUAGE 'plphp';
528
}}}
529 1 bford -
530 14 Álvaro Herrera
This will result in:
531
{{{
532
pl_regression=# SELECT * FROM php_set(1);
533 1 bford -
 f1 |  f2   |    f3
534
----+-------+----------
535
  1 | hello | world
536 3 Álvaro Herrera
  2 | hello | postgres
537 1 bford -
  3 | hello | plphp
538 14 Álvaro Herrera
(3 rows)
539 1 bford -
}}}
540 3 Álvaro Herrera
541 14 Álvaro Herrera
The important thing to notice here is the `return_next()` function.  This function must be called with a single parameter, which must be an array with one element per attribute of the return type.  Note that currently the names of the array elements are ignored; the elements are assigned to attributes in order.  If you must return a null value, asign a null value to an array element.  This may be fixed in a future release.
542
543
Finishing a SRF is done by either calling `return` or by falling off the end of the function.  At that point the entire results are delivered back to the outer query, be it directly to the user or to be joined to other tables/SRFs/etc.
544
545
The return tuples are written to a Postgres "tuplestore", which is disk-backed, and thus do not need to fit entirely in RAM.
546
547 16 Alexey Klyukin
== Function Argument Names ==
548
549
You can assign names to pl/php function arguments:
550
551
{{{
552
CREATE FUNCTION plphp_match(document text, word text) RETURNS BOOL AS
553
$$
554
   if (strstr($document, $word))
555
       return TRUE;
556
   else
557
       return FALSE;
558
$$ LANGUAGE 'plphp';
559
560
Example output:
561
562
template1=# select plphp_match('PostgreSQL', 'SQL');
563
 plphp_match 
564
-------------
565
 t
566
(1 row)
567
568
}}}
569
570
Both $name and $args[position] variable access methods are supported.
571
572 12 Álvaro Herrera
== Limitations ==
573 1 bford -
574
PL/php functions cannot call each other directly because their names
575
are mangled.
576 14 Álvaro Herrera
577
SPI calls have the whole results allocated in memory, so processing huge results may be problematic.  Also it's not possible to use cursors with SPI.