Функция spi_exec_query выполняет команду SQL и возвращает весь набор строк в виде ссылки на массив хешей. Если предел задан и больше нуля, то spi_exec_query получает число строк, не превышающее предел, как если бы запрос включал предложение 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 и 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 # здесь мы обращаемся к файлу!
        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, 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

Заметьте, что параметры для 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)

Эти функции фиксируют или откатывают текущую транзакцию. Они могут вызываться только в процедурах или в анонимных блоках кода (в команде 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();

Выдаёт служебное сообщение или сообщение об ошибке. Возможные уровни сообщений: DEBUG (ОТЛАДКА), LOG (СООБЩЕНИЕ), INFO (ИНФОРМАЦИЯ), NOTICE (ЗАМЕЧАНИЕ), WARNING (ПРЕДУПРЕЖДЕНИЕ) и ERROR (ОШИБКА). С уровнем ERROR выдаётся ошибка; если она не перехватывается окружающим кодом Perl, она распространяется в вызывающий запрос, что приводит к прерыванию текущей транзакции или подтранзакции. По сути то же самое делает команда die языка Perl. При использовании других уровней происходит просто вывод сообщения с заданным уровнем важности. Будут ли сообщения определённого уровня передаваться клиенту и/или записываться в журнал, определяется конфигурационными параметрами log_min_messages и client_min_messages. За дополнительными сведениями обратитесь к Главе 19.

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

Оформляет переданную строку для использования в качестве текстовой строки в SQL-операторе; либо, если поступает аргумент undef, возвращает строку «NULL» (без кавычек). Символы апостроф и обратная косая черта дублируются должным образом.

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

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

Возвращает закодированные в виде bytea двоичные данные, содержащиеся в переданной строке.

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

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

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

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

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