Select into table%ROWTYPE failed

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

Responses

Browse pgsql-general by date

  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