Documentation » History » Version 8
Álvaro Herrera, 11/16/2005 11:35 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 | 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 | 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 | 4 | Álvaro Herrera | $_TD["event"]:: |
224 | 1 | bford - | Contains one of the values: "INSERT", "UPDATE", "DELETE", or |
225 | "UNKNOWN". /note to offshore re: unknown/ |
||
226 | |||
227 | 4 | Álvaro Herrera | $_TD["when"]:: |
228 | 1 | bford - | Contains one of the values: "BEFORE", "AFTER", or "UNKNOWN". |
229 | |||
230 | 4 | Álvaro Herrera | $_TD["level"]:: |
231 | 1 | bford - | Contains one of the values: "ROW", "STATEMENT", or "UNKNOWN". |
232 | |||
233 | 4 | Álvaro Herrera | $_TD["relid"]:: |
234 | 1 | bford - | Contains the relation ID of the table on which the trigger occured. |
235 | |||
236 | 4 | Álvaro Herrera | $_TD["relname"]:: |
237 | 1 | bford - | Contains the relation name. |
238 | |||
239 | 4 | Álvaro Herrera | $_TD["args"]:: |
240 | 1 | bford - | An array of arguments passed to the trigger, if any. They can be |
241 | accessed as $_TD["args"][idx]. Example, $_TD["args"][0]. |
||
242 | |||
243 | 4 | Álvaro Herrera | $_TD["argc"]:: |
244 | 1 | bford - | The number of arguments passed to the trigger, if any. |
245 | |||
246 | 4 | Álvaro Herrera | === Example of an AFTER INSERT trigger === |
247 | 1 | bford - | |
248 | 2 | Álvaro Herrera | Suppose you have a users table with the typical columns and an |
249 | 1 | bford - | activity table that you're using to track page accesses. On row |
250 | 3 | Álvaro Herrera | |
251 | 1 | bford - | INSERT to the activity table, you want to update the last_seen field |
252 | of the appropriate user's record. |
||
253 | |||
254 | Consider the following table definitions: |
||
255 | |||
256 | |||
257 | {{{ |
||
258 | CREATE TABLE users ( |
||
259 | id serial PRIMARY KEY NOT NULL, |
||
260 | username text NOT NULL, |
||
261 | email text, |
||
262 | last_seen timestamp without time zone, |
||
263 | active boolean DEFAULT true NOT NULL |
||
264 | ); |
||
265 | |||
266 | CREATE TABLE activity ( |
||
267 | id serial PRIMARY KEY NOT NULL, |
||
268 | users_id integer NOT NULL, |
||
269 | file_accessed text NOT NULL, |
||
270 | stamp timestamp without time zone DEFAULT now() NOT NULL, |
||
271 | CONSTRAINT users_id_exists FOREIGN KEY (users_id) REFERENCES users(id) |
||
272 | ); |
||
273 | |||
274 | CREATE FUNCTION update_lastseen() RETURNS trigger AS ' |
||
275 | $new =& $_TD[''new'']; |
||
276 | |||
277 | if (isset($new[''users_id'']) && isset($new[''stamp''])) { |
||
278 | $query = "UPDATE users SET last_seen = ''" .$new[''stamp'']. |
||
279 | "'' WHERE id = " .$new[''users_id'']; |
||
280 | |||
281 | $rv = spi_exec_query($query); |
||
282 | } |
||
283 | |||
284 | return; |
||
285 | ' LANGUAGE 'plphp'; |
||
286 | |||
287 | CREATE TRIGGER after_update_lastseen_trigger |
||
288 | AFTER INSERT ON activity FOR EACH ROW EXECUTE PROCEDURE update_lastseen(); |
||
289 | |||
290 | }}} |
||
291 | |||
292 | '''1. We'll insert a new user row.''' |
||
293 | |||
294 | |||
295 | {{{ |
||
296 | sklassen=# insert into users (username, email) values ('sklassen','sklassen@commandprompt.com'); |
||
297 | INSERT 1 |
||
298 | |||
299 | sklassen=# select * from users where username = 'sklassen'; |
||
300 | |||
301 | id | username | email | last_seen | active |
||
302 | ----+----------+----------------------------+-----------+-------- |
||
303 | 1 | sklassen | sklassen@commandprompt.com | | t |
||
304 | (1 row) |
||
305 | }}} |
||
306 | |||
307 | |||
308 | '''2. Insert a new row into the activity table.''' |
||
309 | |||
310 | |||
311 | {{{ |
||
312 | sklassen=# insert into activity (users_id, file_accessed) values (1,'index.html'); |
||
313 | INSERT 1 |
||
314 | |||
315 | '''3. Check and make sure our trigger fired as expected.''' |
||
316 | |||
317 | sklassen=# select * from users where username = 'sklassen'; |
||
318 | id | username | email | last_seen | active |
||
319 | ----+----------+----------------------------+----------------------------+-------- |
||
320 | 1 | sklassen | sklassen@commandprompt.com | 2005-01-10 09:48:57.191595 | t |
||
321 | (1 row) |
||
322 | |||
323 | }}} |
||
324 | |||
325 | 4 | Álvaro Herrera | === Example of a BEFORE INSERT OR UPDATE trigger === |
326 | 1 | bford - | |
327 | Let's say we have a user named admin that we want to prevent the |
||
328 | pplication from modifying. We'll create a BEFORE DELETE trigger that |
||
329 | prevents them from deleting the row and a BEFORE UPDATE trigger that |
||
330 | prevents them modifying the username on which the previous trigger |
||
331 | depends. |
||
332 | |||
333 | |||
334 | {{{ |
||
335 | CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS ' |
||
336 | # The record may not be deleted if the username is "admin". |
||
337 | echo "You cannot delete the admin user.\n"; |
||
338 | return ($_TD[''old''][''username''] == ''admin'') ? "SKIP" : |
||
339 | "MODIFY"; |
||
340 | ' LANGUAGE 'plphp'; |
||
341 | |||
342 | CREATE TRIGGER before_delete_immortal_trigger BEFORE DELETE ON users |
||
343 | FOR EACH ROW EXECUTE PROCEDURE immortal(); |
||
344 | |||
345 | CREATE OR REPLACE FUNCTION protect_admin() RETURNS trigger AS ' |
||
346 | # Do not let them modify the username of the admin account. |
||
347 | $oldUsername =& $_TD[''old''][''username'']; |
||
348 | $newUsername =& $_TD[''new''][''username'']; |
||
349 | |||
350 | if ($oldUsername == ''admin'' && ($oldUsername != $newUsername)) { |
||
351 | echo "You cannot change the admin username.\n"; |
||
352 | return ''SKIP''; |
||
353 | } else { |
||
354 | return ''MODIFY''; |
||
355 | } |
||
356 | ' LANGUAGE 'plphp'; |
||
357 | |||
358 | CREATE TRIGGER before_update_protect_admin_trigger BEFORE UPDATE ON |
||
359 | users FOR EACH ROW EXECUTE PROCEDURE protect_admin(); |
||
360 | |||
361 | sklassen=> select * from users; |
||
362 | id | username | email |
||
363 | ----+----------+---------------------------- |
||
364 | 1 | sklassen | sklassen@commandprompt.com |
||
365 | 2 | admin | admin@yourhost.com |
||
366 | (2 rows) |
||
367 | |||
368 | sklassen=> update users set username = 'frobotz' where id = 2::bigint; |
||
369 | You cannot change the admin username. |
||
370 | UPDATE 0 |
||
371 | |||
372 | sklassen=> delete from users where username = 'admin'; |
||
373 | You cannot delete the admin user. |
||
374 | DELETE 0 |
||
375 | |||
376 | }}} |
||
377 | |||
378 | |||
379 | == Trusted vs. Untrusted == |
||
380 | |||
381 | Normally, plPHP is installed as a "trusted" procedural language named |
||
382 | 'plphp'. In this configuration, PHP will run in "safe mode". Read |
||
383 | 3 | Álvaro Herrera | more about the restrictions here: |
384 | 1 | bford - | |
385 | http://www.php.net/manual/en/features.safe-mode.functions.php |
||
386 | |||
387 | In general, the operations that are restricted are those that interact |
||
388 | with the environment. This includes file operations, require, and use |
||
389 | (for external modules). |
||
390 | |||
391 | Since there is no way to gain access to the internals of the database |
||
392 | backend process or the operating system itself, any unprivileged |
||
393 | database user may use functions written in this language. |
||
394 | |||
395 | An example of a NON-working function due to security constraints: |
||
396 | |||
397 | |||
398 | {{{ |
||
399 | CREATE FUNCTION read_passwd_file() RETURNS text AS ' |
||
400 | readfile("/etc/passwd"); |
||
401 | return 0; |
||
402 | ' LANGUAGE 'plphp'; |
||
403 | |||
404 | }}} |
||
405 | |||
406 | It will appear to execute, but depending on your log level, you'll may |
||
407 | see something like the following: |
||
408 | |||
409 | 4 | Álvaro Herrera | {{{ |
410 | Warning: readfile(): SAFE MODE Restriction in effect. The script |
||
411 | 1 | bford - | whose uid is 500 is not allowed to access /etc/passwd owned by uid 0 |
412 | in Command line code on line 3 |
||
413 | |||
414 | Warning: readfile(/etc/passwd): failed to open stream: Success in |
||
415 | plphp function source on line 3 |
||
416 | }}} |
||
417 | |||
418 | 4 | Álvaro Herrera | Sometimes it is desirable to write PHP functions that are not |
419 | 1 | bford - | restricted. In this case, you can create the language as 'plphpu' to |
420 | enable the previously unavailable functions. |
||
421 | |||
422 | |||
423 | {{{ |
||
424 | CREATE LANGUAGE plphpu HANDLER plphp_call_handler; |
||
425 | }}} |
||
426 | |||
427 | 3 | Álvaro Herrera | |
428 | 1 | bford - | == Composite Type Arguments == |
429 | |||
430 | Composite-type arguments are passed to the function as associative |
||
431 | array. The keys of the array are the attribute names of |
||
432 | the composite type. Here is an example: |
||
433 | |||
434 | |||
435 | {{{ |
||
436 | CREATE TABLE employee ( |
||
437 | name text, |
||
438 | basesalary integer, |
||
439 | bonus integer |
||
440 | ); |
||
441 | |||
442 | CREATE FUNCTION empcomp(employee) RETURNS integer AS ' |
||
443 | return $args[0][''basesalary''] + $args[0][''bonus'']; |
||
444 | ' LANGUAGE 'plphp'; |
||
445 | |||
446 | SELECT name, empcomp(employee) FROM employee; |
||
447 | |||
448 | }}} |
||
449 | |||
450 | 6 | Álvaro Herrera | == Returning A Row (composite type) == |
451 | 3 | Álvaro Herrera | |
452 | 6 | Álvaro Herrera | To return a row or composite-type value from a PL/php-language |
453 | function, you can use an indexed array: |
||
454 | 1 | bford - | |
455 | {{{ |
||
456 | CREATE TYPE __testrowphp AS (f1 integer, f2 text, f3 text); |
||
457 | |||
458 | CREATE OR REPLACE FUNCTION php_row(integer) RETURNS __testrowphp AS ' |
||
459 | $ret[f1]=$args[0]; |
||
460 | $ret[f2]="hello"; |
||
461 | $ret[f3]="world"; |
||
462 | return $ret; |
||
463 | ' LANGUAGE 'plphp'; |
||
464 | |||
465 | select * FROM php_row(1); |
||
466 | |||
467 | Will return: |
||
468 | f1 | f2 | f3 |
||
469 | ----+-------+------- |
||
470 | 1 | hello | world |
||
471 | }}} |
||
472 | |||
473 | |||
474 | 6 | Álvaro Herrera | == Returning multiple rows (SRF functions) == |
475 | 1 | bford - | |
476 | 6 | Álvaro Herrera | SRF stands for "set-returning functions". This means you can return |
477 | multiple rows, like in the following example: |
||
478 | 2 | Álvaro Herrera | |
479 | 1 | bford - | {{{ |
480 | CREATE TYPE __testsetphp AS (f1 integer, f2 text, f3 text); |
||
481 | |||
482 | CREATE OR REPLACE FUNCTION php_set(integer) RETURNS SETOF __testsetphp AS ' |
||
483 | $ret[0][f1]=$args[0]; |
||
484 | $ret[0][f2]="hello"; |
||
485 | $ret[0][f3]="world"; |
||
486 | 2 | Álvaro Herrera | |
487 | 1 | bford - | $ret[1][f1]=2*$args[0]; |
488 | $ret[1][f2]="hello"; |
||
489 | $ret[1][f3]="postgres"; |
||
490 | 2 | Álvaro Herrera | |
491 | 1 | bford - | $ret[2][f1]=3*$args[0]; |
492 | $ret[2][f2]="hello"; |
||
493 | $ret[2][f3]="plphp"; |
||
494 | return $ret; |
||
495 | ' LANGUAGE 'plphp'; |
||
496 | |||
497 | SELECT * FROM php_set(1); |
||
498 | |||
499 | Will return: |
||
500 | f1 | f2 | f3 |
||
501 | ----+-------+---------- |
||
502 | 1 | hello | world |
||
503 | 2 | hello | postgres |
||
504 | 3 | hello | plphp |
||
505 | |||
506 | }}} |
||
507 | |||
508 | 3 | Álvaro Herrera | == Limitations == |
509 | 1 | bford - | |
510 | plPHP functions cannot call each other directly because they are |
||
511 | anonymous subroutines inside PHP. |
||
512 | |||
513 | 3 | Álvaro Herrera | == TODO == |
514 | 1 | bford - | |
515 | Implement elog() function for notices. |