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

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Optimizing Pl/PGSQL stored proc (nested conditionals)...
Date: 2002-03-03 18:09:05
Message-ID: 20020303100905.G20143@ninja1.internal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Patrick Welche 2002-03-03 18:32:43 Re: oids vs. serial question
Previous Message geotronix 2002-03-03 16:44:08 How do I pass the -i option during boot time?