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

PostgreSQL function single parameter multi-value passing

PostgreSQL provides a convenient way to pass multiple values ​​to a function using a single parameter, which can be achieved by using the VARIADIC parameter.

VARIADIC parameter syntax

The syntax of the VARIADIC parameter is as follows:

<code class="language-sql">CREATE OR REPLACE FUNCTION 函数名(VARIADIC 参数数据类型)</code>

Where, VARIADIC indicates that the parameter can accept multiple values. 参数数据类型 Specifies the data type of the value that will be passed.

Multiple value passing example

The following functions accept multiple integer values:

<code class="language-sql">CREATE OR REPLACE FUNCTION test(VARIADIC int[])
RETURNS TABLE (job_id int, job_reference int, job_job_title text, job_status text) AS
$$
BEGIN
  RETURN QUERY
  SELECT *
  FROM jobs
  WHERE job_id = ANY()
  ORDER BY job_job_title;
END;
$$ LANGUAGE plpgsql;</code>

This function can be called by passing multiple integer values ​​separated by commas:

<code class="language-sql">SELECT * FROM test(270, 378);</code>

Alternative syntax for using arrays

In PostgreSQL 9.1 or later, you can also call VARIADIC parameter functions directly using array types. The following code is equivalent to the previous example:

<code class="language-sql">SELECT * FROM test(VARIADIC '{1, 2, 3}'::int[]);</code>

Other performance optimization suggestions

• Use the negative argument of the right() function to remove characters at the beginning of the string, which is faster than using the substring() function.
• Avoid using double quotes around column names, such as "DeleteFlag".
• Use a suitable boolean data type to represent the flag, rather than a character data type, such as text.

Optimized function

Applying the above optimizations, here is an improved version of the original function:

<code class="language-sql">CREATE OR REPLACE FUNCTION f_test(VARIADIC int[])
RETURNS TABLE (id int, reference int, job_title text, status text) AS
$func$
SELECT id, reference, job_title, ltrim(right(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 id = ANY()
ORDER BY job_title;
$func$ LANGUAGE sql;</code>

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