How to Pass Multiple Values to a PostgreSQL Function Using a Single Parameter?

Passing Multiple Values via a Single Parameter in PostgreSQL Functions

This guide demonstrates how to efficiently pass multiple values to a PostgreSQL function using a single parameter, focusing on the VARIADIC type and offering optimization strategies.

Leveraging the VARIADIC Parameter

PostgreSQL's VARIADIC type offers a concise solution for handling multiple input values within a single parameter. Let's adapt a sample function:

<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 j.id, j.reference, j.job_title,
       ltrim(right(j.status, -2)) AS status  -- Optimized string trimming
FROM jobs j
JOIN company c ON j.id = c.id
WHERE c.active
AND NOT c.delete_flag
AND NOT j.delete_flag
AND (j.id = ANY() OR '{<b>-1</b>}'::int[] = )
ORDER BY j.job_title;
END;
$$ LANGUAGE plpgsql;</code>

This revised function accepts an integer array (int[]) as input. The VARIADIC keyword enables the function to accept a variable number of integer arguments, automatically converting them into an array.

Function Invocation with VARIADIC

The function can be invoked directly using an array:

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

This is functionally equivalent to:

<code class="language-sql">SELECT * FROM test(1, 2, 3);</code>

Performance Enhancements and Best Practices

Efficient String Trimming: For PostgreSQL 9.1 and later, using right() with a negative length (right(j.status, -2)) provides a faster and cleaner method for removing leading characters compared to substring().

Data Type Considerations: If DeleteFlag represents boolean values, using the boolean data type is more efficient and semantically correct than text. Avoid unnecessary double quotes around column names in WHERE clauses.

Streamlined Function (SQL Implementation)

For enhanced readability and potential performance gains, consider a pure SQL implementation:

<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 j.id, j.reference, j.job_title
        , ltrim(right(j.status, -2)) AS status
   FROM job j
   JOIN company c ON j.id = c.id
   WHERE c.active
   AND NOT c.delete_flag
   AND NOT j.delete_flag
   AND (j.id = ANY() OR '{-1}'::int[] = )
   ORDER BY j.job_title
$func$ LANGUAGE sql;</code>

This simplified version achieves the same functionality with improved conciseness. Remember to adjust table and column names to match your specific schema.

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