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