begriffs open source - ai-pg/blob - full-docs/txt/plperl-builtins.txt

   1
   2 43.3. Built-in Functions #
   3
   4    43.3.1. Database Access from PL/Perl
   5    43.3.2. Utility Functions in PL/Perl
   6
   7 43.3.1. Database Access from PL/Perl #
   8
   9    Access to the database itself from your Perl function can be done via
  10    the following functions:
  11
  12    spi_exec_query(query [, limit])
  13           spi_exec_query executes an SQL command and returns the entire
  14           row set as a reference to an array of hash references. If limit
  15           is specified and is greater than zero, then spi_exec_query
  16           retrieves at most limit rows, much as if the query included a
  17           LIMIT clause. Omitting limit or specifying it as zero results in
  18           no row limit.
  19
  20           You should only use this command when you know that the result
  21           set will be relatively small. Here is an example of a query
  22           (SELECT command) with the optional maximum number of rows:
  23
  24 $rv = spi_exec_query('SELECT * FROM my_table', 5);
  25
  26           This returns up to 5 rows from the table my_table. If my_table
  27           has a column my_column, you can get that value from row $i of
  28           the result like this:
  29
  30 $foo = $rv->{rows}[$i]->{my_column};
  31
  32           The total number of rows returned from a SELECT query can be
  33           accessed like this:
  34
  35 $nrows = $rv->{processed}
  36
  37           Here is an example using a different command type:
  38
  39 $query = "INSERT INTO my_table VALUES (1, 'test')";
  40 $rv = spi_exec_query($query);
  41
  42           You can then access the command status (e.g., SPI_OK_INSERT)
  43           like this:
  44
  45 $res = $rv->{status};
  46
  47           To get the number of rows affected, do:
  48
  49 $nrows = $rv->{processed};
  50
  51           Here is a complete example:
  52
  53 CREATE TABLE test (
  54     i int,
  55     v varchar
  56 );
  57
  58 INSERT INTO test (i, v) VALUES (1, 'first line');
  59 INSERT INTO test (i, v) VALUES (2, 'second line');
  60 INSERT INTO test (i, v) VALUES (3, 'third line');
  61 INSERT INTO test (i, v) VALUES (4, 'immortal');
  62
  63 CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
  64     my $rv = spi_exec_query('select i, v from test;');
  65     my $status = $rv->{status};
  66     my $nrows = $rv->{processed};
  67     foreach my $rn (0 .. $nrows - 1) {
  68         my $row = $rv->{rows}[$rn];
  69         $row->{i} += 200 if defined($row->{i});
  70         $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
  71         return_next($row);
  72     }
  73     return undef;
  74 $$ LANGUAGE plperl;
  75
  76 SELECT * FROM test_munge();
  77
  78    spi_query(command)
  79           spi_fetchrow(cursor)
  80           spi_cursor_close(cursor)
  81           spi_query and spi_fetchrow work together as a pair for row sets
  82           which might be large, or for cases where you wish to return rows
  83           as they arrive. spi_fetchrow works only with spi_query. The
  84           following example illustrates how you use them together:
  85
  86 CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
  87
  88 CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
  89     use Digest::MD5 qw(md5_hex);
  90     my $file = '/usr/share/dict/words';
  91     my $t = localtime;
  92     elog(NOTICE, "opening file $file at $t" );
  93     open my $fh, '<', $file # ooh, it's a file access!
  94         or elog(ERROR, "cannot open $file for reading: $!");
  95     my @words = <$fh>;
  96     close $fh;
  97     $t = localtime;
  98     elog(NOTICE, "closed file $file at $t");
  99     chomp(@words);
 100     my $row;
 101     my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
 102     while (defined ($row = spi_fetchrow($sth))) {
 103         return_next({
 104             the_num => $row->{a},
 105             the_text => md5_hex($words[rand @words])
 106         });
 107     }
 108     return;
 109 $$ LANGUAGE plperlu;
 110
 111 SELECT * from lotsa_md5(500);
 112
 113           Normally, spi_fetchrow should be repeated until it returns
 114           undef, indicating that there are no more rows to read. The
 115           cursor returned by spi_query is automatically freed when
 116           spi_fetchrow returns undef. If you do not wish to read all the
 117           rows, instead call spi_cursor_close to free the cursor. Failure
 118           to do so will result in memory leaks.
 119
 120    spi_prepare(command, argument types)
 121           spi_query_prepared(plan, arguments)
 122           spi_exec_prepared(plan [, attributes], arguments)
 123           spi_freeplan(plan)
 124           spi_prepare, spi_query_prepared, spi_exec_prepared, and
 125           spi_freeplan implement the same functionality but for prepared
 126           queries. spi_prepare accepts a query string with numbered
 127           argument placeholders ($1, $2, etc.) and a string list of
 128           argument types:
 129
 130 $plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2',
 131                                                      'INTEGER', 'TEXT');
 132
 133           Once a query plan is prepared by a call to spi_prepare, the plan
 134           can be used instead of the string query, either in
 135           spi_exec_prepared, where the result is the same as returned by
 136           spi_exec_query, or in spi_query_prepared which returns a cursor
 137           exactly as spi_query does, which can be later passed to
 138           spi_fetchrow. The optional second parameter to spi_exec_prepared
 139           is a hash reference of attributes; the only attribute currently
 140           supported is limit, which sets the maximum number of rows
 141           returned from the query. Omitting limit or specifying it as zero
 142           results in no row limit.
 143
 144           The advantage of prepared queries is that is it possible to use
 145           one prepared plan for more than one query execution. After the
 146           plan is not needed anymore, it can be freed with spi_freeplan:
 147
 148 CREATE OR REPLACE FUNCTION init() RETURNS VOID AS $$
 149         $_SHARED{my_plan} = spi_prepare('SELECT (now() + $1)::date AS now',
 150                                         'INTERVAL');
 151 $$ LANGUAGE plperl;
 152
 153 CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
 154         return spi_exec_prepared(
 155                 $_SHARED{my_plan},
 156                 $_[0]
 157         )->{rows}->[0]->{now};
 158 $$ LANGUAGE plperl;
 159
 160 CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$
 161         spi_freeplan( $_SHARED{my_plan});
 162         undef $_SHARED{my_plan};
 163 $$ LANGUAGE plperl;
 164
 165 SELECT init();
 166 SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
 167 SELECT done();
 168
 169   add_time  |  add_time  |  add_time
 170 ------------+------------+------------
 171  2005-12-10 | 2005-12-11 | 2005-12-12
 172
 173           Note that the parameter subscript in spi_prepare is defined via
 174           $1, $2, $3, etc., so avoid declaring query strings in double
 175           quotes that might easily lead to hard-to-catch bugs.
 176
 177           Another example illustrates usage of an optional parameter in
 178           spi_exec_prepared:
 179
 180 CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address
 181                       FROM generate_series(1,3) AS id;
 182
 183 CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$
 184         $_SHARED{plan} = spi_prepare('SELECT * FROM hosts
 185                                       WHERE address << $1', 'inet');
 186 $$ LANGUAGE plperl;
 187
 188 CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$
 189         return spi_exec_prepared(
 190                 $_SHARED{plan},
 191                 {limit => 2},
 192                 $_[0]
 193         )->{rows};
 194 $$ LANGUAGE plperl;
 195
 196 CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$
 197         spi_freeplan($_SHARED{plan});
 198         undef $_SHARED{plan};
 199 $$ LANGUAGE plperl;
 200
 201 SELECT init_hosts_query();
 202 SELECT query_hosts('192.168.1.0/30');
 203 SELECT release_hosts_query();
 204
 205     query_hosts
 206 -----------------
 207  (1,192.168.1.1)
 208  (2,192.168.1.2)
 209 (2 rows)
 210
 211    spi_commit()
 212           spi_rollback()
 213           Commit or roll back the current transaction. This can only be
 214           called in a procedure or anonymous code block (DO command)
 215           called from the top level. (Note that it is not possible to run
 216           the SQL commands COMMIT or ROLLBACK via spi_exec_query or
 217           similar. It has to be done using these functions.) After a
 218           transaction is ended, a new transaction is automatically
 219           started, so there is no separate function for that.
 220
 221           Here is an example:
 222
 223 CREATE PROCEDURE transaction_test1()
 224 LANGUAGE plperl
 225 AS $$
 226 foreach my $i (0..9) {
 227     spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
 228     if ($i % 2 == 0) {
 229         spi_commit();
 230     } else {
 231         spi_rollback();
 232     }
 233 }
 234 $$;
 235
 236 CALL transaction_test1();
 237
 238 43.3.2. Utility Functions in PL/Perl #
 239
 240    elog(level, msg)
 241           Emit a log or error message. Possible levels are DEBUG, LOG,
 242           INFO, NOTICE, WARNING, and ERROR. ERROR raises an error
 243           condition; if this is not trapped by the surrounding Perl code,
 244           the error propagates out to the calling query, causing the
 245           current transaction or subtransaction to be aborted. This is
 246           effectively the same as the Perl die command. The other levels
 247           only generate messages of different priority levels. Whether
 248           messages of a particular priority are reported to the client,
 249           written to the server log, or both is controlled by the
 250           log_min_messages and client_min_messages configuration
 251           variables. See Chapter 19 for more information.
 252
 253    quote_literal(string)
 254           Return the given string suitably quoted to be used as a string
 255           literal in an SQL statement string. Embedded single-quotes and
 256           backslashes are properly doubled. Note that quote_literal
 257           returns undef on undef input; if the argument might be undef,
 258           quote_nullable is often more suitable.
 259
 260    quote_nullable(string)
 261           Return the given string suitably quoted to be used as a string
 262           literal in an SQL statement string; or, if the argument is
 263           undef, return the unquoted string "NULL". Embedded single-quotes
 264           and backslashes are properly doubled.
 265
 266    quote_ident(string)
 267           Return the given string suitably quoted to be used as an
 268           identifier in an SQL statement string. Quotes are added only if
 269           necessary (i.e., if the string contains non-identifier
 270           characters or would be case-folded). Embedded quotes are
 271           properly doubled.
 272
 273    decode_bytea(string)
 274           Return the unescaped binary data represented by the contents of
 275           the given string, which should be bytea encoded.
 276
 277    encode_bytea(string)
 278           Return the bytea encoded form of the binary data contents of the
 279           given string.
 280
 281    encode_array_literal(array)
 282           encode_array_literal(array, delimiter)
 283           Returns the contents of the referenced array as a string in
 284           array literal format (see Section 8.15.2). Returns the argument
 285           value unaltered if it's not a reference to an array. The
 286           delimiter used between elements of the array literal defaults to
 287           ", " if a delimiter is not specified or is undef.
 288
 289    encode_typed_literal(value, typename)
 290           Converts a Perl variable to the value of the data type passed as
 291           a second argument and returns a string representation of this
 292           value. Correctly handles nested arrays and values of composite
 293           types.
 294
 295    encode_array_constructor(array)
 296           Returns the contents of the referenced array as a string in
 297           array constructor format (see Section 4.2.12). Individual values
 298           are quoted using quote_nullable. Returns the argument value,
 299           quoted using quote_nullable, if it's not a reference to an
 300           array.
 301
 302    looks_like_number(string)
 303           Returns a true value if the content of the given string looks
 304           like a number, according to Perl, returns false otherwise.
 305           Returns undef if the argument is undef. Leading and trailing
 306           space is ignored. Inf and Infinity are regarded as numbers.
 307
 308    is_array_ref(argument)
 309           Returns a true value if the given argument may be treated as an
 310           array reference, that is, if ref of the argument is ARRAY or
 311           PostgreSQL::InServer::ARRAY. Returns false otherwise.