Передать несколько значений в одном параметре

Question

Я хочу вызвать функцию, передав несколько значений по одному параметру, например так:

SELECT * FROM jobTitle('270,378');

Вот моя функция.

CREATE OR REPLACE FUNCTION test(int)
RETURNS TABLE (job_id int, job_reference int, job_job_title text
                                            , job_status text) AS
$$
BEGIN
RETURN QUERY
select jobs.id,jobs.reference, jobs.job_title,
       ltrim(substring(jobs.status,3,char_length(jobs.status))) as status
FROM jobs ,company c
WHERE jobs."DeleteFlag" = '0'
and c.id= jobs.id and c.DeleteFlag = '0' and c.active = '1' 
and (jobs.id = $1 or -1 = $1)
order by jobs.job_title;
END;
$$ LANGUAGE plpgsql;

Может кто-нибудь помочь с синтаксисом? Или даже предоставить пример кода?

Answer 1

Как @mu уже предоставлено, VARIADIC твой друг. Еще одна важная деталь:

Вы также можете вызвать функцию, используя VARIADIC параметр с типом массива напрямую. Добавить ключевое слово VARIADIC в вызове функции:

SELECT * FROM  f_test(VARIADIC '{1,2,3}'::int[]);

Эквивалентно:

SELECT * FROM  f_test(1,2,3);

Другие советы

• В Postgres 9.1+ right() с отрицательной длиной быстрее и проще вырезать начальные символы из строки:

  right(j.status, -2)
  

  эквивалентно:

  substring(j.status, 3, char_length(jobs.status))
  

• У тебя есть j."DeleteFlag" так же как j.DeleteFlag (без двойных кавычек) в вашем запросе. Это, вероятно, неправильно.

• "DeleteFlag" = '0' указывает на другую проблему. В отличие от других СУБД, Postgres полностью и правильно поддерживает логический тип данных. Если флаг содержит логические данные (Да / Нет / возможно NULL), используйте boolean тип. Тип персонажа, как text было бы неуместно.

• Вам не нужно plpgsql здесь. Вы можете использовать более простую функцию SQL для этого:

Правильная функция

Все это вместе, ваша функция может выглядеть так:

CREATE OR REPLACE FUNCTION f_test(VARIADIC int[])
  RETURNS TABLE (id int, reference int, job_title text, status text) AS
$func$

SELECT j.id, j.reference, j.job_title
      ,ltrim(right(j.status, -2)) AS status
FROM   company c
JOIN   job     j USING (id)
WHERE  c.active
AND    NOT c.delete_flag
AND    NOT j.delete_flag
AND   (j.id = ANY($1) OR '{-1}'::int[] = $1)
ORDER  BY j.job_title

$func$ LANGUAGE sql;

-> Демоверсия SQLfiddle.

Answer 2

Не делайте странных и ужасных вещей, таких как преобразование списка целых чисел в строку CSV, это:

jobTitle('270,378')

это не то, что вы хотите. Вы хотите сказать такие вещи:

jobTitle(270, 378)
jobTitle(array[270, 378])

Если вы собираетесь звонить jobTitle вручную, тогда, вероятно, было бы проще работать с переменной функцией:

create or replace function jobTitle(variadic int[])
returns table (...) as $$
    -- $1 will be an array if integers in here so UNNEST, IN, ANY, ... as needed

Тогда ты можешь jobTitle(6), jobTitle(6, 11), jobTitle(6, 11, 23, 42),... по мере необходимости.

Если вы собираетесь строить jobTitle аргументы в SQL, тогда с явной версией массива, вероятно, будет легче работать:

create or replace function jobTitle(int[])
returns table (...) as $$
    -- $1 will be an array if integers in here so UNNEST, IN, ANY, ... as needed

Тогда вы могли бы jobTitle(array[6]), jobTitle(array[6, 11]),... по мере необходимости, и вы можете использовать все обычные операторы и функции массива для создания списков аргументов для jobTitle,

Я оставлю внутренности функции в качестве упражнения для читателя.