PostgreSQL：检查函数输入数组是否为空、含NULL或仅含NULL

Let's refine your _product_ids::BIGINT[] validation logic to control when the "contains NULL" check triggers an exception. First, let's fix the original NULL-in-array check (your current -1 = ANY(_product_ids) IS NULL isn't the most reliable or intuitive way to detect NULLs in an array) and then tailor it to your specific scenarios.

First: Fix the Base NULL-in-Array Check #

The correct, readable ways to check if an array contains any NULL value are:

• NULL = ANY(_product_ids) (PostgreSQL returns true if any element in the array is NULL)
• array_position(_product_ids, NULL) IS NOT NULL (explicitly checks for the presence of a NULL and returns its position if found)

We'll use these clearer methods in the adjusted code below.

Scenario 1: Disallow ANY NULL in the Array #

If you want to raise an exception whenever the array contains even one NULL value (alongside your existing checks for a NULL array or empty array), here's the updated code:

IF (
    _product_ids IS NULL -- Array itself is NULL
    OR NULL = ANY(_product_ids) -- Array contains at least one NULL element
    OR COALESCE(ARRAY_LENGTH(_product_ids, 1) < 1, TRUE) -- Empty array
) THEN
    RAISE EXCEPTION 'INPUT IS INVALID: product IDs array cannot be NULL, empty, or contain NULL values';
END IF;

Scenario 2: Allow Mixed NULL/Non-NULL, Disallow ALL-NULL Arrays #

If you want to permit arrays with a mix of NULL and non-NULL values, but raise an exception only when the array contains nothing but NULLs (plus the existing NULL/empty array checks), use this logic:

IF (
    _product_ids IS NULL -- Array itself is NULL
    OR COALESCE(ARRAY_LENGTH(_product_ids, 1) < 1, TRUE) -- Empty array
    OR (
        NULL = ALL(_product_ids) -- Every element in the array is NULL
        AND ARRAY_LENGTH(_product_ids, 1) >= 1 -- Clarify it's a non-empty all-NULL array
    )
) THEN
    RAISE EXCEPTION 'INPUT IS INVALID: product IDs array cannot be NULL, empty, or consist solely of NULL values';
END IF;

Scenario 3: Custom Conditional NULL Check #

If your "specific scenario" is something more targeted (e.g., only check for NULLs if the array has more than 5 elements), wrap the NULL check in a conditional. For example:

IF (
    _product_ids IS NULL
    OR COALESCE(ARRAY_LENGTH(_product_ids, 1) < 1, TRUE)
    -- Only enforce no NULLs if array has 3+ elements
    OR (ARRAY_LENGTH(_product_ids, 1) >= 3 AND NULL = ANY(_product_ids))
) THEN
    RAISE EXCEPTION 'INPUT IS INVALID: product IDs array cannot be NULL, empty, or contain NULLs when it has 3+ elements';
END IF;

Each example keeps your core validation rules while making the NULL-in-array check explicit and fully aligned with your exact requirements.

内容的提问来源于stack exchange，提问作者Dan