sql - Pass multiple values in single parameter

Question

I want to call a function by passing multiple values on single parameter, like this:

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

Here is my function.

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;

Can someone help with the syntax? Or even provide sample code?

Answer 1

VARIADIC

Like @mu provided, VARIADIC is your friend. One more important detail:

You can also call a function using a VARIADIC parameter with an array type directly. Add the key word VARIADIC in the function call:

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

is equivalent to:

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

Other advice

In Postgres 9.1 or later right() with a negative length is faster and simpler to trim leading characters from a string:

right(j.status, -2)

is equivalent to:

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

You have j."DeleteFlag" as well as j.DeleteFlag (without double quotes) in your query. This is probably incorrect. See:

• PostgreSQL Error: Relation already exists

"DeleteFlag" = '0' indicates another problem. Unlike other RDBMS, Postgres properly supports the boolean data type. If the flag holds boolean data (true / false / NULL) use the boolean type. A character type like text would be inappropriate / inefficient.

Proper function

You don't need PL/pgSQL here. You can use a simpler SQL function:

CREATE OR REPLACE FUNCTION f_test(VARIADIC int[])
  RETURNS TABLE (id int, reference int, job_title text, status text)
  LANGUAGE sql 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$;

db<>fiddle here
_{Old sqlfiddle}