Re: Question on COUNT performance

In retrospect, it's a big assumption whether f_project_acl() or
f_customer_acl() always return TRUE. If they can return FALSE, you
probably want to replace the statements inside the FOR..LOOP with


>                IF plan_record.project_id IS NOT NULL THEN
>                        IF f_project_acl(uid, plan_record.project_id) THEN i := i + 1; END IF;
>                ELSEIF plan_record.customer_id IS NOT NULL THEN
>                        IF f_customer_acl(uid, plan_record.customer_id) THEN i := i + 1; END IF;
>                ELSE
>                        i := i + 1;
>                END IF;

This would mimic the results of your original query, although I must
confess I don't understand the usefulness of the count results, as a
number less that the number of rows in plan_events has an ambiguous
meaning. Either

(1) there is a matching event but f_project_acl returned FALSE

OR

(2) there is no matching event, there IS a matching customer, but
f_customer_acl returned FALSE

And of course you don't know which plan_ids these might be true of.

--Lee

2010/7/14 Lee Hachadoorian <lee.hachadoorian@gmail.com>:
> SELECT newfunc(uid);
>
> CREATE FUNCTION newfunc(uid int) RETURNS int AS $$
> DECLARE
>        plan_record record;
>        i int := 0;
> BEGIN
>        FOR plan_record IN SELECT DISTINCT plan_id, project_id, customer_id
> FROM plan_events LEFT JOIN project_plan_events USING (plan_id) LEFT
> JOIN customer_plan_events USING (plan_id)  LOOP
>                IF plan_record.project_id IS NOT NULL THEN
>                        PERFORM f_project_acl(uid, plan_record.project_id);
>                ELSEIF plan_record.customer_id IS NOT NULL THEN
>                        PERFORM f_customer_acl(uid, plan_record.customer_id);
>                END IF;
>                i := i + 1;
>        END LOOP ;
>        RETURN i;
> END;
> $$ LANGUAGE plpgsql;