I'm asking to fix this =) On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas <robertmh...@gmail.com> wrote:
> 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 > -- С уважением Олег Серов
