On Tue, Sep 9, 2014 at 10:23 AM, Dan Wells <dbw2@xxxxxxxxxx> wrote: > Hello all, > > I’ve run into this issue in several contexts recently, and wonder if folks > here can help clear up my understanding of function volatility. I often > have functions which are not truly immutable (they do something minor, like > read in configuration information), but the functions themselves are fairly > expensive, so I want them to run just once per query. At face value, I feel > like STABLE should do what I want, but often it does not. Here is a simple > example of what I am talking about (tested on 9.1.9): > > -------------------------------------------------------------------------- > CREATE TABLE t1(id INT PRIMARY KEY, val TEXT); > > -- Using numbers as "text" for convenience > INSERT INTO t1 SELECT generate_series(1,1000), random() * 1000; > > -- The real function reads configuration from the DB, and so > -- cannot be truthfully IMMUTABLE > -- > -- This function returns 'text' to better match my real case, > -- but is otherwise just for demonstration > -- > CREATE OR REPLACE FUNCTION passthru(myval text) > RETURNS text > LANGUAGE plpgsql > STABLE STRICT > AS $function$ > DECLARE > BEGIN > RAISE NOTICE 'test'; > RETURN myval; > END; > $function$ > ; This is kinda off topic but I'd like to point out your 'passthru' function is a wonderful debugging trick. I write it like this: CREATE OR REPLACE FUNCTION Notice(anyelement) RETURNS anyelement AS $$ BEGIN RAISE NOTICE '%', $1; RETURN $1; END; $$ LANGUAGE PLPGSQL; The reason why that's so useful is that when you have complicated functions that depend on each other it can be kind of a pain to adjust complicated SQL so that it 'raise notices' values you'd want to see -- the passthrough function makes it a snap without adjusting query behavior. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
