Обсуждение: BUG #3040: Domain type handling change in 8.2.2 breaks declarations
The following bug has been logged online: Bug reference: 3040 Logged by: Dreas Nielsen Email address: dreas.nielsen@gmail.com PostgreSQL version: 8.2.3 Operating system: FreeBSD Description: Domain type handling change in 8.2.2 breaks declarations Details: If a domain type is declared NOT NULL, and that domain type is used in a composite data type, then declarations of that composite data type in a plpgsql function raise an error because the custom domain value is NULL--and the composite data type cannot be initialized so that it is non-null. Example: CREATE DOMAIN real_value AS double precision NOT NULL; CREATE DOMAIN significant_digits AS integer DEFAULT 2 NOT NULL; CREATE TYPE measurement_result AS (value real_value, sig_figs significant_digits, std_dev double precision, undetected measurement_qualifier, estimated measurement_qualifier, rejected measurement_qualifier, greater_than measurement_qualifier); CREATE OR REPLACE FUNCTION avg_mv_half(currstate measval_accum) RETURNS measurement_result AS $BODY$ DECLARE rv measurement_result; BEGIN rv := avg_mv_fact( currstate, 0.5 ); RETURN rv; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; The declaration of the variable rv in function avg_mv_half() fails. This behavior appeared with version 8.2.2, and appears to be related to the item in the release notes that says "Improve PL/pgSQL handling of domain types (Sergiy Vyshnevetskiy, Tom)". I suggest that restrictions on domain types be enforced in the RETURN statement of a plpgsql function rather than in the DECLARE statement. The only workaround that I have found so far is to ALTER DOMAIN ... DROP NOT NULL, but the whole point of defining those domains in the first place was to enforce a NOT NULL constraint in the composite data type.
"Dreas Nielsen" <dreas.nielsen@gmail.com> writes: > CREATE DOMAIN real_value > AS double precision > NOT NULL; > CREATE DOMAIN significant_digits > AS integer > DEFAULT 2 > NOT NULL; > CREATE TYPE measurement_result AS > (value real_value, > sig_figs significant_digits, > std_dev double precision, > undetected measurement_qualifier, > estimated measurement_qualifier, > rejected measurement_qualifier, > greater_than measurement_qualifier); > CREATE OR REPLACE FUNCTION avg_mv_half(currstate measval_accum) > RETURNS measurement_result AS > $BODY$ > DECLARE > rv measurement_result; > The declaration of the variable rv in function avg_mv_half() fails. AFAICS it should do so, since you neglected to provide a non-null initial value. Now there is an implementation deficiency here, since if you try to fix it: rv measurement_result := '(0,0 ... you get ERROR: default value for row or record variable is not supported CONTEXT: compile of PL/pgSQL function "avg_mv_half" near line 2 I think that should be fixed, but that's not what you are claiming the bug is. > I suggest that restrictions on domain types > be enforced in the RETURN statement of a plpgsql function rather than in the > DECLARE statement. If you don't want the domain restrictions enforced against the temporary variable, don't declare it as being of the domain type. Personally I think that NOT NULL domain restrictions are the stupidest idea I've seen lately, as they break all sorts of behavior, starting with outer joins. But if you insist on using one, do not complain when it gets enforced against you. regards, tom lane