Project

General

Profile

Documentation » History » Version 7

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