PostgreSQL RPCs: Handling Required Array Parameters that can be Null or Empty

In this article, we will explore how to handle required array parameters in PostgreSQL RPCs (Remote Procedure Calls) that can be null or empty. This is a common issue faced by many developers when working with APIs and views.

Problem Statement

Imagine you have a PostgreSQL view that filters rows based on various criteria, including categories, colors, and other attributes. You want to allow users to request filtered results using array parameters, such as catg_article and color_article. However, you also want to accommodate cases where the user doesn’t provide any values for these parameters, resulting in a return of all possible combinations.

Background

The problem arises when trying to use PostgreSQL’s built-in functions like ANY() or IN() with array parameters. By default, these functions require non-empty arrays as input.

To demonstrate this, let’s consider the following SQL query:

SELECT *
FROM dev.get_all_articles
WHERE get_all_articles.lib_article ILIKE '%' || $1 || '%'
AND ($2 is null or CAST(get_all_articles.catg_et_type-'id_catgarticle' AS BIGINT) = any ($2));

In this example, the ANY() function is used to check if the catg_et_type field matches any value in the $2 array. However, if the user doesn’t provide a value for $2, this query will not return any results.

Solution

To solve this issue, we can use a combination of PostgreSQL’s syntax and clever indexing to achieve the desired behavior.

One approach is to add an additional check in the WHERE clause:

SELECT *
FROM dev.get_all_articles
WHERE get_all_articles.lib_article ILIKE '%' || $1 || '%'
AND ($2 is null OR ($3 = '{}'));

In this modified query, we’ve added a new condition $3 = '{}', which checks if the $2 array is equal to an empty string. If it is, we return all results.

However, this approach has some limitations and potential drawbacks.

Alternative Approach

A better solution involves using PostgreSQL’s NULLS FIRST or NULLS LAST clause in conjunction with indexing on the WHERE column. This allows us to specify that NULL values should be evaluated first or last, respectively.

Here’s an updated query:

SELECT *
FROM dev.get_all_articles
WHERE get_all_articles.lib_article ILIKE '%' || $1 || '%'
AND ($2 is null OR (CAST(get_all_articles.catg_et_type-'id_catgarticle' AS BIGINT) = any ($2))::boolean);

In this query, we’ve added a cast to boolean when evaluating the ANY() function. This ensures that NULL values are treated as FALSE.

Another approach is to use PostgreSQL’s pg_catalog.pg_array functions to generate an array of distinct values from the $2 column:

SELECT *
FROM dev.get_all_articles
WHERE get_all_articles.lib_article ILIKE '%' || $1 || '%'
AND ($2 IS NULL OR ($3 = ANY (SELECT DISTINCT * FROM pg_catalog.pg_array(
  SELECT catg_et_type - 'id_catgarticle' AS val
  FROM get_all_articles
)));

In this query, we’re using the ANY() function with a subquery that returns an array of distinct values from the $2 column.

Best Practices

When handling NULL or empty values in PostgreSQL RPCs:

1. Always validate user input to prevent SQL injection attacks.
2. Use indexing on columns used in WHERE clauses to improve query performance.
3. Consider using PostgreSQL’s NULLS FIRST or NULLS LAST clause to specify NULL value evaluation order.
4. Be mindful of array comparison operations, such as ANY() and IN(), when working with NULL values.

By following these best practices and exploring the various solutions outlined above, you can create robust and efficient PostgreSQL RPCs that handle required array parameters with ease.

Last modified on 2025-01-13