Re: Bug in triggers

I'm asking to fix this =)

On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 2010/3/3 Oleg Serov <serovov(at)gmail(dot)com>:
> >
> >
> > 2010/3/1 Robert Haas <robertmhaas(at)gmail(dot)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 := 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 := 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
С уважением

Олег Серов