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