Project

General

Profile

Documentation » History » Version 11

Álvaro Herrera, 11/30/2005 12:14 PM

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