PostgreSQL中遍历字符串数组时，如何获取每个SELECT查询的首个结果？

Hey there! Let's work through this problem to get your desired results without dragging down your 4GB Users table. Your goal is to grab one matching record for each prefix in a string array, right? Let's cover a clean pure-SQL approach, fix up that PL/pgSQL loop you tried earlier, and add critical performance tweaks to keep things fast.

First: Lay the Performance Foundation #

Before writing any queries, you need an index to avoid scanning your entire 4GB table dozens of times. For left-anchored LIKE matches (like 'Smith%'), create this optimized index:

CREATE INDEX idx_users_surname_prefix ON "Users" ("Surname" varchar_pattern_ops);

This index is tailored for prefix searches and will make all your lookups run lightning fast instead of doing full table scans.

Method 1: Pure SQL with Window Functions (Clean & Efficient) #

Instead of looping, we can unnest your search array, join to the Users table, and rank results per prefix to pick the first match. Here's how:

-- Replace @search_surnames with your actual array variable/value
WITH search_terms AS (
    SELECT unnest(@search_surnames) AS term
),
ranked_matches AS (
    SELECT
        u.*,
        -- Rank records per search term; adjust ORDER BY to pick your preferred "first" record
        ROW_NUMBER() OVER (PARTITION BY st.term ORDER BY u.Id) AS match_rank
    FROM "Users" u
    JOIN search_terms st 
        ON u."Surname" LIKE st.term || '%'
)
SELECT Id, Name, Surname
FROM ranked_matches
WHERE match_rank = 1;

How it works: #

1. unnest() turns your string array into individual rows of search terms.
2. We join each term to Users where the surname starts with that term.
3. ROW_NUMBER() assigns a rank to each match per term (sorted by Id here—you can change the ORDER BY if you want a different "first" record, like a creation date).
4. Finally, we filter to only keep the first-ranked match for each term.

For your sample data and ['Smith', 'Connor'] array, this will return exactly the output you want:

Method 2: Fixed PL/pgSQL Loop (If You Prefer Procedural Code) #

It sounds like your earlier FOREACH loop failed because of how you were handling results. Here's a working version that returns each matching record directly, no manual array appending needed:

CREATE OR REPLACE FUNCTION get_matching_users(search_surnames text[])
RETURNS SETOF "Users" AS $$
DECLARE
    term text;
    user_rec "Users"%ROWTYPE;
BEGIN
    FOREACH term IN ARRAY search_surnames LOOP
        -- Grab the first match for the current term
        SELECT * INTO user_rec
        FROM "Users"
        WHERE "Surname" LIKE term || '%'
        LIMIT 1;
        
        -- Only return a record if we found a match
        IF FOUND THEN
            RETURN NEXT user_rec;
        END IF;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

To use this function:

SELECT * FROM get_matching_users(ARRAY['Smith', 'Connor']);

This outputs the same result as the SQL method, and it's flexible if you need to add custom logic (like logging or fallback behavior) for each term.

Quick Notes #

• If you don't care which matching record you get (as you mentioned 1st or 2nd is fine), you can omit the ORDER BY in the window function/loop query—PostgreSQL will pick the first record it finds via the index, which is fast.
• For PostgreSQL 12+, you could use gin_trgm_ops indexes for more flexible pattern matching, but varchar_pattern_ops is still the most efficient for left-anchored LIKE queries.

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