Project

General

Profile

Documentation » History » Version 2

Álvaro Herrera, 10/29/2005 03:07 PM
Minor copy-editing.

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