I´m testing a trigger function in a 9.4 installation.
It´s for bill number assignation, but with a twist: there are various
numbering ranges. This ranges are defined by a text code, a minimum and
maximum. Every bill have some code taken from the set defined in a
specific table (billnumberrange)
The first approach was the obvious "select into" a row instance, using
table%ROWTYPE as the destination.
That failed, leaving all fields of the instance as null.
But selecting into the interesting fields works ok. The trigger function
follows; the initial approach lines are commented.
CREATE FUNCTION nextbillnumber() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
lastnumber integer;
lowerlimit integer;
upperlimit integer;
-- ranger billnumberrange%ROWTYPE;
BEGIN
if NEW.billnumber = 0 THEN
select billnumberrange.lowerlimit, billnumberrange.upperlimit
from billnumberrange
where billnumberrange.groupcode = NEW.groupcode into
lowerlimit, upperlimit;
--where billnumberrange.groupcode = NEW.groupcode into ranger;
-- RAISE NOTICE 'first select result % % <> %',
ranger.groupcode, ranger.lowerlimit, ranger.upperlimit; this NOTICE
showed <NULL> <NULL> <> <NULL>
RAISE NOTICE 'first select result % <> %', lowerlimit,
upperlimit;-- this shows the expected values
select max(billnumber) from bill
where billnumber BETWEEN lowerlimit and upperlimit
-- where billnumber BETWEEN ranger.lowerlimit and
ranger.upperlimit
into lastnumber;
RAISE NOTICE 'second select result %', FOUND;
if lastnumber is null THEN
lastnumber := lowerlimit;
-- lastnumber := ranger.lowerlimit;
end if;
RAISE NOTICE 'lastnumber is %', lastnumber;
NEW.billnumber = lastnumber + 1;
end if;
return NEW;
END;
$$;
What was wrong in the first approach?
TIA
---
El software de antivirus Avast ha analizado este correo electrónico en busca de
virus.
https://www.avast.com/antivirus
