Re: function calls in WHERE clause

On 2004-09-08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>"Lee Harr" <missive(at)hotmail(dot)com> writes:
>>I should note that this_program() is defined as VOLATILE.
>
>So don't do that ;-)
>

I had a feeling that was the answer... Actually, that was my
first thought, but then I looked back at the definition of
this_program() it was marked volatile. No other function
is marked like that, so it must be that without that it just
was not working right.

I think the problem is that at other times, calls to this_program()
in the same transaction might have different results.

I have this giant Pl/PgSQL function that runs reports every
night. It goes through each schema (program)
calling that schema's report function, etc, etc.

>>It was a while ago, but I believe this is because I have
>>a this_program() defined in each schema and they
>>return a different value depending on which schema
>>you are in when you call the function...
>
>But each one of these is individually immutable, no? If it's not,
>how are you expecting your query-builder function to give the same
>answers as before?
>

So I guess I lied... :o(

It is immutable here where I am working now, but I am trying to
use the same function that needs to be mutable someplace
else... So either I need to just define a totally separate
this_program_immutable() function or go with my Pl/PgSQL
wrapper -- which is already written and working well.
So I guess that is that :o)

Thank you!

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963