Re: stable functions

On Thursday 31 May 2007 13:23:36 Filip Rembiałkowski wrote:
> 2007/5/31, Vincenzo Romano <vincenzo.romano@gmail.com>:
> > Hi all.
> >
> > Can functions whose effect is to create functions (yep!)
> > be labelled as "stable"?
>
> According to the docs, no.
>
> "STABLE indicates that the function cannot modify the database (...)
> any function that has side-effects must be classified volatile"
>
> http://www.postgresql.org/docs/8.2/static/sql-createfunction.html
>
> If you labeled creatorfunc(param) as stable, ant then issued "SELECT
> creatorfunc(sometable.param) FROM sometable", there is a theoretical
> risk that creatorfunc would be called only _once_ for each distinct
> value of param.

This is the complete quote:
<quote>
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.
This is the appropriate selection for functions whose results
depend on database lookups, parameter variables (such as the
current time zone), etc.
</quote>


They talk about table scans which should not involce the information schema
tables, the only tables that get modified by a fubction whose sole effect it
to create other functions.creatorfunc
Nonetheless your remark makes a lot of sense and I'm still in dubt.
In my case the "creatorfunc" has no parameters and returns void as it reads
data from configuration tables.
And it should be OK if ti were run only once.

I'd say that some more explaination in the documentatio would be great under
"33.6. Function Volatility Categories".

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]