Re: Immutable functions, Exceptions and the Query Optimizer - Mailing list pgsql-general

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