Re: Views- Advantages and Disadvantages

On Thursday 10. May 2007 19:23, Tom Lane wrote:

>"Leif B. Kristensen" <leif(at)solumslekt(dot)org> writes:

>> CREATE OR REPLACE VIEW tmg_persons AS
>> SELECT
>> person_id,
>> get_parent(person_id,1) AS father_id,
>> get_parent(person_id,2) AS mother_id,
>> last_edit,
>> get_pbdate(person_id) AS pb_date,
>> get_pddate(person_id) AS pd_date,
>> gender AS s,
>> living AS l,
>> is_public AS p
>> FROM persons;
>
>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. If they are really stable or immutable, marking them so
> would probably help here. (If they fetch from another table, stable
> is the right marking.)

Tom,
I haven't pondered the subtleties of 'stable', 'immutable' or 'volatile'
yet, but rather reckoned that the default would do. 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;

'relations', 'events', and 'participants' are actual tables. So, what do
you recommend?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/