Postgres Pro
Downloads
Home   >   mailing lists

Thread: function calls in WHERE clause

function calls in WHERE clause

From
"Lee Harr"
Date:
I have a view like this:

CREATE or replace VIEW program_member_this_year AS
    SELECT DISTINCT person.person_id
        FROM unit.person, unit.visit
        WHERE
            unit.person.person_id = unit.visit.person_id
            AND
            visit.program_id = this_program()
            AND
            visit.time_stamp > shared.last_september();



and I would like to speed it up ...

I notice that if I write it like this:

CREATE or replace VIEW program_member_this_year AS
    SELECT DISTINCT person.person_id
        FROM unit.person, unit.visit
        WHERE
            unit.person.person_id = unit.visit.person_id
            AND
            visit.program_id = 3
            AND
            visit.time_stamp > '2004-9-1';

It takes about 1/4 the time as with the function calls.

Both of those function calls will always return the exact
same thing for any given query.

My approach now is to make a Pl/PgSQL function which
will build up the correct query with the function calls
replaced by their constant results .... but is there a
better way?

I should note that this_program() is defined as VOLATILE.

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...

Thanks for any insight.

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus

Re: function calls in WHERE clause

From
Tom Lane
Date:
"Lee Harr" <missive@hotmail.com> writes:
> I should note that this_program() is defined as VOLATILE.

So don't do that ;-)

> 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?

            regards, tom lane

Re: function calls in WHERE clause

From
"Lee Harr"
Date:
On 2004-09-08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>"Lee Harr" <missive@hotmail.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

Re: function calls in WHERE clause

From
"Lee Harr"
Date:
>... 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)
>


Installed my plpgsql wrapped view today and the query
that was taking 4 seconds is down to 100msec. What
a nice surprise! Really made my day...

I cannot say enough how much I appreciate all the hard
work that goes in to postgresql. This place is the best.

_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus