CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.5
Bruno Queiroz
Bruno Queiroz

Reputation: 377

Should i use STABLE or VOLATILE in a function that performs a query?

i'm trying to use the right modifier to a particular function that checks if a count is bigger or equal 2, but i'm not sure which one to use, here is the function:

CREATE FUNCTION check_table_ids()
RETURNS trigger AS
$$
DECLARE
   counter integer := (SELECT count(*) FROM table WHERE fk_id = NEW.fk_id AND status <> 'CANCELED');
BEGIN
   IF counter >= 2 THEN
       RAISE EXCEPTION 'The number of entries for "%" is greater or equal than 2', NEW.fk_id;
   END IF;

   RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

BTW this function will be called by a trigger on insert.

Upvotes: 4

Views: 2816

Answers (1)

Philip Couling
Philip Couling

Reputation: 14913

According to the manual (https://www.postgresql.org/docs/current/static/sql-createfunction.html)

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements.

So if you modify the database or give different results without the database changing then use VOLATILE otherwise use STABLE.

For the code in your question STABLE should be fine.

The fact that this is called by a trigger doesn't make a difference as it's the content of the function you are declaring as STABLE not its usage.

Upvotes: 4

Related Questions