How Can I Pass Multiple Values to a Single Function Parameter in PostgreSQL?

Use VARIADIC parameters to pass multiple values ​​in a single parameter

Create a function with VARIADIC parameters

In order to pass multiple values ​​to a single parameter, you can use the VARIADIC data type. This allows you to specify an array of values ​​for the parameter. Modify the following function to accept a VARIADIC parameter named job_ids:

<code class="language-sql">CREATE OR REPLACE FUNCTION test_var(VARIADIC job_ids 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(right(jobs.status, -2)) AS status  -- 使用right()函数改进字符串处理
  FROM
    jobs
  INNER JOIN
    company c ON c.id = jobs.id  -- 使用INNER JOIN改进查询效率
  WHERE
    jobs."DeleteFlag" = '0' AND
    c.DeleteFlag = '0' AND
    c.active = '1' AND
    (jobs.id = ANY(job_ids) OR job_ids = '{ -1 }') -- 简化条件判断
  ORDER BY
    jobs.job_title;
END;
$$ LANGUAGE plpgsql;</code>

Call function with VARIADIC parameters

You can now call the test_var function by passing an array of values ​​to the job_ids parameter. For example:

<code class="language-sql">SELECT * FROM test_var('{270, 378}');</code>

This will return job data with IDs 270 and 378.

Function improvements and additional suggestions

In addition to using the VARIADIC parameter, you can also use the right() function in combination with a negative length to trim characters at the beginning of the string, which is faster and simpler than using substring.

Also, make sure that the data type of the DeleteFlag column is boolean and not text. This ensures correct comparison and efficiency.

Finally, the f_test function provided in the answer demonstrates a concise SQL function that avoids the use of PL/pgSQL and solves these problems. It is recommended to choose the appropriate function implementation method based on actual needs.

The above is the detailed content of How Can I Pass Multiple Values to a Single Function Parameter in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!