如何在Vertica中创建函数？自定义函数编写报错求助

Hey there! I see you're just getting started with Vertica and ran into an issue creating a custom function. Let's break down the problems in your code and fix them up.

Issues in Your Original Code #

• You declared the res variable but didn't assign the result of your SELECT query to it—Vertica requires using the INTO clause to store query results in variables.
• The function definition was missing the closing $function$ tag and a required semicolon at the end of the END block.

Corrected Function Code #

CREATE OR REPLACE FUNCTION itog_report_status.get_warn_lvl(day_count NUMERIC) 
RETURNS VARCHAR 
AS $function$ 
DECLARE 
    res VARCHAR; 
BEGIN 
    SELECT status::VARCHAR INTO res 
    FROM itog_reports_status.status_warn_level 
    WHERE days = ( 
        SELECT MIN(days) 
        FROM itog_reports_status.status_warn_level 
        WHERE day_count <= days
    ); 
    RETURN COALESCE(res, 'RED'); 
END;
$function$;

Key Fixes Explained #

1. Added INTO res: This tells Vertica to store the result of the SELECT status::VARCHAR query into your res variable—without this, the function doesn't know where to put the query result you're trying to fetch.
2. Fixed Closing Syntax: Added the closing $function$ tag and a semicolon after the END to properly terminate the function definition. Vertica is strict about matching the opening/closing delimiters for function bodies.
3. Cleaned Up Indentation: I formatted the code for readability, which makes it easier to spot syntax gaps or logical issues later on.

Additional Checks #

If you still run into errors after using this corrected code, double-check:

• That the itog_reports_status.status_warn_level table exists and you have permission to read from it.
• That the days column in the table is a numeric type (matching your day_count parameter) to avoid type mismatch errors.
• That there are records in the table where days >= day_count (though your COALESCE already handles cases where no matches are found by returning 'RED').

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