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