Вернуть строки, соответствующие элементам входного массива в функции plpgsql

Question

Я хотел бы создать функцию PostgreSQL, которая делает что-то вроде следующего:

CREATE FUNCTION avg_purchases( IN last_names text[] DEFAULT '{}' )
  RETURNS TABLE(last_name text[], avg_purchase_size double precision)
AS
$BODY$
DECLARE
  qry text;
BEGIN
qry := 'SELECT last_name, AVG(purchase_size) 
          FROM purchases
          WHERE last_name = ANY($1)
          GROUP BY last_name'
RETURN QUERY EXECUTE qry USING last_names;
END;
$BODY$

Но я вижу здесь две проблемы:

1. Мне не ясно, что тип массива является наиболее полезным типом ввода.

2. Это в настоящее время возвращает ноль строк, когда я делаю:

   SELECT avg_purchases($${'Brown','Smith','Jones'}$$);
   

Что мне не хватает?

Answer 1

Это работает:

CREATE OR REPLACE FUNCTION avg_purchases(last_names text[] = '{}')
  RETURNS TABLE(last_name text, avg_purchase_size float8)
AS
$func$
   SELECT last_name, AVG(purchase_size)::float8
   FROM   purchases
   WHERE  last_name = ANY($1)
   GROUP  BY last_name
$func$ LANGUAGE sql;

Вызов:

SELECT * FROM avg_purchases('{foo,Bar,baz,"}weird_name''$$"}');

Или (обновление - пример с долларовыми котировками):

SELECT * FROM avg_purchases($x${foo,Bar,baz,"}weird_name'$$"}$x$);

• Подробнее о том, как цитировать строковые литералы:
  Вставить текст с одинарными кавычками в PostgreSQL

• Вам не нужен динамический SQL здесь.

• Хотя вы можете обернуть его в функцию plpgsql (что может быть полезно), простая функция SQL отлично справляется со своей задачей.

• У вас несоответствия типов.

  • результат avg() может быть numeric провести точный результат. Я бросил в float8 чтобы заставить его работать, который является просто псевдонимом для double precision (вы можете использовать любой). Если вам нужна идеальная точность, используйте numeric вместо.
  • С вами GROUP BY last_name ты хочешь равнину text Параметр OUT вместо text[],

VARIADIC

Массив является полезным типом ввода. Если это проще для вашего клиента, вы также можете использовать VARIADIC входной параметр, который позволяет передать массив в виде списка элементов:

CREATE OR REPLACE FUNCTION avg_purchases(VARIADIC last_names text[] = '{}')
  RETURNS TABLE(last_name text, avg_purchase_size float8)
AS
$func$
   SELECT last_name, AVG(purchase_size)::float8
   FROM   purchases
   JOIN  (SELECT unnest($1)) t(last_name) USING (last_name)
   GROUP  BY last_name
$func$ LANGUAGE sql

Вызов:

SELECT * FROM avg_purchases('foo', 'Bar', 'baz', '"}weird_name''$$"}');

Или (с указанием доллара):

SELECT * FROM avg_purchases('foo', 'Bar', 'baz', $y$'"}weird_name'$$"}$y$);

Имейте в виду, что стандартный Postgres допускает не более 100 элементов. Это определяется во время компиляции с помощью предустановленной опции:

max_function_args (integer)

Сообщает максимальное количество аргументов функции. Это определяется значением FUNC_MAX_ARGS при сборке сервера. Значение по умолчанию составляет 100 аргументов.

Вы все еще можете вызывать его с помощью обозначения массива, когда префикс с ключевым словом VARIADIC:

SELECT * FROM avg_purchases(VARIADIC '{1,2,3, ... 99,100,101}');

Для больших массивов (100+) я бы также использовал unnest() в подзапросе и JOIN к нему, который имеет тенденцию лучше масштабироваться:

• Оптимизация запроса Postgres с большим IN