Re: Bug in triggers

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