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

There could be a bug in the function user_demographic_summary as if
you insert demographic information into the users_demographics
manually you get an error on the /admin/users/one.tcl page telling
you "parse error at or near "elseif".

As I am not too familliar with pgsql, could somebody identify the
bug? I tried to drop the function with "drop function
user_demographics_summary", but it didn't work. I know that this can
be embarrassing, but well. What did I do wrong?

Collapse
Posted by Mike Slack on
I assume you are talking about either 'plpgsql' or psql.  'plpgsql' is
the name of the analogue of Oracle's pl/sql and psql is the analogue
of SQL*Plus.

In plpgsql, you can drop functions using the syntax:

drop function user_demographic_summary (<parameter types>)

where <parameter types> is a comma separated list of the data types of
the input parameters (if any), such as integer, string, etc.  You can
easily get these by looking at the file that defined the function, or
querying the data dictionary.

Collapse
Posted by Bob Fuller on
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.

Collapse
Posted by Don Baccus on
This second problem - casting - is probably brought about by changes
from PG 6.5 to PG 7.0.  Try casting the non-varchar like this:

"foo::varchar"

and see if that fixes the problem.

And, yes, the syntax for if doesn't include a shorthand "elseif" or
"elsif", you have to write them out as a string of nested "if then
else end if" statements in Pl/pgSQL.

I may have time to incorporate your first fix into CVS this weekend -
no promises, though!

Collapse
Posted by Bob Fuller on
No, the problem with user_contact_summary is not exactly as you describe.  Please see my new posting (which I'll post within the next hour), since I've got a fix for user_contact_summary (once I test and verify, of course).