Re: Bug in triggers

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. =A0If 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 (
>> =A0=A0=A0=A0 "typename" VARCHAR
>> );
>> =A0CREATE TYPE "type_row" AS (
>> =A0=A0=A0=A0 "typename" VARCHAR,
>> =A0=A0=A0=A0 "subrow" type_subrow
>> =A0);
>>
>> =A0CREATE OR REPLACE FUNCTION "test_bug"(in_row type_row) RETURNS void AS
>> =A0$body$
>> =A0DECLARE
>> =A0=A0=A0=A0 var type_row%rowtype;
>> =A0BEGIN
>> =A0=A0=A0=A0 var :=3D in_row;
>> =A0=A0=A0=A0 RAISE NOTICE 'Original value: %', in_row;
>> =A0=A0=A0=A0 RAISE NOTICE 'Assigned value: %', var;
>>
>> =A0=A0=A0=A0 IF var::TEXT <> in_row::TEXT THEN
>> =A0=A0=A0=A0 =A0=A0=A0 RAISE EXCEPTION 'var is not equals in_row';
>> =A0=A0=A0=A0 END IF;
>> =A0END;
>> =A0$body$
>> =A0LANGUAGE 'plpgsql';
>>
>> =A0SELECT test_bug('("Test",)'::type_row);
>
> Will output:
>
>> =A0NOTICE:=A0 Original value: (Test,"()")
>> =A0NOTICE:=A0 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:
>
>> =A0ROLLBACK;
>> =A0BEGIN;
>>
>> =A0CREATE TYPE "type_subrow" AS (
>> =A0=A0=A0=A0 "typename" VARCHAR
>> =A0);
>> =A0CREATE TABLE "type_row" (
>> =A0=A0=A0=A0 "typename" VARCHAR,
>> =A0=A0=A0=A0 "subrow" type_subrow
>> =A0);
>>
>> =A0CREATE OR REPLACE FUNCTION "test_bug"() RETURNS trigger AS
>> =A0$body$
>> =A0DECLARE
>> =A0=A0=A0=A0 var type_row%rowtype;
>> =A0BEGIN
>> =A0=A0=A0=A0 var :=3D NEW;
>> =A0=A0=A0=A0 RAISE NOTICE 'Original value: %', NEW;
>> =A0=A0=A0=A0 RAISE NOTICE 'Assigned value: %', var;
>>
>> =A0=A0=A0=A0 IF var::TEXT <> NEW::TEXT THEN
>> =A0=A0=A0=A0 =A0=A0=A0 RAISE NOTICE 'var is not equals NEW';
>> =A0=A0=A0=A0 END IF;
>>
>> =A0=A0=A0=A0 RETURN NEW;
>> =A0END;
>> =A0$body$
>> =A0LANGUAGE 'plpgsql';
>>
>> =A0CREATE TRIGGER "t_bug" BEFORE INSERT
>> =A0ON type_row FOR EACH ROW
>> =A0EXECUTE PROCEDURE "test_bug"();
>>
>> =A0INSERT INTO type_row VALUES('Test', NULL);
>
> Will output:
>
>> =A0NOTICE:=A0 Original value: (Test,)
>> =A0NOTICE:=A0 Assigned value: (Test,"()")
>> =A0NOTICE:=A0 var is not equals NEW
>
> As you see -=A0 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