Project

General

Profile

Documentation » History » Version 3

Álvaro Herrera, 10/30/2005 07:34 PM
Minor formatting changes

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