« back to all changes in this revision
Viewing changes to doc/plr-spi-rsupport-funcs.html
- browse files at revision 4
- compare with another revision
- download diff
- download tarball
- view history from revision 4
- Committer: Bazaar Package Importer
- Author(s): Martin Pitt
- Date: 2006-07-23 10:21:03 UTC
- mfrom: (3.1.2 edgy)
- Revision ID: james.westby@ubuntu.com-20060723102103-bs3qic50imfbhxmt
Tags: 1:0.6.2-4
Build on mips and mipsel again, now that the PostgreSQL SIGBUS problem
turned out to be a kernel bug (which is fixed in 2.6.16).
turned out to be a kernel bug (which is fixed in 2.6.16).
- files added:
- debian/Makefile.Debian.7.4
- files removed:
- Makefile
- doc
- expected
- sql
- files modified:
- debian/changelog
added
removed
doc/plr-spi-rsupport-funcs.html
1
<HTML
2
><HEAD
3
><TITLE
4
>Database Access and Support Functions</TITLE
5
><META
6
NAME="GENERATOR"
7
CONTENT="Modular DocBook HTML Stylesheet Version 1.76b+
8
"><LINK
9
REV="MADE"
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
11
REL="HOME"
12
TITLE="PL/R User's Guide - R Procedural Language"
13
HREF="index.html"><LINK
14
REL="PREVIOUS"
15
TITLE="Using Global Data"
16
HREF="plr-global-data.html"><LINK
17
REL="NEXT"
18
TITLE="PostgreSQL Support Functions"
19
HREF="plr-pgsql-support-funcs.html"><LINK
20
REL="STYLESHEET"
21
TYPE="text/css"
22
HREF="stylesheet.css"><META
23
NAME="creation"
24
CONTENT="2003-08-25T17:44:28"></HEAD
25
><BODY
26
CLASS="CHAPTER"
27
BGCOLOR="#FFFFFF"
28
TEXT="#000000"
29
LINK="#0000FF"
30
VLINK="#840084"
31
ALINK="#0000FF"
32
><DIV
33
CLASS="NAVHEADER"
34
><TABLE
35
SUMMARY="Header navigation table"
36
WIDTH="100%"
37
BORDER="0"
38
CELLPADDING="0"
39
CELLSPACING="0"
40
><TR
41
><TH
42
COLSPAN="3"
43
ALIGN="center"
44
>PL/R User's Guide - R Procedural Language</TH
45
></TR
46
><TR
47
><TD
48
WIDTH="10%"
49
ALIGN="left"
50
VALIGN="bottom"
51
><A
52
HREF="plr-global-data.html"
53
ACCESSKEY="P"
54
>Prev</A
55
></TD
56
><TD
57
WIDTH="80%"
58
ALIGN="center"
59
VALIGN="bottom"
60
></TD
61
><TD
62
WIDTH="10%"
63
ALIGN="right"
64
VALIGN="bottom"
65
><A
66
HREF="plr-pgsql-support-funcs.html"
67
ACCESSKEY="N"
68
>Next</A
69
></TD
70
></TR
71
></TABLE
72
><HR
73
ALIGN="LEFT"
74
WIDTH="100%"></DIV
75
><DIV
76
CLASS="CHAPTER"
77
><H1
78
><A
79
NAME="PLR-SPI-RSUPPORT-FUNCS"
80
></A
81
>Chapter 6. Database Access and Support Functions</H1
82
><P
83
> The following commands are available to access the database from
84
the body of a PL/R procedure, or in support thereof:
85
</P
86
><P
87
></P
88
><DIV
89
CLASS="VARIABLELIST"
90
><DL
91
><DT
92
><TT
93
CLASS="FUNCTION"
94
>pg.spi.exec</TT
95
>
96
(<TT
97
CLASS="TYPE"
98
>character</TT
99
> <TT
100
CLASS="REPLACEABLE"
101
><I
102
>query</I
103
></TT
104
>)</DT
105
><DD
106
><P
107
> Execute an SQL query given as a string. An error in the query
108
causes an error to be raised. Otherwise, the command's return value
109
is the number of rows processed for <TT
110
CLASS="COMMAND"
111
>INSERT</TT
112
>,
113
<TT
114
CLASS="COMMAND"
115
>UPDATE</TT
116
>, or <TT
117
CLASS="COMMAND"
118
>DELETE</TT
119
> statements,
120
or zero if the query is a utility statement. If the query is a
121
<TT
122
CLASS="COMMAND"
123
>SELECT</TT
124
> statement, the values of the selected columns
125
are placed in an R data.frame with the target column names used as
126
the frame column names. However, non-numeric columns <SPAN
127
CLASS="emphasis"
128
><I
129
CLASS="EMPHASIS"
130
>are
131
not</I
132
></SPAN
133
> converted to factors. If you want all non-numeric
134
columns converted to factors, a convenience function <TT
135
CLASS="FUNCTION"
136
> pg.spi.factor</TT
137
> (described below) is provided.
138
</P
139
><P
140
> If a field of a SELECT result is NULL, the target variable for it
141
is set to <SPAN
142
CLASS="QUOTE"
143
>"NA"</SPAN
144
>. For example:
145
</P><PRE
146
CLASS="PROGRAMLISTING"
147
>create or replace function test_spi_tup(text) returns record as '
148
pg.spi.exec(arg1)
149
' language 'plr';
150
151
select * from test_spi_tup('select oid, NULL::text as nullcol,
152
typname from pg_type where typname = ''oid'' or typname = ''text''')
153
as t(typeid oid, nullcol text, typename name);
154
typeid | nullcol | typename
155
--------+---------+----------
156
25 | | text
157
26 | | oid
158
(2 rows)
159
</PRE
160
><P>
161
The NULL values were passed to R as <SPAN
162
CLASS="QUOTE"
163
>"NA"</SPAN
164
>, and on return to
165
PostgreSQL they were converted back to NULL.
166
</P
167
></DD
168
><DT
169
><TT
170
CLASS="FUNCTION"
171
>pg.spi.prepare</TT
172
>
173
(<TT
174
CLASS="TYPE"
175
>character</TT
176
> <TT
177
CLASS="REPLACEABLE"
178
><I
179
>query</I
180
></TT
181
>,
182
<TT
183
CLASS="TYPE"
184
>integer vector</TT
185
> <TT
186
CLASS="REPLACEABLE"
187
><I
188
>type_vector</I
189
></TT
190
>)</DT
191
><DD
192
><P
193
> Prepares and saves a query plan for later execution. The saved plan
194
will be retained for the life of the current backend.
195
</P
196
><P
197
> The query may use <I
198
CLASS="FIRSTTERM"
199
>arguments</I
200
>, which are
201
placeholders for values to be supplied whenever the plan is actually
202
executed. In the query string, refer to arguments by the symbols
203
<TT
204
CLASS="LITERAL"
205
>$1</TT
206
> ... <TT
207
CLASS="LITERAL"
208
>$n</TT
209
>. If the query uses
210
arguments, the values of the argument types must be given as a vector.
211
Pass <TT
212
CLASS="LITERAL"
213
>NA</TT
214
> for <TT
215
CLASS="REPLACEABLE"
216
><I
217
>type_vector</I
218
></TT
219
>
220
if the query has no arguments. The argument types must be identified
221
by the type Oids, shown in pg_type. Global variables are provided for
222
this use. They are named according to the convention TYPENAMEOID, where
223
the actual name of the type, in all capitals, is substituted for
224
TYPENAME. A support function, <TT
225
CLASS="FUNCTION"
226
>r_typenames()</TT
227
> can be
228
used to list the predefined Global variables:
229
</P><PRE
230
CLASS="PROGRAMLISTING"
231
>select * from r_typenames();
232
typename | typeoid
233
-----------------+---------
234
ABSTIMEOID | 702
235
ACLITEMOID | 1033
236
ANYARRAYOID | 2277
237
ANYOID | 2276
238
BITOID | 1560
239
BOOLOID | 16
240
[...]
241
TRIGGEROID | 2279
242
UNKNOWNOID | 705
243
VARBITOID | 1562
244
VARCHAROID | 1043
245
VOIDOID | 2278
246
XIDOID | 28
247
(59 rows)
248
</PRE
249
><P>
250
</P
251
><P
252
> The return value from <TT
253
CLASS="FUNCTION"
254
>pg.spi.prepare</TT
255
> is a query ID
256
to be used in subsequent calls to <TT
257
CLASS="FUNCTION"
258
>pg.spi.execp</TT
259
>. See
260
<TT
261
CLASS="FUNCTION"
262
>spi_execp</TT
263
> for an example.
264
</P
265
></DD
266
><DT
267
><TT
268
CLASS="FUNCTION"
269
>pg.spi.execp</TT
270
>
271
(<TT
272
CLASS="TYPE"
273
>external pointer</TT
274
> <TT
275
CLASS="REPLACEABLE"
276
><I
277
>saved_plan</I
278
></TT
279
>,
280
<TT
281
CLASS="TYPE"
282
>variable list</TT
283
><TT
284
CLASS="REPLACEABLE"
285
><I
286
>value_list</I
287
></TT
288
>)</DT
289
><DD
290
><P
291
> Execute a query previously prepared with <TT
292
CLASS="FUNCTION"
293
>pg.spi.prepare
294
</TT
295
>. <TT
296
CLASS="REPLACEABLE"
297
><I
298
>saved_plan</I
299
></TT
300
> is the external
301
pointer returned by <TT
302
CLASS="FUNCTION"
303
>pg.spi.prepare</TT
304
>. If the query
305
references arguments, a <TT
306
CLASS="REPLACEABLE"
307
><I
308
>value_list</I
309
></TT
310
> must
311
be supplied: this is an R list of actual values for the plan arguments.
312
It must be the same length as the argument <TT
313
CLASS="REPLACEABLE"
314
><I
315
>type_vector
316
</I
317
></TT
318
> previously given to <TT
319
CLASS="FUNCTION"
320
>pg.spi.prepare</TT
321
>.
322
Pass <TT
323
CLASS="LITERAL"
324
>NA</TT
325
> for <TT
326
CLASS="REPLACEABLE"
327
><I
328
>value_list</I
329
></TT
330
>
331
if the query has no arguments. The following illustrates the use of
332
<TT
333
CLASS="FUNCTION"
334
>pg.spi.prepare</TT
335
> and <TT
336
CLASS="FUNCTION"
337
>pg.spi.execp</TT
338
>
339
with and without query arguments:
340
</P><PRE
341
CLASS="PROGRAMLISTING"
342
>create or replace function test_spi_prep(text) returns text as '
343
sp <<- pg.spi.prepare(arg1, c(NAMEOID, NAMEOID));
344
print("OK")
345
' language 'plr';
346
347
select test_spi_prep('select oid, typname from pg_type
348
where typname = $1 or typname = $2');
349
test_spi_prep
350
---------------
351
OK
352
(1 row)
353
354
create or replace function test_spi_execp(text, text, text) returns record as '
355
pg.spi.execp(pg.reval(arg1), list(arg2,arg3))
356
' language 'plr';
357
358
select * from test_spi_execp('sp','oid','text') as t(typeid oid, typename name);
359
typeid | typename
360
--------+----------
361
25 | text
362
26 | oid
363
(2 rows)
364
365
create or replace function test_spi_prep(text) returns text as '
366
sp <<- pg.spi.prepare(arg1, NA);
367
print("OK")
368
' language 'plr';
369
370
select test_spi_prep('select oid, typname from pg_type
371
where typname = ''bytea'' or typname = ''text''');
372
test_spi_prep
373
---------------
374
OK
375
(1 row)
376
377
create or replace function test_spi_execp(text) returns setof record as '
378
pg.spi.execp(pg.reval(arg1), NA)
379
' language 'plr';
380
381
select * from test_spi_execp('sp') as t(typeid oid, typename name);
382
typeid | typename
383
--------+----------
384
17 | bytea
385
25 | text
386
(2 rows)
387
388
create or replace function test_spi_prep(text) returns text as '
389
sp <<- pg.spi.prepare(arg1);
390
print("OK")
391
' language 'plr';
392
393
select test_spi_prep('select oid, typname from pg_type
394
where typname = ''bytea'' or typname = ''text''');
395
test_spi_prep
396
---------------
397
OK
398
(1 row)
399
400
create or replace function test_spi_execp(text) returns setof record as '
401
pg.spi.execp(pg.reval(arg1))
402
' language 'plr';
403
404
select * from test_spi_execp('sp') as t(typeid oid, typename name);
405
typeid | typename
406
--------+----------
407
17 | bytea
408
25 | text
409
(2 rows)
410
</PRE
411
><P>
412
</P
413
><P
414
> NULL arguments should be passed as individual <TT
415
CLASS="LITERAL"
416
>NA</TT
417
> values
418
in <TT
419
CLASS="REPLACEABLE"
420
><I
421
>value_list</I
422
></TT
423
>.
424
</P
425
><P
426
> Except for the way in which the query and its arguments are specified,
427
<TT
428
CLASS="FUNCTION"
429
>pg.spi.execp</TT
430
> works just like
431
<TT
432
CLASS="FUNCTION"
433
>pg.spi.exec</TT
434
>.
435
</P
436
></DD
437
><DT
438
><TT
439
CLASS="FUNCTION"
440
>pg.spi.lastoid</TT
441
>()</DT
442
><DD
443
><P
444
> Returns the OID of the row inserted by the last query executed via
445
<TT
446
CLASS="FUNCTION"
447
>pg.spi.exec</TT
448
> or <TT
449
CLASS="FUNCTION"
450
>pg.spi.execp</TT
451
>,
452
if that query was a single-row INSERT. (If not, you get zero.)
453
</P
454
></DD
455
><DT
456
><TT
457
CLASS="FUNCTION"
458
>pg.quoteliteral</TT
459
>
460
(<TT
461
CLASS="TYPE"
462
>character</TT
463
> <TT
464
CLASS="REPLACEABLE"
465
><I
466
>SQL_string</I
467
></TT
468
>)</DT
469
><DD
470
><P
471
> Duplicates all occurrences of single quote and backslash characters
472
in the given string. This may be used to safely quote strings
473
that are to be inserted into SQL queries given to
474
<TT
475
CLASS="FUNCTION"
476
>pg.spi.exec</TT
477
> or <TT
478
CLASS="FUNCTION"
479
>pg.spi.prepare</TT
480
>.
481
</P
482
></DD
483
><DT
484
><TT
485
CLASS="FUNCTION"
486
>pg.quoteident</TT
487
>
488
(<TT
489
CLASS="TYPE"
490
>character</TT
491
> <TT
492
CLASS="REPLACEABLE"
493
><I
494
>SQL_string</I
495
></TT
496
>)</DT
497
><DD
498
><P
499
> Return the given string suitably quoted to be used as an identifier
500
in an SQL query string. Quotes are added only if necessary (i.e., if
501
the string contains non-identifier characters or would be case-folded).
502
Embedded quotes are properly doubled. This may be used to safely quote
503
strings that are to be inserted into SQL queries given to
504
<TT
505
CLASS="FUNCTION"
506
>pg.spi.exec</TT
507
> or <TT
508
CLASS="FUNCTION"
509
>pg.spi.prepare</TT
510
>.
511
</P
512
></DD
513
><DT
514
><TT
515
CLASS="FUNCTION"
516
>pg.thrownotice</TT
517
>
518
(<TT
519
CLASS="TYPE"
520
>character</TT
521
> <TT
522
CLASS="REPLACEABLE"
523
><I
524
>message</I
525
></TT
526
>)<BR><TT
527
CLASS="FUNCTION"
528
>pg.throwerror</TT
529
>
530
(<TT
531
CLASS="TYPE"
532
>character</TT
533
> <TT
534
CLASS="REPLACEABLE"
535
><I
536
>message</I
537
></TT
538
>)</DT
539
><DD
540
><P
541
> Emit a PostgreSQL <TT
542
CLASS="LITERAL"
543
>NOTICE</TT
544
> or <TT
545
CLASS="LITERAL"
546
>ERROR</TT
547
>
548
message. <TT
549
CLASS="LITERAL"
550
>ERROR</TT
551
> also raises an error condition:
552
further execution of the function is abandoned, and the current
553
transaction is aborted.
554
</P
555
></DD
556
><DT
557
><TT
558
CLASS="FUNCTION"
559
>pg.spi.factor</TT
560
>
561
(<TT
562
CLASS="TYPE"
563
>data.frame</TT
564
> <TT
565
CLASS="REPLACEABLE"
566
><I
567
>data</I
568
></TT
569
>)</DT
570
><DD
571
><P
572
> Accepts an R <TT
573
CLASS="TYPE"
574
>data.frame</TT
575
> as input, and converts all
576
non-numeric columns to <TT
577
CLASS="TYPE"
578
>factor</TT
579
>s. This may be useful
580
for data.frames produced by <TT
581
CLASS="FUNCTION"
582
>pg.spi.exec</TT
583
> or
584
<TT
585
CLASS="FUNCTION"
586
>pg.spi.prepare</TT
587
>, because the PL/R conversion
588
mechanism does <SPAN
589
CLASS="emphasis"
590
><I
591
CLASS="EMPHASIS"
592
>not</I
593
></SPAN
594
> do that for you.
595
</P
596
></DD
597
></DL
598
></DIV
599
></DIV
600
><DIV
601
CLASS="NAVFOOTER"
602
><HR
603
ALIGN="LEFT"
604
WIDTH="100%"><TABLE
605
SUMMARY="Footer navigation table"
606
WIDTH="100%"
607
BORDER="0"
608
CELLPADDING="0"
609
CELLSPACING="0"
610
><TR
611
><TD
612
WIDTH="33%"
613
ALIGN="left"
614
VALIGN="top"
615
><A
616
HREF="plr-global-data.html"
617
ACCESSKEY="P"
618
>Prev</A
619
></TD
620
><TD
621
WIDTH="34%"
622
ALIGN="center"
623
VALIGN="top"
624
><A
625
HREF="index.html"
626
ACCESSKEY="H"
627
>Home</A
628
></TD
629
><TD
630
WIDTH="33%"
631
ALIGN="right"
632
VALIGN="top"
633
><A
634
HREF="plr-pgsql-support-funcs.html"
635
ACCESSKEY="N"
636
>Next</A
637
></TD
638
></TR
639
><TR
640
><TD
641
WIDTH="33%"
642
ALIGN="left"
643
VALIGN="top"
644
>Using Global Data</TD
645
><TD
646
WIDTH="34%"
647
ALIGN="center"
648
VALIGN="top"
649
> </TD
650
><TD
651
WIDTH="33%"
652
ALIGN="right"
653
VALIGN="top"
654
>PostgreSQL Support Functions</TD
655
></TR
656
></TABLE
657
></DIV
658
></BODY
659
></HTML
660
>
b'\\ No newline at end of file'
Loggerhead is a web-based interface for Breezy
Version: 2.0.1