The following bug has been logged online:
Bug reference: 3040
Logged by: Dreas Nielsen
Email address: [EMAIL PROTECTED]
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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
