Project

General

Profile

Documentation » History » Version 9

Álvaro Herrera, 11/16/2005 11:37 AM
Capitalization changes

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
   accessed as $_TD["args"][idx].  Example, $_TD["args"][0].
241
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
314
'''3. Check and make sure our trigger fired as expected.'''
315
316
sklassen=# select * from users where username = 'sklassen';
317
 id | username |           email            |         last_seen          | active 
318
----+----------+----------------------------+----------------------------+--------
319
  1 | sklassen | sklassen@commandprompt.com | 2005-01-10 09:48:57.191595 | t
320
(1 row)
321
322
}}}
323
324 4 Álvaro Herrera
=== Example of a BEFORE INSERT OR UPDATE trigger ===
325 1 bford -
326
Let's say we have a user named admin that we want to prevent the
327
pplication from modifying.  We'll create a BEFORE DELETE trigger that
328
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
{{{
334
CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS '
335
   # The record may not be deleted if the username is "admin".
336
   echo "You cannot delete the admin user.\n";
337
   return ($_TD[''old''][''username''] == ''admin'') ? "SKIP" :
338
   "MODIFY";
339
' LANGUAGE 'plphp';
340
341
CREATE TRIGGER before_delete_immortal_trigger BEFORE DELETE ON users
342
FOR EACH ROW EXECUTE PROCEDURE immortal();
343
344
CREATE OR REPLACE FUNCTION protect_admin() RETURNS trigger AS '
345
   # Do not let them modify the username of the admin account.
346
   $oldUsername =& $_TD[''old''][''username''];
347
   $newUsername =& $_TD[''new''][''username''];
348
349
   if ($oldUsername == ''admin'' && ($oldUsername != $newUsername)) {
350
      echo "You cannot change the admin username.\n";
351
      return ''SKIP'';
352
   } else {
353
      return ''MODIFY'';
354
   }
355
' LANGUAGE 'plphp';
356
357
CREATE TRIGGER before_update_protect_admin_trigger BEFORE UPDATE ON
358
users FOR EACH ROW EXECUTE PROCEDURE protect_admin();
359
360
sklassen=> select * from users;
361
 id | username |           email            
362
----+----------+----------------------------
363
  1 | sklassen | sklassen@commandprompt.com
364
  2 | admin    | admin@yourhost.com
365
(2 rows)
366
367
sklassen=> update users set username = 'frobotz' where id = 2::bigint;
368
You cannot change the admin username.
369
UPDATE 0
370
371
sklassen=> delete from users where username = 'admin';
372
You cannot delete the admin user.
373
DELETE 0
374
375
}}}
376
377
378
== Trusted vs. Untrusted ==
379
380 9 Álvaro Herrera
Normally, PL/php is installed as a "trusted" procedural language named
381 1 bford -
'plphp'.  In this configuration, PHP will run in "safe mode".  Read
382 3 Álvaro Herrera
more about the restrictions here:
383 1 bford -
384
http://www.php.net/manual/en/features.safe-mode.functions.php
385
386
In general, the operations that are restricted are those that interact
387
with the environment.  This includes file operations, require, and use
388
(for external modules).
389
390
Since there is no way to gain access to the internals of the database
391
backend process or the operating system itself, any unprivileged
392
database user may use functions written in this language.
393
394
An example of a NON-working function due to security constraints:
395
396
397
{{{
398
CREATE FUNCTION read_passwd_file() RETURNS text AS '
399
   readfile("/etc/passwd");
400
   return 0;
401
' LANGUAGE 'plphp';
402
403
}}}
404
405
It will appear to execute, but depending on your log level, you'll may
406
see something like the following:
407
408 4 Álvaro Herrera
{{{
409
Warning: readfile(): SAFE MODE Restriction in effect. The script
410 1 bford -
whose uid is 500 is not allowed to access /etc/passwd owned by uid 0
411
in Command line code on line 3
412
413
Warning: readfile(/etc/passwd): failed to open stream: Success in
414
plphp function source on line 3
415
}}}
416
417 4 Álvaro Herrera
Sometimes it is desirable to write PHP functions that are not
418 1 bford -
restricted.  In this case, you can create the language as 'plphpu' to
419
enable the previously unavailable functions.
420
421
  
422
{{{
423
 CREATE LANGUAGE plphpu HANDLER plphp_call_handler;
424
}}}
425
426 3 Álvaro Herrera
427 1 bford -
== Composite Type Arguments ==
428
429
Composite-type arguments are passed to the function as associative
430
array. The keys of the array are the attribute names of
431
the composite type. Here is an example:
432
433
434
{{{
435
CREATE TABLE employee (
436
   name text,
437
   basesalary integer,
438
   bonus integer
439
);
440
441
CREATE FUNCTION empcomp(employee) RETURNS integer AS '
442
   return $args[0][''basesalary''] + $args[0][''bonus''];
443
' LANGUAGE 'plphp';
444
445
SELECT name, empcomp(employee) FROM employee;
446
447
}}}
448
449 6 Álvaro Herrera
== Returning A Row (composite type) ==
450 3 Álvaro Herrera
451 6 Álvaro Herrera
To return a row or composite-type value from a PL/php-language
452
function, you can use an indexed array:
453 1 bford -
454
{{{
455
CREATE TYPE __testrowphp AS (f1 integer, f2 text, f3 text);
456
457
CREATE OR REPLACE FUNCTION php_row(integer) RETURNS __testrowphp AS '
458
   $ret[f1]=$args[0];
459
   $ret[f2]="hello";
460
   $ret[f3]="world";
461
   return $ret;
462
' LANGUAGE 'plphp';
463
464
select * FROM php_row(1);
465
466
Will return:
467
 f1 |  f2   |  f3
468
----+-------+-------
469
  1 | hello | world
470
}}}
471
472
473 6 Álvaro Herrera
== Returning multiple rows (SRF functions) ==
474 1 bford -
475 6 Álvaro Herrera
SRF stands for "set-returning functions".  This means you can return
476
multiple rows, like in the following example:
477 2 Álvaro Herrera
478 1 bford -
{{{
479
CREATE TYPE __testsetphp AS (f1 integer, f2 text, f3 text);
480
481
CREATE OR REPLACE FUNCTION php_set(integer) RETURNS SETOF __testsetphp AS '
482
   $ret[0][f1]=$args[0];
483
   $ret[0][f2]="hello";
484
   $ret[0][f3]="world";
485 2 Álvaro Herrera
486 1 bford -
   $ret[1][f1]=2*$args[0];
487
   $ret[1][f2]="hello";
488
   $ret[1][f3]="postgres";
489 2 Álvaro Herrera
490 1 bford -
   $ret[2][f1]=3*$args[0];
491
   $ret[2][f2]="hello";
492
   $ret[2][f3]="plphp";
493
   return $ret;
494
' LANGUAGE 'plphp';
495
496
SELECT * FROM php_set(1);
497
498
Will return:
499
 f1 |  f2   |    f3
500
----+-------+----------
501
  1 | hello | world
502
  2 | hello | postgres
503
  3 | hello | plphp
504
505
}}}
506
507 3 Álvaro Herrera
== Limitations ==
508 1 bford -
509 9 Álvaro Herrera
PL/php functions cannot call each other directly because they are
510 1 bford -
anonymous subroutines inside PHP.
511
512 3 Álvaro Herrera
== TODO ==
513 1 bford -
514
Implement elog() function for notices.