Re: Bug in triggers - Mailing list pgsql-bugs

I'm asking to fix this =3D)

On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas <robertmhaas@gmail.com> wrote:

> 2010/3/3 Oleg Serov <serovov@gmail.com>:
> >
> >
> > 2010/3/1 Robert Haas <robertmhaas@gmail.com>
> >>
> >> It's not obvious whether this is the same as one of the various other
> >> problems you've complained about.  If it isn't, an English description
> >> of what you think the problem is would probably improve your odds.
> >> See also:
> >>
> >> http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
> >>
> >> ...Robert
> >
> > Thanks! This was long time ago, so i reposted it due empty responses.
> > i think this problem already discussed by Tom Lane, it is about "Row of
> > nulls OR null row", but i couldn't find this thread in archive.
> >
> > So if you have null row in plpgsql and assign it to plpgsql var it will
> be
> > translated to row of nulls instead null row.
> > Here it is an example:
> > It is assign with direct function call:
> >>
> >> CREATE TYPE "type_subrow" AS (
> >>      "typename" VARCHAR
> >> );
> >>  CREATE TYPE "type_row" AS (
> >>      "typename" VARCHAR,
> >>      "subrow" type_subrow
> >>  );
> >>
> >>  CREATE OR REPLACE FUNCTION "test_bug"(in_row type_row) RETURNS void AS
> >>  $body$
> >>  DECLARE
> >>      var type_row%rowtype;
> >>  BEGIN
> >>      var :=3D in_row;
> >>      RAISE NOTICE 'Original value: %', in_row;
> >>      RAISE NOTICE 'Assigned value: %', var;
> >>
> >>      IF var::TEXT <> in_row::TEXT THEN
> >>          RAISE EXCEPTION 'var is not equals in_row';
> >>      END IF;
> >>  END;
> >>  $body$
> >>  LANGUAGE 'plpgsql';
> >>
> >>  SELECT test_bug('("Test",)'::type_row);
> >
> > Will output:
> >
> >>  NOTICE:  Original value: (Test,"()")
> >>  NOTICE:  Assigned value: (Test,"()")
> >
> > As you see - subrow of type row is not null, it is ROW(NULL).
> >
> > Now see how it will be in trigger:
> >
> >>  ROLLBACK;
> >>  BEGIN;
> >>
> >>  CREATE TYPE "type_subrow" AS (
> >>      "typename" VARCHAR
> >>  );
> >>  CREATE TABLE "type_row" (
> >>      "typename" VARCHAR,
> >>      "subrow" type_subrow
> >>  );
> >>
> >>  CREATE OR REPLACE FUNCTION "test_bug"() RETURNS trigger AS
> >>  $body$
> >>  DECLARE
> >>      var type_row%rowtype;
> >>  BEGIN
> >>      var :=3D NEW;
> >>      RAISE NOTICE 'Original value: %', NEW;
> >>      RAISE NOTICE 'Assigned value: %', var;
> >>
> >>      IF var::TEXT <> NEW::TEXT THEN
> >>          RAISE NOTICE 'var is not equals NEW';
> >>      END IF;
> >>
> >>      RETURN NEW;
> >>  END;
> >>  $body$
> >>  LANGUAGE 'plpgsql';
> >>
> >>  CREATE TRIGGER "t_bug" BEFORE INSERT
> >>  ON type_row FOR EACH ROW
> >>  EXECUTE PROCEDURE "test_bug"();
> >>
> >>  INSERT INTO type_row VALUES('Test', NULL);
> >
> > Will output:
> >
> >>  NOTICE:  Original value: (Test,)
> >>  NOTICE:  Assigned value: (Test,"()")
> >>  NOTICE:  var is not equals NEW
> >
> > As you see -  NEW.subrow is null.
> > But var.subrow is not null, it is ROW(NULL).
> >
> > Do you understand what is the problem?
>
> It does seem weird that assigning NEW to var changes the value; I'm
> not sure why that happens.  Is that what you're asking about?
>
> ...Robert
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>



--=20
=F3 =D5=D7=C1=D6=C5=CE=C9=C5=CD

=EF=CC=C5=C7 =F3=C5=D2=CF=D7