Необязательный аргумент в функции PL/pgSQL

Question

Я пытаюсь написать функцию PL/pgSQL с необязательными аргументами. Он выполняет запрос на основе отфильтрованного набора записей (если указан), в противном случае выполняет запрос ко всему набору данных в таблице.

Например (КОД ПСЕВДО):

CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids=[]) RETURNS SETOF RECORD AS $$
    IF len(optional_list_of_ids) > 0 THEN
        RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2 AND id in optional_list_of_ids);
    ELSE
        RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2);
    ENDIF
$$ LANGUAGE SQL;

Каков будет правильный способ реализации этой функции?

Кроме того, я хотел бы знать, как я мог бы вызвать такую ​​функцию в другой внешней функции. Вот как бы я это сделал - это правильно или есть лучший способ?

CREATE FUNCTION foofuncwrapper(param1 integer, param2 date, param2 date) RETURNS SETOF RECORD AS $$
BEGIN
   CREATE TABLE ids AS SELECT id from foobar where id < 100;
   RETURN QUERY (SELECT * FROM foofunc(param1, param2, ids));
END
$$ LANGUAGE SQL

Answer 1

Начиная с PostgreSQL 8.4 (который, кажется, выполняется), для параметров функции существуют значения по умолчанию. Если вы указали свой параметр последним и указали значение по умолчанию, вы можете просто опустить его в вызове:

CREATE OR REPLACE FUNCTION foofunc(_param1 integer
                                 , _param2 date
                                 , _ids    int[] DEFAULT '{}')
  RETURNS SETOF foobar AS -- declare return type!
$func$
BEGIN  -- required for plpgsql
   IF _ids <> '{}'::int[] THEN -- exclude empty array and NULL
      RETURN QUERY
      SELECT *
      FROM   foobar
      WHERE  f1 = _param1
      AND    f2 = _param2
      AND    id = ANY(_ids); -- "IN" is not proper syntax for arrays
   ELSE
      RETURN QUERY
      SELECT *
      FROM   foobar
      WHERE  f1 = _param1
      AND    f2 = _param2;
   END IF;
END  -- required for plpgsql
$func$  LANGUAGE plpgsql;

Основные моменты:

• Ключевое слово DEFAULT используется для объявления параметров по умолчанию. Короткая альтернатива: =,

• Я удалил лишнее param1 из грязного примера.

• Так как вы вернетесь SELECT * FROM foobar, объявите тип возвращаемого значения как RETURNS SETOF foobar вместо RETURNS SETOF record, Последняя форма с анонимными записями очень громоздкая, вам придется предоставлять список определений столбцов при каждом вызове.

• Я использую массив целых чисел (int[]) в качестве параметра функции. Адаптировал IF выражение и WHERE пункт соответственно.

• IF операторы не доступны в простом SQL. Должен быть LANGUAGE plpgsql для этого.

Звоните с или без _ids:

SELECT * FROM foofunc(1, '2012-1-1'::date);

Эффективно то же самое:

SELECT * FROM foofunc(1, '2012-1-1'::date, '{}'::int[]);

Вы должны убедиться, что звонок однозначен. Если у вас есть другая функция с тем же именем и двумя параметрами, Postgres может не знать, какую из них выбрать. Явное приведение (как я демонстрирую) сужает его. Кроме того, нетипизированные строковые литералы тоже работают, но явное выражение никогда не повредит.

Вызов из другой функции:

CREATE FUNCTION foofuncwrapper(_param1 integer, _param2 date)
  RETURNS SETOF foobar AS
$func$
DECLARE
   _ids int[] := '{1,2,3}';
BEGIN
   -- irrelevant stuff

   RETURN QUERY
   SELECT * FROM foofunc(_param1, _param2, _ids);
END
$func$  LANGUAGE plgpsql;

Answer 2

Разрабатывая ответ Frank Heikens на эту тему:

VARIADIC аргумент не должен быть единственным аргументом, только последним.

Ты можешь использовать VARIADIC для функций, которые могут принимать нулевые переменные аргументы, это просто немного сложнее, так как требует нулевого стиля вызова для нулевых аргументов. Вы можете предоставить функцию-обертку, чтобы скрыть безобразие. Учитывая начальное определение функции varardic, например:

CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids VARIADIC integer[]) RETURNS SETOF RECORD AS $$
....
$$ language sql;

Для нулевых аргументов используйте оболочку, например:

CREATE OR REPLACE FUNCTION foofunc(integer, date, date) RETURNS SETOF RECORD AS $body$
SELECT foofunc($1,$2,$3,VARIADIC ARRAY[]::integer[]);
$body$ LANGUAGE 'sql';

или просто вызовите основной функционал с пустым массивом, как VARIADIC '{}'::integer[] непосредственно. Обертка уродлива, но в ней есть уродство, поэтому я бы порекомендовал использовать обертку.

Прямые звонки могут быть сделаны в вариативной форме:

SELECT foofunc(1,'2011-01-01','2011-01-01', 1, 2, 3, 4);

... или форма вызова массива с массивом ctor:

SELECT foofunc(1,'2011-01-01','2011-01-01', VARIADIC ARRAY[1,2,3,4]);

... или массив текстовой литеральной формы:

SELECT foofunc(1,'2011-01-01','2011-01-01', VARIADIC '{1,2,3,4}'::int[]);

Последние две формы работают с пустыми массивами.

Answer 3

Вы имеете в виду функции SQL с переменным числом аргументов? Если это так, используйте VARIADIC.