43.3. Встроенные функции#

43.3. Встроенные функции
43.3. Встроенные функции
Назад НаверхГлава 43. PL/Perl — Perl Процедурный ЯзыкНачало Далее

43.3. Встроенные функции

43.3.1. Доступ к базе данных из PL/Perl
43.3.2. Утилитарные функции в PL/Perl

43.3.1. Доступ к базе данных из PL/Perl

Доступ к самой базе данных из вашей Perl-функции можно осуществить с помощью следующих функций:

spi_exec_query(query [, limit])

spi_exec_query выполняет SQL-команду и возвращает весь набор строк в виде ссылки на массив ссылок на хеш. Если указано limit и оно больше нуля, то spi_exec_query извлекает не более limit строк, как если бы в запросе было указано предложение LIMIT. Пропуск limit или его указание как ноль приводит к отсутствию ограничения на количество строк.

Вы должны использовать эту команду только тогда, когда знаете, что набор результатов будет относительно небольшим. Вот пример запроса (SELECT команда) с необязательным максимальным числом строк: 

$rv = spi_exec_query('SELECT * FROM my_table', 5);

Это возвращает до 5 строк из таблицы my_table. Если у my_table есть столбец my_column, вы можете получить его значение из строки $i результата таким образом: 

$foo = $rv->{rows}[$i]->{my_column};

Общее количество строк, возвращаемых из запроса SELECT, можно получить следующим образом: 

$nrows = $rv->{processed}

Вот пример использования другого типа команды: 

$query = "INSERT INTO my_table VALUES (1, 'test')";
$rv = spi_exec_query($query);

Вы можете затем получить доступ к статусу команды (например, SPI_OK_INSERT) следующим образом: 

$res = $rv->{status};

Чтобы получить количество затронутых строк, выполните: 

$nrows = $rv->{processed};

Вот полный пример: 

CREATE TABLE test (
    i int,
    v varchar
);

INSERT INTO test (i, v) VALUES (1, 'first line');
INSERT INTO test (i, v) VALUES (2, 'second line');
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');

CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
    my $rv = spi_exec_query('select i, v from test;');
    my $status = $rv->{status};
    my $nrows = $rv->{processed};
    foreach my $rn (0 .. $nrows - 1) {
        my $row = $rv->{rows}[$rn];
        $row->{i} += 200 if defined($row->{i});
        $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
        return_next($row);
    }
    return undef;
$$ LANGUAGE plperl;

SELECT * FROM test_munge();

spi_query(command)
spi_fetchrow(cursor)
spi_cursor_close(cursor)

spi_query и spi_fetchrow работают вместе в паре для наборов строк, которые могут быть большими, или для случаев, когда вы хотите возвращать строки по мере их поступления. spi_fetchrow работает только с spi_query. Следующий пример иллюстрирует, как их использовать вместе: 

CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);

CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
    use Digest::MD5 qw(md5_hex);
    my $file = '/usr/share/dict/words';
    my $t = localtime;
    elog(NOTICE, "opening file $file at $t" );
    open my $fh, '<', $file # ooh, it's a file access!
        or elog(ERROR, "cannot open $file for reading: $!");
    my @words = <$fh>;
    close $fh;
    $t = localtime;
    elog(NOTICE, "closed file $file at $t");
    chomp(@words);
    my $row;
    my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
    while (defined ($row = spi_fetchrow($sth))) {
        return_next({
            the_num => $row->{a},
            the_text => md5_hex($words[rand @words])
        });
    }
    return;
$$ LANGUAGE plperlu;

SELECT * from lotsa_md5(500);

Обычно функцию spi_fetchrow следует повторять до тех пор, пока она не вернет значение undef, указывающее на то, что больше нет строк для чтения. Курсор, возвращаемый spi_query, автоматически освобождается, когда функция spi_fetchrow возвращает значение undef. Если вы не хотите читать все строки, вместо этого вызовите функцию spi_cursor_close для освобождения курсора. Не выполнение этого действия приведет к утечкам памяти.

spi_prepare(command, argument types)
spi_query_prepared(plan, arguments)
spi_exec_prepared(plan [, attributes], arguments)
spi_freeplan(plan)

spi_prepare, spi_query_prepared, spi_exec_prepared, и spi_freeplan реализуют ту же функциональность, но для подготовленных запросов. spi_prepare принимает строку запроса с номерными заполнителями аргументов ($1, $2 и т.д.) и список строковых типов аргументов: 

$plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2',
                                                     'INTEGER', 'TEXT');

После подготовки плана запроса с помощью вызова spi_prepare, этот план может быть использован вместо строкового запроса, либо в spi_exec_prepared, где результат такой же, как возвращаемый spi_exec_query, либо в spi_query_prepared, который возвращает курсор точно так же, как это делает spi_query, который затем может быть передан в spi_fetchrow. Необязательный второй параметр для spi_exec_prepared - это ссылка на хеш атрибутов; единственный в настоящее время поддерживаемый атрибут - это limit, который устанавливает максимальное количество строк, возвращаемых из запроса. Пропуск limit или его указание как ноль приводит к отсутствию ограничения на количество строк.

Преимущество подготовленных запросов заключается в возможности использовать один подготовленный план для выполнения нескольких запросов. После того, как план больше не нужен, его можно освободить с помощью spi_freeplan: 

CREATE OR REPLACE FUNCTION init() RETURNS VOID AS $$
        $_SHARED{my_plan} = spi_prepare('SELECT (now() + $1)::date AS now',
                                        'INTERVAL');
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
        return spi_exec_prepared(
                $_SHARED{my_plan},
                $_[0]
        )->{rows}->[0]->{now};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$
        spi_freeplan( $_SHARED{my_plan});
        undef $_SHARED{my_plan};
