function calls in WHERE clause

[Lee Harr]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

"Lee Harr" <mi*****@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org