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 и 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, 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 строки)

Фиксация или откат текущей транзакции. Данная операция может быть вызвана только в процедуре или анонимном блоке кода (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 конфигурации переменных. См. Глава 3.4 для получения более подробной информации.

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

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

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

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

Возвращает bytea закодированное представление двоичных данных содержимого заданной строки.

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

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

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

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

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