Встроенные функции
Эта страница переведена при помощи нейросети GigaChat.
Доступ к базе данных из 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
Обратите внимание, что индекс параметра в 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();
Вспомогательные функции в PL/Perl
elog(level, msg)
Вывод сообщения журнала или ошибки. Возможные уровни: DEBUG, LOG, INFO, NOTICE, WARNING, и ERROR. ERROR вызывает условие ошибки. Если она не перехватывается окружающим кодом Perl, ошибка распространяется наружу к вызывающему запросу, вызывая прерывание текущей транзакции или подоперации. По сути это то же самое, что и команда Perl die. Другие уровни генерируют только сообщения различных уровней приоритета. Контроль за тем, сообщаются ли сообщения определенного приоритета клиенту, записываются в журнал сервера или и то, и другое, осуществляется с помощью переменных конфигурации log_min_messages и client_min_messages. См. раздел «Настройка сервера» для получения дополнительной информации.
quote_literal(string)
Оформляет переданную строку для использования в качестве текстовой строки в SQL-операторе. Встроенные одинарные кавычки и обратные слэши правильно удваиваются. Обратите внимание, что quote_literal возвращает undef, когда получает аргумент undef. Если такие аргументы возможны, часто лучше использовать quote_nullable.
quote_nullable(string)
Оформляет переданную строку для использования в качестве текстовой строки в SQL-операторе, или, если поступает аргумент undef, возвращает строку NULL. Встроенные одинарные кавычки и обратные слэши дублируются должным образом.
quote_ident(string)
Оформляет переданную строку для использования в качестве идентификатора в SQL-операторе. Кавычки добавляются только при необходимости (т.е. если строка содержит символы, недопустимые в открытом виде, или буквы в разном регистре). Встроенные кавычки дублируются должным образом.
decode_bytea(string)
Возвращает нескорректированные двоичные данные, представленные содержимым данной строки, которая должна быть закодирована с помощью bytea.
encode_bytea(string)
Возвращает форму, закодированную с использованием bytea, для двоичных данных содержимого заданной строки.
encode_array_literal(array)encode_array_literal(array, delimiter)
Возвращает содержимое массива, на который ссылается ссылка, в виде строки в формате литерала массива. Возвращает значение аргумента без изменений, если это не ссылка на массив. Разделитель, используемый между элементами массива в литеральном формате, по умолчанию «, » (если разделитель не указан или undef).
encode_typed_literal(value, typename)
Преобразует переменную Perl в значение типа данных, переданное во втором аргументе, и возвращает строку представления этого значения. Правильно обрабатывает вложенные массивы и значения составных типов.
encode_array_constructor(array)
Возвращает содержимое массива, на который ссылается ссылка, в виде строки в формате конструктора массива. Отдельные значения заключаются в кавычки с использованием quote_nullable. Возвращает значение аргумента, заключенного в кавычки с использованием quote_nullable, если это не ссылка на массив.
looks_like_number(string)
Возвращает значение true, если содержимое данной строки выглядит как число согласно Perl, возвращает ложь в противном случае. Возвращает undef, если аргумент не определен. Ведущие и конечные пробелы игнорируются. Inf и Infinity рассматриваются как числа.
is_array_ref(argument)
Возвращает значение true, если данный аргумент может рассматриваться как ссылка на массив, то есть, если ref аргумента равен ARRAY или PostgreSQL::InServer::ARRAY. В противном случае возвращается false.