[SQL] Query is fast and function is slow

[Richard Ray]

The query

select count(*) from documents where doc_num = '106973821'  and (select 
bit_or(group_access) from mda_groups where group_name in (select groname 
from pg_user,pg_group where usename =  'bbob'  and usesysid = any(grolist) 
and (groname ~ '.*owner$' or groname = 'admin'))) & access > 
'0'::bit(100);

returns very fast

If I create function

create or replace function check_for_update_permission(text,text) returns 
boolean as '
declare
  doc_number alias for $1;
  user alias for $2;
  doc_count integer;
begin

  select count(*) into doc_count from documents where doc_num = doc_number 
and (select bit_or(group_access) from mda_groups where group_name in 
(select groname from pg_user,pg_group where usename = user and usesysid = 
any(grolist) and (groname ~ ''.*owner$'' or groname = ''admin''))) & 
access > ''0''::bit(100);

  if doc_count > 0 then
    return(true);
  end if;

  return(false);

end;
' language 'plpgsql';


and run "select check_for_update_permission('106973821','bbob');"
it returns the correct info but takes several minutes
Would someone please enlighten me.
Can you do something like explain analyze on a function

Thanks
Richard


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster