Select into table%ROWTYPE failed
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
marcelo <marcelo.nicolet@gmail.com> writes:
What was wrong in the first approach?
plpgsql's "SELECT INTO" expects a one-for-one match between the output
columns of the SELECT and the columns of the INTO destination. So I'd
expect something like this to work:
DECLARE
ranger billnumberrange%ROWTYPE;
BEGIN
SELECT * FROM billnumberrange WHERE ... INTO ranger;
Your example clearly wasn't selecting all the columns, and it
wasn't clear whether you paid any attention to column ordering;
but both of those matter.
regards, tom lane
Ohh, you are right!
Thank you!
On 18/09/2018 14:10 , Tom Lane wrote:
marcelo <marcelo.nicolet@gmail.com> writes:
What was wrong in the first approach?
plpgsql's "SELECT INTO" expects a one-for-one match between the output
columns of the SELECT and the columns of the INTO destination. So I'd
expect something like this to work:DECLARE
ranger billnumberrange%ROWTYPE;
BEGIN
SELECT * FROM billnumberrange WHERE ... INTO ranger;Your example clearly wasn't selecting all the columns, and it
wasn't clear whether you paid any attention to column ordering;
but both of those matter.regards, tom lane
---
El software de antivirus Avast ha analizado este correo electrónico en busca de virus.
https://www.avast.com/antivirus