From: | marcelo <marcelo(dot)nicolet(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Select into table%ROWTYPE failed |
Date: | 2018-09-18 16:14:11 |
Message-ID: | 101ff717-5edc-b671-1903-098ae1f74bd3@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | James Keener | 2018-09-18 16:27:33 | Re: Code of Conduct |
Previous Message | Tomas Vondra | 2018-09-18 14:34:53 | Re: Code of Conduct |