Thread: Optimizing Pl/PGSQL stored proc (nested conditionals)...

Optimizing Pl/PGSQL stored proc (nested conditionals)...

From
Sean Chittenden
Date:
Howdy.  I've got a quick snippet below of a stored procedure that sets
utc_end_date to the time when the row is disabled.  The reverse is
true also that if it's enabled, then it will remove the utc_end_date
and set it to NULL.  Anyway, I had to use nested IF blocks because I
couldn't get the case or switch statement to work.  Is this the most
optimal way to do this?  -sc

/* Validates and sets the end_date */
CREATE FUNCTION update_enabled() RETURNS opaque AS '
BEGIN
        IF NEW.enabled = ''y'' OR NEW.enabled = ''n'' THEN
                IF NEW.enabled = ''y'' THEN
                        NEW.utc_end_date := NULL;
                ELSE
                        NEW.utc_end_date := (SELECT NOW());
                END IF;
        ELSE
                IF NEW.enabled IS NOT NULL THEN
                        RAISE EXCEPTION ''Invalid enabled value (%): can be only (y) or (n)'', NEW.enabled;
                END IF;
        END IF;
        RETURN NEW;
END;
' LANGUAGE 'plpgsql'


--
Sean Chittenden