Select into table%ROWTYPE failed
От | marcelo |
---|---|
Тема | Select into table%ROWTYPE failed |
Дата | |
Msg-id | 101ff717-5edc-b671-1903-098ae1f74bd3@gmail.com обсуждение исходный текст |
Ответы |
Re: Select into table%ROWTYPE failed
|
Список | 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
В списке pgsql-general по дате отправления: