Re: Immutable functions, Exceptions and the Query Optimizer

Thanks for your elaborate reply and providing me these insights on the
concept on immutable functions.

Regarding your question about what the CHECK constraint should achieve, I
had abstracted by use case into Books/Book Types, which may have caused
some vagueness. The actual use case are the following tables.

------------------------------------------------------------------------
-- Describes a type of geographical entity.
--
-- Columns:
-- gtype_id: The primary key and identifier code of type.
-- feature_class: A character indicating the feature class.
-- display_name: The display name of the type.
-- description: A description of the type.
-- is_enabled: Indicates if type is globally enabled.
-- is_system: Indicates if type is system and therefor may not
-- be modified.
-- is_deleted: Indicates if type is considered deleted.
------------------------------------------------------------------------
CREATE TABLE gtypes(
feature_code varchar(64) NOT NULL PRIMARY KEY,
feature_class varchar(1) NOT NULL,
display_name varchar(128) NOT NULL,
--administrative_depth integer NOT NULL CHECK(administrative_depth > 0)
DEFAULT 0,
description text,
is_enabled boolean NOT NULL DEFAULT TRUE,
is_system boolean NOT NULL DEFAULT FALSE,
is_deleted boolean NOT NULL DEFAULT FALSE,
UNIQUE (feature_code, feature_class)
);

------------------------------------------------------------------------
-- Describes a geographical entity.
--
-- Columns:
-- feature_id: An unsigned long integer specifying the primary
-- key.
-- gtype_id: A string referencing a gtype instance.
-- ascii_name: The entity name as ascii.
-- display_name: A string containing the display name of entity,
-- English preferred.
-- native_name: A string containing the native name of entity.
-- valid_from: A date specifying the valid from date of entity.
-- valid_to: A date specifying the valid to date of entity.
-- primary_datasource: A string indicating the primary datasource
-- of entity.
-- is_deleted: A boolean indicating if entity is considered deleted.
-- created: A timestamp with time zone indicating the date and
-- time entity was inserted.
------------------------------------------------------------------------
CREATE SEQUENCE feature_id_seq START WITH 100000;
CREATE TABLE features(
feature_id bigint NOT NULL PRIMARY KEY DEFAULT
nextval('feature_id_seq'),
feature_code varchar(64) NOT NULL -- Immutable
REFERENCES gtypes (feature_code)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE INITIALLY DEFERRED,
feature_name varchar(255),
feature_name_native varchar(255),
ascii_name varchar(512) NOT NULL,
display_name varchar(512) NOT NULL,
native_name varchar(512),
abbreviation varchar(64),
timezone varchar(64),
valid_from date NOT NULL DEFAULT now()::date,
valid_to date,
created timestamp with time zone NOT NULL DEFAULT now(),
modified timestamp with time zone NOT NULL DEFAULT now(),
primary_datasource varchar(64) NOT NULL, -- Immutable
is_deleted boolean NOT NULL DEFAULT FALSE,
CHECK (valid_to >= valid_from),
UNIQUE (feature_id, feature_code)
);

------------------------------------------------------------------------
-- trigger function to handle immutable fields on the features table.
------------------------------------------------------------------------
CREATE FUNCTION chk_features_immutable()
RETURNS TRIGGER AS
$$
BEGIN
CASE
WHEN OLD.feature_code != NEW.feature_code THEN
RAISE SQLSTATE '23514' USING MESSAGE = 'features.feature_code
is immutable';
WHEN OLD.primary_datasource != NEW.primary_datasource THEN
RAISE SQLSTATE '23514' USING MESSAGE =
'features.primary_datasource is immutable';
ELSE
RETURN NEW;
END CASE;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER tr_chk_features_immutable
BEFORE UPDATE ON features
FOR EACH ROW
EXECUTE PROCEDURE chk_features_immutable();

------------------------------------------------------------------------
-- Returns the feature code of a given feature.
--
-- Args:
-- feature_id bigint: The primary key of a features entity.
--
-- Returns:
-- varchar(64)
------------------------------------------------------------------------
CREATE FUNCTION features_get_feature_code(int8)
RETURNS varchar(64) AS
$$
DECLARE
fcode varchar(64);
BEGIN
SELECT feature_code INTO fcode FROM features WHERE feature_id = $1;
IF NOT FOUND THEN
RAISE EXCEPTION 'Entity does not exist.';
END IF;
RETURN fcode;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

The "features" table contains countries, administrative divisions, cities,
postal codes, landmarks, sights, rivers, mountains; any kind of
geographical feature.

There is also a table called "persons" (irrelevant fields omitted):

CREATE TABLE persons(
person_id int8 NOT NULL PRIMARY KEY,
place_of_birth_id int8
REFERENCES features (feature_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
INITIALLY IMMEDIATE,
CHECK (features_get_feature_code(place_of_birth_id) ~ 'PC.*|ADM.*|PP.*')
);

The CHECK constraint should achieve that "persons.place_of_birth_id" is
always a country, or a (first_order) adminitrative division, or a city
(which is defined by "features.gtype_id").

Though this could be done by creating a multi-column foreign key on
("features.feature_id","features.gtype_id"), this would violate the
principles of normalization.

Of course this could also be achieved by a TRIGGER, but that seems a little
redundant to me.

Kind regards,

Cochise Ruhulessin