Skip site navigation (1) Skip section navigation (2)

boolean short-circuiting in plpgsql

From: Kev <kevinjamesfield(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: boolean short-circuiting in plpgsql
Date: 2008-07-30 20:31:25
Message-ID: (view raw or whole thread)
Lists: pgsql-general
Hi everyone,

I may be missing something obvious, but it seems like the advice in
4.2.12 on
doesn't seem to apply in plpgsql.

I have a table that I want to use a trigger on when either a new row
is inserted or at least one of two particular columns is updated.
This fails on insert:

	if TG_OP = 'INSERT' or (new.sortnum != old.sortnum or new.parent !=
old.parent) then
		perform recalc_sortnumpath(;
	end if;
	return new;

...because 'old' doesn't exist and the latter argument of the 'or'
gets evaluated despite the TG_OP being 'INSERT'.  According to the
docs I should change that line to:

if (select case when TG_OP = 'UPDATE' then (new.sortnum != old.sortnum
or new.parent != old.parent) else 't' end) then

...because the case should force it to only evaluate 'old' when TG_OP
=  'UPDATE' and otherwise ('INSERT') skip through to 't'.  But this
causes the same error on insert.  I suspect it's because the select
query gets parameterized and at that point the 'old' is missing,
before the case even gets to be parsed.  How do I get around this
without having two 'perform' statements?  Is there no short-circuit
option in plpgsql?



pgsql-general by date

Next:From: Richard BroersmaDate: 2008-07-30 20:53:27
Subject: Re: Declaring constants in SQL
Previous:From: EXT-Rothermel, Peter MDate: 2008-07-30 20:16:35
Subject: Declaring constants in SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group