Re: [SQL] Evaluation of if conditions
Daniel CAUNE wrote: Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? The following snippet seems to be invalid, which let me think that PL/PGSQL evaluates all the conditions: IF (TG_OP = 'INSERT') OR (OLD.bar = ...) THEN statement END IF; Should be rewritten as (for example): IF (TG_OP = 'INSERT') THEN statement ELSIF (OLD.bar = ...) THEN statement END IF; Regards, -- Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match The following is working fine for me on 8.1.x on Windows. I am not sure what order it is evaluating the if statement in but it is working correctly. Oisin CREATE OR REPLACE FUNCTION zfunc_testor(bool, bool) RETURNS varchar AS $BODY$DECLARE v_1 boolean; v_2 boolean; BEGIN v_1 :=$1; v_2 := $2; if (v_1 = TRUE) OR (v_2 = TRUE) then return 'At least 1 true'; else return 'neither true'; end if; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION zfunc_testor(bool, bool) OWNER TO postgres; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Evaluation of if conditions
Daniel, AFAIK there is no short-circuiting of evaluations in postgres and I don't think you can depend on the order they appear in to determine the order in which they are checked, although more knowledgable people may have better info than I ... so the rewritten form is the way to go. Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Daniel CAUNE Sent: Wed 9/6/2006 3:32 PM To: pgsql-sql@postgresql.org Cc: Subject:[SQL] Evaluation of if conditions Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? The following snippet seems to be invalid, which let me think that PL/PGSQL evaluates all the conditions: IF (TG_OP = 'INSERT') OR (OLD.bar = ...) THEN statement END IF; Should be rewritten as (for example): IF (TG_OP = 'INSERT') THEN statement ELSIF (OLD.bar = ...) THEN statement END IF; Regards, -- Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match --- Click link below if it is SPAM [EMAIL PROTECTED] https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=44ff5167171391789821027[EMAIL PROTECTED]retrain=spamtemplate=historyhistory_page=1 !DSPAM:44ff5167171391789821027! --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Evaluation of if conditions
Daniel CAUNE wrote: Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? Please see 4.2.12. Expression Evaluation Rules of the manual: The order of evaluation of subexpressions is not defined. In particular, the inputs of an operator or function are not necessarily evaluated left-to-right or in any other fixed order. There's more examples there too. Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Evaluation of if conditions
Daniel CAUNE [EMAIL PROTECTED] writes: How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? The following snippet seems to be invalid, which let me think that PL/PGSQL evaluates all the conditions: IF (TG_OP = 'INSERT') OR (OLD.bar = ...) THEN It's not that all the conditions get evaluated by an OR, it's that plpgsql needs to send all the parameter values that the IF-expression needs down to the core SQL engine. So it fails on OLD.bar not being defined, long before the expression evaluator gets to think about whether TG_OP = 'INSERT' or not. So, yeah, you want to rewrite it as two separate IF-tests. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
