43.3. Встроенные функции #
43.3.1. Обращение к базе данных из PL/Perl #
Обращаться к самой базе данных из кода Perl можно, используя следующие функции:
-
spi_exec_query
(query
[,предел
]) Функция
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(
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 # здесь мы обращаемся к файлу! 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
,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();
43.3.2. Вспомогательные функции в PL/Perl #
-
elog(
уровень
,сообщение
) Выдаёт служебное сообщение или сообщение об ошибке. Возможные уровни сообщений:
DEBUG
(ОТЛАДКА),LOG
(СООБЩЕНИЕ),INFO
(ИНФОРМАЦИЯ),NOTICE
(ЗАМЕЧАНИЕ),WARNING
(ПРЕДУПРЕЖДЕНИЕ) иERROR
(ОШИБКА). С уровнемERROR
выдаётся ошибка; если она не перехватывается окружающим кодом Perl, она распространяется в вызывающий запрос, что приводит к прерыванию текущей транзакции или подтранзакции. По сути то же самое делает командаdie
языка Perl. При использовании других уровней происходит просто вывод сообщения с заданным уровнем важности. Будут ли сообщения определённого уровня передаваться клиенту и/или записываться в журнал, определяется конфигурационными параметрами log_min_messages и client_min_messages. За дополнительными сведениями обратитесь к Главе 18.-
quote_literal(
строка
) Оформляет переданную строку для использования в качестве текстовой строки в SQL-операторе. Включённые в неё апострофы и обратная косая черта при этом дублируются. Заметьте, что
quote_literal
возвращает undef, когда получает аргумент undef; если такие аргументы возможны, часто лучше использоватьquote_nullable
.-
quote_nullable(
строка
) Оформляет переданную строку для использования в качестве текстовой строки в SQL-операторе; либо, если поступает аргумент undef, возвращает строку «NULL» (без кавычек). Символы апостроф и обратная косая черта дублируются должным образом.
-
quote_ident(
строка
) Оформляет переданную строку для использования в качестве идентификатора в SQL-операторе. При необходимости идентификатор заключается в кавычки (например, если он содержит символы, недопустимые в открытом виде, или буквы в разном регистре). Если переданная строка содержит кавычки, они дублируются.
-
decode_bytea(
строка
) Возвращает неформатированные двоичные данные, представленные содержимым заданной строки, которая должна быть закодирована как
bytea
.-
encode_bytea(
строка
) Возвращает закодированные в виде
bytea
двоичные данные, содержащиеся в переданной строке.-
encode_array_literal(
массив
)
encode_array_literal(
массив
,разделитель
) Возвращает содержимое указанного массива в виде строки в формате массива (см. Подраздел 8.15.2). Возвращает значение аргумента неизменённым, если это не ссылка не массив. Разделитель элементов в строке массива по умолчанию — «
,
» (если разделитель не определён или undef).-
encode_typed_literal(
значение
,имя_типа
) Преобразует переменную Perl в значение типа данных, указанного во втором аргументе, и возвращает строковое представление этого значения. Корректно обрабатывает вложенные массивы и значения составных типов.
-
encode_array_constructor(
массив
) Возвращает содержимое переданного массива в виде строки в формате конструктора массива (см. Подраздел 4.2.12). Отдельные значения заключаются в кавычки функцией
quote_nullable
. Возвращает значение аргумента, заключённое в кавычки функциейquote_nullable
, если аргумент — не ссылка на массив.-
looks_like_number(
строка
) Возвращает значение true, если содержимое переданной строки похоже на число, по правилам Perl, и false в обратном случае. Возвращает undef для аргумента undef. Ведущие и замыкающие пробелы игнорируются. Строки
Inf
иInfinity
считаются представляющими число (бесконечность).-
is_array_ref(
аргумент
) Возвращает значение true, если переданный аргумент можно воспринять как ссылку на массив, то есть это ссылка на
ARRAY
илиPostgreSQL::InServer::ARRAY
. В противном случае возвращает false.