PostgreSQL SQL是否有Python map等价函数？如何处理文本数组元素？

Great questions! Let's break these down step by step.

Absolutely! PostgreSQL offers a couple of ways to replicate Python's map() functionality, depending on your server version:

• PostgreSQL 14+: Use the built-in array_map() function directly. It works exactly like Python's map()—you pass a function and an array, and it applies the function to every element in the array.
• Older PostgreSQL versions: Combine unnest() (which expands an array into individual rows) with array_agg() (which aggregates transformed rows back into an array). This workaround is reliable and works across most supported versions.

For your specific example—extracting the last segment from each string split by -—we can leverage PostgreSQL's built-in split_part() function, which accepts parameters for the delimiter and segment position. Here are two solid approaches:

Using array_map() (PostgreSQL 14+) #

This is the cleanest, most Python-like approach:

SELECT array_map(
  elem -> split_part(elem, '-', -1),
  ARRAY['abc-123-x', 'def-123-y', 'hij-234-k', 'klm-232-p', 'nop-3434-9', 'qrs-23-p9']
);

The lambda elem -> split_part(elem, '-', -1) applies split_part() to each element. The -1 parameter tells PostgreSQL to grab the last segment after the final -—perfect for your use case.

Compatibility Approach (All Supported Versions) #

If you're running an older PostgreSQL version, use this tried-and-true method:

SELECT array_agg(split_part(elem, '-', -1)) AS transformed_array
FROM unnest(ARRAY['abc-123-x', 'def-123-y', 'hij-234-k', 'klm-232-p', 'nop-3434-9', 'qrs-23-p9']) AS elem;

Both queries will return your desired result: {x,y,k,p,9,p9}.

Bonus: Reusable Custom Function #

If you need to reuse this logic (or apply a more complex parameterized function), define a custom SQL function:

CREATE OR REPLACE FUNCTION get_last_segment(str text, delimiter text)
RETURNS text AS $$
BEGIN
  RETURN split_part(str, delimiter, -1);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Then use it with array_map():

SELECT array_map(elem -> get_last_segment(elem, '-'), your_array_column)
FROM your_table;

Or with the compatibility method:

SELECT array_agg(get_last_segment(elem, '-'))
FROM unnest(your_array_column) AS elem;

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