Статьи

Передача массивов в функцию PostgreSQL PL / pgSQL

Январь 20, 2019

Может быть удобно передать набор строк в хранимую функцию PL / pgSQL через массив PostgreSQL . Как правило, это очень легко сделать, но в этом посте демонстрируется пара нюансов, о которых следует знать при передаче массива в функцию PL / pgSQL из JDBC или psql .

Следующий листинг кода для искусственной хранимой функции PL / pgSQL, которая будет использоваться в этом посте. Эта функция принимает массив текстовых переменных, проходит по ним в зависимости от длины массива и сообщает об этих строках с помощью оператора PL / pgSQL RAISE .

printStrings.sql

01
02
03
04
05
06
07
08
09
10
11
CREATE OR REPLACE FUNCTION printStrings(strings text[]) RETURNS void AS $printStrings$
DECLARE
   number_strings integer := array_length(strings, 1);
   string_index integer := 1;
BEGIN
   WHILE string_index <= number_strings LOOP
      RAISE NOTICE '%', strings[string_index];
      string_index = string_index + 1;
   END LOOP;
END;
$printStrings$ LANGUAGE plpgsql;

Приведенный выше код PL / pgSQL в файле printStrings.sql может быть выполнен в psql с \ir как показано на следующем снимке экрана.

creatingStoredFunctionPrintStrings

Синтаксис вызова хранимой функции PL / pgSQL с массивом в качестве аргумента описан в разделе « Ввод значения массива » в документации по массивам PostgreSQL . В этой документации объясняется, что «общим форматом константы массива» является '{ val1 delim val2 delim ... }' где delim в большинстве случаев является разделителем запятой ( , ). В той же документации приведен пример: '{{1,2,3},{4,5,6},{7,8,9}}' . В этом примере представлены три массива целых чисел с тремя целыми числами в каждом массиве.

Только что показанный синтаксис литерала массива прост для использования с числовыми типами, такими как целые числа в показанном примере. Однако для строк необходимо экранировать кавычки вокруг строк, поскольку вокруг всего массива уже есть кавычки ( '{}' ). Это экранирование выполняется путем окружения каждой строки в массиве двумя одинарными кавычками на каждой стороне. Например, чтобы вызвать сохраненную функцию, только что показанную в трех строках «Inspired», «Actual» и «Events», в psql можно использовать следующий синтаксис: SELECT printstrings('{''Inspired'', ''Actual'', ''Events''}'); как показано на следующем снимке экрана.

printStringsStoredFunctionPrintsThreeStrings

Массивы также могут быть переданы в функции PL / pgSQL из кода Java. Это обеспечивает простой подход для передачи коллекций Java в функции PL / pgSQL. Следующий фрагмент кода Java демонстрирует, как вызвать хранимую функцию, показанную ранее с JDBC. Поскольку эта хранимая функция возвращает void (это больше похоже на хранимую процедуру ), коду JDBC не нужно вызывать какие- либо переопределенные методы registerOutParameter () CallableStatement .

JDBC Code вызывает хранимую функцию с массивом Java

1
2
3
4
5
6
7
final CallableStatement callable =
   connection.prepareCall("{ call printstrings ( ? ) }");
final String[] strings = {"Inspired", "Actual", "Events"};
final Array stringsArray = connection.createArrayOf("varchar", strings);
callable.setArray(1, stringsArray);
callable.execute();
callable.close();

Приложения Java часто работают больше с коллекциями Java, чем с массивами, но, к счастью, Collection предоставляет toArray (T []) для простого получения представления массива коллекции. Например, следующий листинг кода адаптирован из предыдущего листинга, но работает с ArrayList, а не с массивом.

Вызов хранимой функции JDBC с помощью Java Collection

01
02
03
04
05
06
07
08
09
10
11
12
13
final CallableStatement callable =
   connection.prepareCall("{ call printstrings ( ? ) }");
final ArrayList<String> strings = new ArrayList<>();
strings.add("Inspired");
strings.add("Actual");
strings.add("Events");
final Array stringsArray =
   connection.createArrayOf(
      "varchar",
      strings.toArray(new String[strings.size()]));
callable.setArray(1, stringsArray);
callable.execute();
callable.close();

Вывод

Возможность передачи массива в качестве параметра хранимой функции PostgreSQL PL / pgSQL является простым процессом. Этот пост специально продемонстрировал передачу массива строк (включая правильное экранирование) в хранимую функцию PL / pgSQL из psql и передачу массива строк в хранимую функцию PL / pgSQL из JDBC с использованием java.sql.Array и Connection.createArrayOf (String Объект []) .

Ссылка: Передача массивов в функцию PostgreSQL PL / pgSQL от нашего партнера по JCG Дастина Маркса из блога Inspired by Actual Events .
446
Share: