Syntax error near 'WHEN place.price > 3000 THEN' in PostgreSQL function

Question

I'm working on a PostgreSQL function in SQL Editor in Supabase that filters places based on price categories provided by array of strings. For example ['$', '$$']. Here's my code:

CREATE OR REPLACE FUNCTION get_places_by_price(
    price_category_param text[]
)
RETURNS SETOF public.places AS $$
BEGIN
    RETURN QUERY
    SELECT
        *
    FROM
        public.places AS place
    WHERE 
        (CASE
            WHEN place.price = 0 THEN 'free'
            WHEN place.price <= 1500 THEN '$' 
            WHEN place.price <= 3000 THEN '$$'
            WHEN place.price > 3000 THEN '$$$'
        END) IN (SELECT unnest(price_category_param));
END;
$$ LANGUAGE plpgsql;

However, I encounter a syntax error near 'WHEN place.price > 3000 THEN'. Can someone please help me to fix it? Thank you!

Answer 1

Your dollar quoting gets mixed up by the $$ in the function body. Use another delimiter for the function definition:

CREATE FUNCTION get_places_by_price(...) RETURNS ...
AS $something$
   /* function body containing $$ and $$$ */
$something$;