$$ LANGUAGE plperl;

SELECT init();
SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
SELECT done();

  add_time  |  add_time  |  add_time
------------+------------+------------
 2005-12-10 | 2005-12-11 | 2005-12-12

Обратите внимание, что параметр subscript в spi_prepare определяется через $1, $2, $3 и т. д., поэтому избегайте объявления строк запросов в двойных кавычках, что может легко привести к трудноуловимым ошибкам.

Еще один пример иллюстрирует использование необязательного параметра в spi_exec_prepared: 

CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address
                      FROM generate_series(1,3) AS id;

CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$
        $_SHARED{plan} = spi_prepare('SELECT * FROM hosts
                                      WHERE address << $1', 'inet');
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$
        return spi_exec_prepared(
                $_SHARED{plan},
                {limit => 2},
                $_[0]
        )->{rows};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$
        spi_freeplan($_SHARED{plan});
        undef $_SHARED{plan};
$$ LANGUAGE plperl;

SELECT init_hosts_query();
SELECT query_hosts('192.168.1.0/30');
SELECT release_hosts_query();

    query_hosts
-----------------
 (1,192.168.1.1)
 (2,192.168.1.2)
(2 rows)

spi_commit()
spi_rollback()

Совершите или откатите текущую транзакцию. Это можно сделать только в процедуре или анонимном блоке кода (команда DO), вызванном на верхнем уровне. (Обратите внимание, что невозможно выполнить команды SQL COMMIT или ROLLBACK через spi_exec_query или аналогичные. Это должно быть сделано с использованием этих функций). После завершения транзакции автоматически запускается новая транзакция, поэтому нет отдельной функции для этого.

Вот пример: 

CREATE PROCEDURE transaction_test1()
LANGUAGE plperl
AS $$
foreach my $i (0..9) {
    spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
    if ($i % 2 == 0) {
        spi_commit();
    } else {
        spi_rollback();
    }
}
$$;

CALL transaction_test1();

43.3.2. Утилитарные функции в PL/Perl

elog(level, msg)

Выводит сообщение в журнал или сообщение об ошибке. Возможные уровни: DEBUG, LOG, INFO, NOTICE, WARNING и ERROR. Уровень ERROR вызывает ошибочное состояние; если оно не перехватывается окружающим Perl-кодом, ошибка передается вызывающему запросу, что приводит к прерыванию текущей транзакции или подтранзакции. Фактически это то же самое, что и команда Perl die. Остальные уровни генерируют только сообщения разных приоритетов. Отображение сообщений определенного приоритета для клиента, запись в журнал сервера или и то, и другое контролируется переменными конфигурации log_min_messages и client_min_messages. Дополнительную информацию см. в разделе Глава 19.

quote_literal(string)

Вернуть заданную строку в правильном виде в кавычках для использования в качестве строкового литерала в SQL-запросе. Встроенные апострофы и обратные косые черты правильно удваиваются. Обратите внимание, что функция quote_literal возвращает undef при вводе undef; если аргумент может быть undef, часто более подходящей является функция quote_nullable.

quote_nullable(string)

Вернуть заданную строку в виде строки, подходящей для использования в качестве строкового литерала в SQL-запросе; или, если аргумент не определен, вернуть строку "NULL" без кавычек. Встроенные апострофы и обратные косые черты правильно удваиваются.

quote_ident(string)

Вернуть заданную строку в виде идентификатора, подходящего для использования в строке оператора SQL. Кавычки добавляются только при необходимости (т.е. если строка содержит символы, не являющиеся идентификаторами, или если она будет приведена к нижнему регистру). Встроенные кавычки правильно удваиваются.

decode_bytea(string)

Вернуть неэкранированные двоичные данные, представленные содержимым данной строки, которая должна быть закодирована в формате bytea.

encode_bytea(string)

Вернуть закодированную форму двоичных данных содержимого данной строки типа bytea.

encode_array_literal(array)
encode_array_literal(array, delimiter)

Возвращает содержимое ссылочного массива в виде строки в формате литерала массива (см. Раздел 8.15.2). Возвращает неизмененное значение аргумента, если это не ссылка на массив. Разделитель, используемый между элементами литерала массива, по умолчанию равен ", ", если разделитель не указан или не определен.

encode_typed_literal(value, typename)

Преобразует переменную Perl в значение типа данных, переданного вторым аргументом, и возвращает строковое представление этого значения. Правильно обрабатывает вложенные массивы и значения составных типов.

encode_array_constructor(array)

Возвращает содержимое ссылочного массива в виде строки в формате конструктора массива (см. Раздел 4.2.12). Отдельные значения заключены в кавычки с использованием функции quote_nullable. Возвращает значение аргумента, заключенное в кавычки с использованием функции quote_nullable, если это не ссылка на массив.

looks_like_number(string)

Возвращает истинное значение, если содержимое данной строки выглядит как число согласно Perl, в противном случае возвращает ложное значение. Возвращает undef, если аргумент равен undef. Ведущие и завершающие пробелы игнорируются. Inf и Infinity считаются числами.

is_array_ref(argument)

Возвращает истинное значение, если заданный аргумент может быть рассмотрен как ссылка на массив, то есть, если ref аргумента равен ARRAY или PostgreSQL::InServer::ARRAY. В противном случае возвращает ложное значение.

Назад Наверх Далее
43.2. Значения данных в PL/Perl Начало 43.4. Глобальные значения в PL/Perl