"Leif B. Kristensen" <leif@solumslekt.org> writes:
> On Thursday 10. May 2007 19:23, Tom Lane wrote:
>> Are get_parent() and/or get_pbdate() marked volatile by any chance?
>> 8.2 is more conservative about optimizing sub-selects involving
>> volatile functions than previous releases were, because we got
>> complaints about surprising behavior when a volatile function is
>> executed more or fewer times than the text of the query would
>> suggest.
> I haven't pondered the subtleties of 'stable', 'immutable' or 'volatile'
> yet, but rather reckoned that the default would do.
Yeah, I was against this particular change actually, because I expected
that it would cause more problems for people who hadn't paid close
attention to this point than it'd fix for those trying to do cute things.
> Here are the function definitions:
> CREATE OR REPLACE FUNCTION get_parent(INTEGER,INTEGER) RETURNS INTEGER
> AS $$
> DECLARE
> person ALIAS FOR $1; -- person ID
> rel_type ALIAS FOR $2; -- gender code (1=male, 2=female)
> par INTEGER; -- person ID of parent, returned by func
> BEGIN
> SELECT parent_fk INTO par FROM relations
> WHERE child_fk = person AND relation_type = rel_type;
> RETURN COALESCE(par,0); -- will return parent ID if it exists, 0
> otherwise
> END;
> $$ LANGUAGE plpgsql;
> CREATE OR REPLACE FUNCTION get_pbdate(INTEGER) RETURNS TEXT AS $$
> DECLARE
> pb_date TEXT;
> BEGIN
> SELECT event_date INTO pb_date FROM events, participants
> WHERE events.event_id = participants.event_fk
> AND participants.person_fk = $1
> AND events.tag_fk IN (2,62,1035)
> AND participants.is_principal IS TRUE;
> RETURN COALESCE(pb_date,'000000003000000001');
> END;
> $$ LANGUAGE plpgsql;
AFAICS you ought to mark both of those STABLE, since they use but don't
change database data.
regards, tom lane