Cochise Ruhulessin wrote: > If an immutable function raises an exception, is that exception cached by the query optimizer? Or does > it only cache in the case that a function actually returns a value? If an error occurs, query processing is terminated, so nothing needs to be cached. PostgreSQL doesn't cache function results, immutable or not: CREATE OR REPLACE FUNCTION i(integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE STRICT AS $$BEGIN RAISE NOTICE 'Called for %', $1; RETURN $1; END$$; WITH t(t) AS (VALUES (1), (2), (1)) SELECT i(t) FROM t; NOTICE: Called for 1 NOTICE: Called for 2 NOTICE: Called for 1 i --- 1 2 1 (3 rows) The difference is that an immutable function, when applied to a constant, can be evaluated at query planning time: WITH t(t) AS (VALUES (1), (2), (1)) SELECT i(42) FROM t; NOTICE: Called for 42 i ---- 42 42 42 (3 rows) Notice that the function was evaluated only once. > The use case is a table books(book_id NOT NULL PRIMARY KEY, type_id) wherein type_id is considered > immutable (enforced with a trigger). No database object is immutable (note that "immutable" means something else here than in the case of a function, so don't mix those up). You can, for example, drop the table. Any function that SELECTs from the database cannot be immutable. > The function f() must return type_id given book_id, and raise an exception if no entity with book_id > exists. I'd like this function to be immutable so it can be used as a check constraint. The documentation says in http://www.postgresql.org/docs/current/static/sql-createtable.html Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. CHECK constraints are only verified when the value is modified, so nothing can prevent the constraint from getting violated after the row has been added. It might, for example, lead to problems during dump/restore, as seen here: http://www.postgresql.org/message-id/29488.1332857456@xxxxxxxxxxxxx What should the CHECK constraint achieve? Maybe it can be expressed with a BEFORE trigger or some other construct. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
