PostgreSQL：如何将字符串数组转为bigint数组？存储过程异常求助

Hey Christian, let's break down why your stored procedure is only grabbing the first element from your input string array, and how to fix it. This is a common pitfall when working with array inputs in stored procedures, so let's walk through the most likely causes and solutions:

1. You're not expanding the input array (the most common issue) #

If your current code treats the input array $1 as a single value instead of iterating over its elements, it will only match the first entry. For example, if you wrote something like:

SELECT _id INTO lst_id FROM your_table WHERE codes = $1;

This either compares codes to the entire array (which only works if codes is an array itself, not a scalar string) or implicitly pulls just the first element of $1 in some databases.

Instead, you need to explicitly handle all array elements with one of these approaches:

• Use the ANY operator to check against every element in the input array:

  SELECT array_agg(_id::bigint) INTO lst_id FROM your_table WHERE codes = ANY($1);
  

• Or use unnest() to expand the array into individual rows, then join against your table:

  SELECT array_agg(_id::bigint) INTO lst_id 
  FROM your_table 
  JOIN unnest($1) AS input_codes(code) 
    ON your_table.codes = input_codes.code;
  

2. Input array type or format is incorrect #

Double-check that $1 is actually being passed as a string array (e.g., text[] in PostgreSQL) and not a single comma-separated string. If you're passing something like 'code1,code2' instead of ARRAY['code1','code2'], the database will treat it as a single-element array—hence only processing one value.

Add a quick debug check at the start of your procedure to verify the input:

RAISE NOTICE 'Input array length: %', array_length($1, 1);

If this outputs 1 when you expect multiple elements, your input format is the problem.

3. You're assigning a single value to the array variable #

If lst_id is defined as a bigint[] (array) type, using SELECT _id INTO lst_id will only store the first matching _id in the array's first position, not aggregate all matches. You must use array_agg() to collect all matching _id values into a single array, as shown in the examples above.

Full Working Example #

Here's a revised stored procedure that addresses all these points:

CREATE OR REPLACE PROCEDURE string_array_to_bigint_array(IN input_codes text[], OUT output_ids bigint[])
LANGUAGE plpgsql
AS $$
BEGIN
    -- Validate input isn't empty
    IF array_length(input_codes, 1) IS NULL THEN
        RAISE EXCEPTION 'Input array cannot be empty';
    END IF;

    -- Aggregate all matching _id values into a bigint array
    SELECT array_agg(_id::bigint) INTO output_ids
    FROM your_table
    WHERE codes = ANY(input_codes);

    -- Handle case where no matches are found (return empty array instead of NULL)
    IF output_ids IS NULL THEN
        output_ids := '{}'::bigint[];
    END IF;
END;
$$;

How to Test It #

Call the procedure with a proper array input:

-- In PostgreSQL/psql:
CALL string_array_to_bigint_array(ARRAY['code1', 'code2', 'code3'], output_ids);
SELECT output_ids;

This should return all matching _id values as a bigint array, not just the first one.

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