Pass multiple values in single parameter
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
Don't do strange and horrible things like converting a list of integers to a CSV string, this:
jobTitle('270,378')
is not what you want. You want to say things like this:
jobTitle(270, 378)
jobTitle(array[270, 378])
If you're going to be calling jobTitle
by hand then a variadic function would probably be easiest to work with:
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
Then you can jobTitle(6)
, jobTitle(6, 11)
, jobTitle(6, 11, 23, 42)
, ... as needed.
If you're going to be building the jobTitle
arguments in SQL then the explicit-array version would probably be easier to work with:
create or replace function jobTitle(int[])
returns table (...) as $$
-- $1 will be an array if integers in here so UNNEST, IN, ANY, ... as needed
Then you could jobTitle(array[6])
, jobTitle(array[6, 11])
, ... as needed and you could use all the usual array operators and functions to build argument lists for jobTitle
.
I'll leave the function's internals as an exercise for the reader.