Документация по PostgreSQL 9.4.1 | |||
---|---|---|---|
Пред. | Уровень выше | Глава 31. libpq — библиотека для языка C | След. |
31.9. Функции, связанные с командой COPY
Команда COPY в PostgreSQL имеет возможность читать и записывать данные через сетевое подключение, установленное libpq. Описанные в этом разделе функции позволяют приложениям воспользоваться этой возможностью для передачи или приёма копируемых данных.
Общая процедура такова: сначала приложение выдаёт SQL-команду COPY, вызывая PQexec
или одну из подобных функций. В ответ оно должно получить (если не возникла ошибка) объект PGresult с кодом состояния PGRES_COPY_OUT или PGRES_COPY_IN (в зависимости от направления копирования). Затем приложение должно использовать функции, описанные в этом разделе, и принимать или передавать строки данных. По завершении передачи возвращается ещё один объект PGresult, сообщающий о состоянии завершения передачи. В случае успеха он содержит код состояния PGRES_COMMAND_OK, а если возникает какая-то проблема — PGRES_FATAL_ERROR. После этого можно продолжать выполнять SQL-команды через PQexec
. (Пока операция COPY не завершена, выполнять другие SQL-команды через то же подключение нельзя.)
Если команда COPY была выполнена через PQexec
в строке, содержащей дополнительные команды, приложение должно продолжить получать результаты через PQgetResult
после завершения последовательности COPY. Только когда PQgetResult
возвращает NULL, можно с уверенностью считать, что командная строка PQexec
выполнена полностью, и безопасно передавать другие команды.
Функции, описанные в этом разделе, должны выполняться только после получения кода состояния PGRES_COPY_OUT или PGRES_COPY_IN от функции PQexec
или PQgetResult
.
Объект PGresult с таким кодом состояния содержит дополнительные данные о начавшейся операции COPY. Эти данные можно получить функциями, также применяющимися при обработке результатов запроса:
PQnfields
Возвращает число копируемых колонок (полей).
PQbinaryTuples
Значение 0 указывает, что для всей операции копирования применяется текстовый формат (строки разделяются символами новой строки, колонки разделяются символами-разделителями и т. д.). Значение 1 указывает, что для всей операции копирования применяется двоичный формат. За дополнительными сведениями обратитесь к COPY.
PQfformat
Возвращает код формата (0 — текстовый, 1 — двоичный), связанный с каждой копируемой колонкой. Коды форматов колонок всегда будут нулевыми, если общий формат копирования — текстовый, но с двоичным форматом поддерживаются и текстовые, и двоичные колонки. (Однако в текущей реализации COPY при двоичном копировании колонки могут быть только двоичными, так что форматы колонок должны всегда соответствовать общему формату.)
Замечание: Эти дополнительные значения данных доступны только при использовании протокола 3.0. С протоколом 2.0 все эти функции возвращают 0.
31.9.1. Функции для передачи данных COPY
Эти функции применяются для передачи данных при операции COPY FROM STDIN. Они не будут работать, если подключение находится не в состоянии COPY_IN.
PQputCopyData
Отправляет данные на сервер, когда активно состояние COPY_IN.
int PQputCopyData(PGconn *conn, const char *buffer, int nbytes);
Передаёт серверу данные COPY из указанного буфера (buffer), длиной nbytes байт. Она возвращает 1, если данные были переданы, 0, если они не передавались во избежание блокировки (это возможно, только если подключение находится в неблокирующем режиме), или -1, если произошла ошибка. (Если возвращено -1, подробности ошибки можно узнать, вызвав
PQerrorMessage
. Если получен 0, дождитесь состояния готовности к записи и повторите попытку.)Приложение может разделять поток данных COPY на буферизуемые блоки любого удобного размера. Границы буфера не имеют семантического значения при передаче. Содержимое потока данных должно соответствовать формату данных, ожидаемому командой COPY; за подробностями обратитесь к COPY.
PQputCopyEnd
Отправляет признак конца данных на сервер, когда активно состояние COPY_IN.
int PQputCopyEnd(PGconn *conn, const char *errormsg);
Завершает операцию COPY_IN с успешным результатом, если в errormsg передаётся NULL. Если errormsg не NULL, команда COPY будет завершена с ошибкой, а сообщением об ошибке будет строка, переданная в errormsg. (Однако не следует полагать, что именно это сообщение будет получено от сервера назад, так как сервер мог уже прервать операцию COPY по своим причинам. Также заметьте, что принудительный вызов ошибки не работает с соединениями по протоколу версии до 3.0.)
Эта функция возвращает 1, если признак завершения был передан, 0, если он не передавался во избежание блокировки (это возможно, только если соединение находится в неблокирующем режиме), или -1, если произошла ошибка. (Если возвращено -1, подробности ошибки можно узнать, вызвав
PQerrorMessage
. Если получен 0, дождитесь состояния готовности к записи и повторите попытку.)После успешного вызова
PQputCopyEnd
вызовитеPQgetResult
, чтобы узнать окончательный результат команды COPY. Ожидать появления этого результата можно обычным образом. Затем вернитесь к обычным операциям.
31.9.2. Функции для приёма данных COPY
Эти функции применяются для получения данных при операции COPY TO STDOUT. Они не будут работать, если подключение находится не в состоянии COPY_OUT.
PQgetCopyData
Принимает данные от сервера, когда активно состояние COPY_OUT.
int PQgetCopyData(PGconn *conn, char **buffer, int async);
Запрашивает следующую строку данных с сервера в процессе операции COPY. Данные всегда возвращаются строка за строкой; если поступила только часть строки, она не возвращается. Успешное получение строки данных подразумевает выделение блока памяти для этих данных. В параметре buffer ей передаётся указатель, отличный от NULL. По адресу *buffer записывается указатель на выделенную память, либо NULL, когда буфер не возвращается. Если буфер результата отличен от NULL, его следует освободить, когда он станет не нужен, вызвав
PQfreemem
.Когда строка получена успешно, возвращается число байт данных в этой строке (это число всегда больше нуля). Возвращаемое строковое значение всегда завершается нулём, хотя это полезно, вероятно, только для текстовой COPY. Нулевой результат означает, что операция COPY продолжает выполняться, но строка ещё не готова (это возможно, только когда параметр async равен true). Возвращённое значение -1 означает, что команда COPY завершена, а -2 показывает, что произошла ошибка (её причину можно узнать с помощью
PQerrorMessage
).Когда параметр async равен true (отличен от нуля), функция
PQgetCopyData
не будет блокироваться, ожидая данных; она возвратит ноль, если выполнение COPY продолжается, но полная строка ещё не получена. (В этом случае нужно дождаться готовности к чтению и затем вызватьPQconsumeInput
, прежде чем вызыватьPQgetCopyData
ещё раз.) Когда async равен false (нулю),PQgetCopyData
будет заблокирована до поступления данных или окончания операции.Когда
PQgetCopyData
возвращает -1, вызовитеPQgetResult
, чтобы узнать окончательный результат команды COPY. Ожидать появления этого результата можно обычным образом. Затем вернитесь к обычным операциям.
31.9.3. Устаревшие функции для COPY
Эти функции представляют старые методы выполнения операции COPY. Хотя они продолжают работать, они признаны устаревшими из-за плохой обработки ошибок, неудобных способов обнаружения конца данных и отсутствия поддержки двоичных или неблокирующих передач.
PQgetline
Читает передаваемую сервером строку символов, завершающуюся символом новой строки, в буфер (buffer) размера length.
int PQgetline(PGconn *conn, char *buffer, int length);
Эта функция копирует length-1 символов в буфер и преобразует символ конца строки в нулевой байт.
PQgetline
возвращает EOF в конце ввода, 0, если была прочитана вся строка, и 1, если буфер заполнен, но завершающий символ конца строки ещё не прочитан.Заметьте, что приложение должно проверить, не состоит ли новая строка в точности из двух символов \., что будет означать, что сервер завершил передачу результатов команды COPY. Если приложение может принимать строки длиннее length-1 символов, необходимо позаботиться о том, чтобы оно корректно распознавало строку \. (а не воспринимало, например, конец длинной строки данных как завершающую строку).
PQgetlineAsync
Читает передаваемую сервером строку данных COPY в буфер без блокировки.
int PQgetlineAsync(PGconn *conn, char *buffer, int bufsize);
Эта функция похожа на
PQgetline
, но может применяться в приложениях, которые должны читать данные COPY асинхронно, то есть, без блокировки. Запустив команду COPY и получив ответ PGRES_COPY_OUT, приложение должно вызыватьPQconsumeInput
иPQgetlineAsync
, пока не будет получен сигнал конца данных.В отличие от
PQgetline
, эта функция сама отвечает за обнаружение конца данных.При каждом вызове
PQgetlineAsync
будет возвращать данные, если во входном буфере libpq оказывается полная строка данных. В противном случае никакие данные не возвращаются до поступления остального содержимого строки. Эта функция возвращает -1, если обнаруживается признак завершения копирования, или 0, если данные не получены, или положительное количество возвращённых байт данных. Если возвращается -1, вызывающий код должен затем вызватьPQendcopy
и после этого перейти в обычный режим работы.Возвращаемые данные не будут пересекать границы строк данных. При этом может быть возвращена одна строка целиком. Но если буфер, выделенный вызывающим кодом, оказывается слишком мал для строки, передаваемой сервером, возвращена будет часть строки. Когда передаются текстовые данные, это можно выявить, проверив, содержит ли последний возвращаемый байт символ \n. (Для COPY в двоичном формате потребуется собственно разобрать формат данных COPY, чтобы выявить подобную ситуацию.) Возвращаемая строка не завершается нулём. (Если вы хотите получить строку с нулём в конце, передайте в bufsize число на единицу меньше фактического размера блока.)
PQputline
Передаёт серверу строку, завершённую нулём. Возвращает 0 в случае успеха, либо EOF, если передать строку не удаётся.
int PQputline(PGconn *conn, const char *string);
Поток данных COPY, передаваемых последовательностью вызовов
PQputline
, имеет тот же формат, что возвращаетPQgetlineAsync
, за исключением того, что приложения не обязательно должны передавать по одной строке данных за вызовPQputline
; они могут посылать части строк или сразу несколько строк.Замечание: До версии 3.0 протокола PostgreSQL приложение должно было явно отправлять два символа \. последней строкой, чтобы сообщить серверу, что оно закончило передачу данных COPY. Хотя это по-прежнему работает, такое поведение считается устаревшим и ожидается, что особое значение \. будет исключено в будущих версиях. Передав собственно данные, сейчас достаточно вызвать
PQendcopy
.PQputnbytes
Передаёт серверу строку, не завершённую нулём. Возвращает 0 в случае успеха, либо EOF, если передать строку не удаётся.
int PQputnbytes(PGconn *conn, const char *buffer, int nbytes);
Поведение этой функции не отличается от
PQputline
, но её буфер данных не должен содержать завершающий ноль, так как для неё число передаваемых байт задаётся непосредственно. Используйте эту функцию для передачи двоичных данных.PQendcopy
Производит синхронизацию с сервером.
int PQendcopy(PGconn *conn);
Эта функция ожидает завершения копирования сервером. Её следует вызывать, либо когда серверу была передана последняя строка функцией
PQputline
, либо когда от сервера была получена последняя строка функциейPGgetline
. Если её не вызвать, сервер "потеряет синхронизацию" с клиентом. После завершения этой функции сервер готов принимать следующую команду SQL. В случае успешного завершения возвращается 0, в противном случае — ненулевое значение. (Чтобы получить подробности ошибки при ненулевом значении, вызовитеPQerrorMessage
.)Вызывая
PQgetResult
, приложение должно обрабатывать результат PGRES_COPY_OUT, в цикле выполняяPQgetline
, а обнаружив завершающую строку, вызватьPQendcopy
. Затем оно должно вернуться к циклуPQgetResult
, и выйти из него, когдаPQgetResult
возвратит нулевой указатель. Подобным образом, получив результат PGRES_COPY_IN, приложение должно выполнить серию вызововPQputline
, завершить её, вызвавPQendcopy
, а затем вернуться к циклуPQgetResult
. При такой организации обработки команда COPY будет корректно выполняться и в составе последовательности команд SQL.Старые приложения обычно передают команду COPY через
PQexec
и рассчитывают, что транзакция будет завершена послеPQendcopy
. Это будет работать, только если команда COPY является единственной SQL-командой в строке команд.
Пред. | Начало | След. |
Асинхронное уведомление | Уровень выше | Функции управления |
45.3. Built-in Functions
45.3.1. Database Access from PL/Perl
Access to the database itself from your Perl function can be done via the following functions:
-
spi_exec_query
(query
[,limit
]) spi_exec_query
executes an SQL command and returns the entire row set as a reference to an array of hash references. Iflimit
is specified and is greater than zero, thenspi_exec_query
retrieves at mostlimit
rows, much as if the query included aLIMIT
clause. Omittinglimit
or specifying it as zero results in no row limit.You should only use this command when you know that the result set will be relatively small. Here is an example of a query (
SELECT
command) with the optional maximum number of rows:$rv = spi_exec_query('SELECT * FROM my_table', 5);
This returns up to 5 rows from the table
my_table
. Ifmy_table
has a columnmy_column
, you can get that value from row$i
of the result like this:$foo = $rv->{rows}[$i]->{my_column};
The total number of rows returned from a
SELECT
query can be accessed like this:$nrows = $rv->{processed}
Here is an example using a different command type:
$query = "INSERT INTO my_table VALUES (1, 'test')"; $rv = spi_exec_query($query);
You can then access the command status (e.g.,
SPI_OK_INSERT
) like this:$res = $rv->{status};
To get the number of rows affected, do:
$nrows = $rv->{processed};
Here is a complete example:
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
andspi_fetchrow
work together as a pair for row sets which might be large, or for cases where you wish to return rows as they arrive.spi_fetchrow
works only withspi_query
. The following example illustrates how you use them together: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);
Normally,
spi_fetchrow
should be repeated until it returnsundef
, indicating that there are no more rows to read. The cursor returned byspi_query
is automatically freed whenspi_fetchrow
returnsundef
. If you do not wish to read all the rows, instead callspi_cursor_close
to free the cursor. Failure to do so will result in memory leaks.-
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
, andspi_freeplan
implement the same functionality but for prepared queries.spi_prepare
accepts a query string with numbered argument placeholders ($1, $2, etc.) and a string list of argument types:$plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2', 'INTEGER', 'TEXT');
Once a query plan is prepared by a call to
spi_prepare
, the plan can be used instead of the string query, either inspi_exec_prepared
, where the result is the same as returned byspi_exec_query
, or inspi_query_prepared
which returns a cursor exactly asspi_query
does, which can be later passed tospi_fetchrow
. The optional second parameter tospi_exec_prepared
is a hash reference of attributes; the only attribute currently supported islimit
, which sets the maximum number of rows returned from the query. Omittinglimit
or specifying it as zero results in no row limit.The advantage of prepared queries is that is it possible to use one prepared plan for more than one query execution. After the plan is not needed anymore, it can be freed with
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
Note that the parameter subscript in
spi_prepare
is defined via $1, $2, $3, etc., so avoid declaring query strings in double quotes that might easily lead to hard-to-catch bugs.Another example illustrates usage of an optional parameter in
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()
Commit or roll back the current transaction. This can only be called in a procedure or anonymous code block (
DO
command) called from the top level. (Note that it is not possible to run the SQL commandsCOMMIT
orROLLBACK
viaspi_exec_query
or similar. It has to be done using these functions.) After a transaction is ended, a new transaction is automatically started, so there is no separate function for that.Here is an example:
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();
45.3.2. Utility Functions in PL/Perl
-
elog(
level
,msg
) Emit a log or error message. Possible levels are
DEBUG
,LOG
,INFO
,NOTICE
,WARNING
, andERROR
.ERROR
raises an error condition; if this is not trapped by the surrounding Perl code, the error propagates out to the calling query, causing the current transaction or subtransaction to be aborted. This is effectively the same as the Perldie
command. The other levels only generate messages of different priority levels. Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 20 for more information.-
quote_literal(
string
) Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that
quote_literal
returns undef on undef input; if the argument might be undef,quote_nullable
is often more suitable.-
quote_nullable(
string
) Return the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is undef, return the unquoted string "NULL". Embedded single-quotes and backslashes are properly doubled.
-
quote_ident(
string
) Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled.
-
decode_bytea(
string
) Return the unescaped binary data represented by the contents of the given string, which should be
bytea
encoded.-
encode_bytea(
string
) Return the
bytea
encoded form of the binary data contents of the given string.-
encode_array_literal(
array
)
encode_array_literal(
array
,delimiter
) Returns the contents of the referenced array as a string in array literal format (see Section 8.15.2). Returns the argument value unaltered if it's not a reference to an array. The delimiter used between elements of the array literal defaults to "
,
" if a delimiter is not specified or is undef.-
encode_typed_literal(
value
,typename
) Converts a Perl variable to the value of the data type passed as a second argument and returns a string representation of this value. Correctly handles nested arrays and values of composite types.
-
encode_array_constructor(
array
) Returns the contents of the referenced array as a string in array constructor format (see Section 4.2.12). Individual values are quoted using
quote_nullable
. Returns the argument value, quoted usingquote_nullable
, if it's not a reference to an array.-
looks_like_number(
string
) Returns a true value if the content of the given string looks like a number, according to Perl, returns false otherwise. Returns undef if the argument is undef. Leading and trailing space is ignored.
Inf
andInfinity
are regarded as numbers.-
is_array_ref(
argument
) Returns a true value if the given argument may be treated as an array reference, that is, if ref of the argument is
ARRAY
orPostgreSQL::InServer::ARRAY
. Returns false otherwise.