Hi everyone,
I may be missing something obvious, but it seems like the advice in
4.2.12 on http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html
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:
begin
if TG_OP = 'INSERT' or (new.sortnum != old.sortnum or new.parent !=
old.parent) then
perform recalc_sortnumpath(new.id);
end if;
return new;
end;
...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?
Thanks,
Kev
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
