Forum OpenACS Q&A: Response to Demograpics - plsql (user_demographic

I came across a problem with both user_demographic_summary and user_contact_summary just last night. Therefore, I decided to hunt down and fix the bug in user_demographic_summary:

Here's the fix for user_demographic_summary:

drop function user_demographics_summary (integer);
create function user_demographics_summary (integer)
returns text
as '
declare
  demo_row              users_demographics%ROWTYPE;
  age                   integer;
  pretty_sex            varchar(20);
begin
  select * into demo_row from users_demographics where user_id = $1;

  --the original formula wasn't working correctly, so I fixed it
  age := round ( (date_part(''years'', current_timestamp)-
                  date_part(''years'', demo_row.birthdate)) +
                 (date_part(''months'', current_timestamp)/12.0-
                  date_part(''months'', demo_row.birthdate)/12.0) );

--It would appear that 'ELSIF' is incorrect syntax
--Therefore, you need to use 'ELSE IF' instead
--The extra 'ELSE IF' necessitates an extra 'END IF;' at the end

  IF demo_row.sex = ''m'' THEN
    pretty_sex := ''man'';
  ELSE
  IF demo_row.sex = ''f'' THEN
    pretty_sex := ''woman'';
  END IF;
  END IF;
  IF pretty_sex is null and age is null THEN
    return null;
  END IF;
  IF pretty_sex is not null and age is null THEN
    return ''a '' || pretty_sex;
  END IF;
  IF pretty_sex is null and age is not null THEN
    return ''a '' || age || ''-year-old person of unknown sex'';
  ELSE
    return ''a '' || age || ''-year-old '' || pretty_sex;
  END IF;
end;' language 'plpgsql';

As for the problem with user_contact_summary, it involves an issue with varchar and bpchar in postgresql that apparently necessitates some sort of explicit "casting", at least under certain conditions, when you are using the || concatenator (at least within a function).

I'm going to continue to research that problem and I'll probably post something once I've figured out a fix.

Forum OpenACS Q&A: Response to Demograpics - plsql (user_demographic_summary)