CODEWITHSUNDEEP
sqlpostgresqlstored-proceduresparameter-passingplpgsql
Naveen
Naveen

Reputation: 159

Pass multiple values in single parameter

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?

Upvotes: 2

Views: 6396

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656271

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:

"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

Upvotes: 7

mu is too short
mu is too short

Reputation: 434585

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.

Upvotes: 5

Related Questions