Re: Convincing STABLE functions to run once

On Tue, Sep 9, 2014 at 10:23 AM, Dan Wells <dbw2(at)calvin(dot)edu> 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