AI doesn't write perfect code… but it can with a bit of help

Recently, I've been experimenting with integrating AI into my development process, and I've learned a lot about its potential and limitations.

This article details a real-world debugging journey I undertook, shared with permission from a client, to build a robust wildcard search function in PostgreSQL. This process took about 2 hours of iterative refinement, highlighting how AI can be a powerful assistant, but it's crucial to remember that it's just that, an assistant.

The initial problem was a common one: our client needed a flexible search function for their product catalog, stored in a PostgreSQL database. They wanted users to be able to search across brand, model, and reference_number using partial matches, whole-word matches, and combinations of both. A simple LIKE query wouldn't cut it for performance, and PostgreSQL's full-text search, while powerful, wasn't the ideal fit for this specific use case.

The Iterative Refinement Process

My approach was to leverage trigram indexes for efficient substring matching. I started by creating a basic function and index:

-- Initial function (using to_tsvector/to_tsquery)
CREATE OR REPLACE FUNCTION search_watches(prefix text)
RETURNS SETOF watches AS $$
	BEGIN 
	RETURN QUERY 
		SELECT * FROM watches 
		WHERE to_tsvector(brand || ' ' || model || ' ' || reference_number) @@ to_tsquery(prefix || ':*');
END;
$$ LANGUAGE plpgsql;
-- Trigram index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX trgm_watches_brand_model_ref_idx ON watches 
USING gin ((brand || ' ' || model || ' ' || reference_number) gin_trgm_ops);

I then enlisted the help of an AI coding assistant. My goal was to create a function that could handle wildcard searches and combine them with whole-word matching. The AI provided several iterations of the function, each with its own quirks.

Attempt 1: Wildcards with ILIKE

The AI suggested using ILIKE for wildcard support:

-- AI-generated function (ILIKE)
CREATE OR REPLACE FUNCTION search_watches(search_term text)
RETURNS TABLE (watch_id int, brand text, model text, reference_number text) AS $$
DECLARE modified_search_term text;
	BEGIN 
		modified_search_term := replace(search_term, '*', '%'); 
		modified_search_term := replace(modified_search_term, '?', '_'); 
		modified_search_term := replace(modified_search_term, ' ', '+'); 
	RETURN QUERY 
	SELECT watch_id, brand, model, reference_number FROM watches 
	WHERE brand || ' ' || model || ' ' || reference_number ILIKE modified_search_term;
END;
$$ LANGUAGE plpgsql;

This was a good start, but it lacked the combined search capabilities I needed.

Attempts 2-5: Regex and Combined Matching

The AI then helped me explore regular expressions for whole-word matching and attempts to combine regex with trigram matching. These attempts, while promising, were plagued with subtle errors. The AI-generated code often had syntax issues, logical flaws in the combined patterns, or incorrect use of the trigram index. Here's an example of one of these attempts:

-- AI-generated function (regex and trigram combination - INCORRECT)
CREATE OR REPLACE FUNCTION search_watches(search_term TEXT)
RETURNS SETOF watches AS $$
DECLARE words TEXT[]; word TEXT; regex_pattern TEXT; trigram_pattern TEXT := ''; combined_pattern TEXT;
	BEGIN words := string_to_array(search_term, ' '); 
	-- ... (regex and trigram pattern building logic) 
	IF regex_pattern <> '' AND trigram_pattern <> '' 
	THEN combined_pattern := '(' || regex_pattern || ') AND (' || trigram_pattern || ')'; 
	-- ... (other conditions) 
RETURN QUERY 
	SELECT w.* FROM watches w 
	WHERE (w.brand || ' ' || w.model || ' ' || w.reference_number) ~* combined_pattern;
END;
$$ LANGUAGE plpgsql;

These attempts, while helpful for exploring different approaches, didn't produce a working solution.

Attempt 6: Individual Column Checks

We then realised the ~~* operator with trigram indexes works best on individual columns:

-- AI-generated function (individual column checks - STILL INCORRECT)
CREATE OR REPLACE FUNCTION search_watches(search_term TEXT)
RETURNS SETOF watches AS $$
DECLARE words TEXT[]; word TEXT; trigram_pattern TEXT := '';
BEGIN 
	-- ... (trigram pattern building logic) 
	RETURN QUERY 
	SELECT w.* FROM watches w 
	WHERE w.brand ~~* trigram_pattern 
	OR w.model ~~* trigram_pattern 
	OR w.reference_number ~~* trigram_pattern;
END;
$$ LANGUAGE plpgsql;

Attempt 7: Final Correction

Even this needed further refinement. We discovered issues with the trigram pattern generation and case sensitivity. The final, working version looked like this:

-- Final, corrected function
CREATE OR REPLACE FUNCTION search_watches(search_term TEXT)
RETURNS SETOF watches AS $$
DECLARE words TEXT[]; word TEXT; trigrams TEXT[]; trigram_array TEXT[];
BEGIN words := string_to_array(search_term, ' '); 
	trigram_array := ARRAY[]::TEXT[]; 
	FOREACH word IN ARRAY words 
	LOOP trigrams := ARRAY[]::TEXT[]; FOR i IN 0 .. length(word) - 3 
		LOOP trigrams := array_append(trigrams, lower(substring(word FROM i + 1 FOR 3))); 
		END LOOP; 
	trigram_array := trigram_array || trigrams; 
	END LOOP; 
	
RETURN QUERY SELECT w.* FROM watches w WHERE (w.brand || ' ' || w.model || ' ' || w.reference_number) ~* ANY(trigram_array);
END;
$$ LANGUAGE plpgsql;

The key was using the ANY operator with the case-insensitive regex match (~*) and correcting the trigram generation to correctly search across the trigram array.

Common Pitfalls and Tips for Using AI for Code Generation

This experience highlighted some common pitfalls of using AI for code generation:

• Subtle Errors: AI-generated code can contain subtle errors that are difficult to spot without careful testing.
• Logical Flaws: The AI might not always understand the nuances of your requirements, leading to logical flaws in the code.
• Over-Reliance: It's easy to become overly reliant on the AI, neglecting the importance of understanding the underlying logic.

To get the best results from AI code generation:

• Be Explicit: Clearly define your requirements and provide as much context as possible.
• Iterate and Test: Treat the AI's output as a starting point and be prepared to iterate and test thoroughly.
• Understand the Code: Don't just copy and paste. Make sure you understand how the code works.

The Importance of Review

Finally, and most importantly, always have an experienced engineer review AI-generated code before submitting it to production. While AI can be a valuable tool, it's not a replacement for human expertise. This project, while challenging, ultimately demonstrated the power of combining AI assistance with human oversight to build a robust and efficient solution.