Using a PL/SQL function to restrict a SELECT

Hi, I implemented a PL/SQL function to report the access status of a photo given the photo ID and an ACS user_id. I keep getting "invalid relational operator" errors on the SELECT. Here's the SELECT:

set selection [ns_db 1row $db \
"select count(*)
 from family_photo_relative_map
 where relative_id = $relative_id
 and family_photo_access_check(family_photo_id, $acs_user_id)"]

and here's the PL/SQL function, it returns TRUE if access is granted, FALSE otherwise.

create or replace function family_photo_access_check 
  (v_photo_id family_photos.family_photo_id%TYPE, v_user_id 
users.user_id%TYPE)
return BOOLEAN
is
  v_photo_count integer;
  v_access_type varchar(20);
BEGIN
  select access_control into v_access_type
    from family_photos
    where family_photo_id = v_photo_id;
  if v_photo_id = 'public' then
    return TRUE;
  elsif v_photo_id = 'family' then
    if v_user_id > 0 then
      return TRUE;
    else
      return FALSE;
    end if;
  else
    select count(*) into v_photo_count
      from family_photo_access_control
      where family_photo_id = v_photo_id
      and user_id = v_user_id;
    if v_photo_count != 0 then
      return TRUE;
    else
      return FALSE;
    end if;
  end if;
END family_photo_access_check;

-- Matthew Lee, October 11, 1999

Answers

Never mind...

I implemented a nastier, less elegant method of doing access control using just plain old queries and subqueries. It works... I'm happy.

I would have liked for this PL/SQL thing to have worked though...

-- Matthew Lee, October 12, 1999

The prob here is that you need to use a relational operator for the where clause. What does this mean? Include a comparision against the return value of your PL/SQL function, like

... WHERE can_access_p(v_user_id) = 't'

for example.

-- Ben Walter, October 15, 1999