Forum OpenACS Q&A: user_contact_summary fix (followup to user_demographic_summary)

The user_demographic_summary posting, which also featured a fix of mine, was the "inspiration" for this fix. (Thanks to all who contributed!)

First, I should definitely mention that my fix posted for user_demographic_summary needs to have all the "comment lines" (any lines beginning with "--") stripped, or it won't work, as I discovered a little while ago.

Next, there are a couple of things I discovered when I started to troubleshoot user_contact_summary:

1. Since my ha_line2 (or, equivalently, wa_line2) field was null, when I tried concatenating just the first two items, the ha_line1 field suddenly "disappeared"! That's why it was necessary to add the "case" stuff to ha_line2 and wa_line2. (And it would actually be necessary to add them to any of the other fields, as well, unless you make the assumption, as I did, that if any of the other fields are populated, then all the rest of them are, and, if not, you don't really care, because all you'd get would be a partial address, which is kind of sloppy. If you do care, then the rest of the fix is left for you as an exercise. 😊
2. The error message I was getting:

   ERROR: Unable to identify an operator '||' for types 'varchar' and 'bpchar'
   You will have to retype this query using an explicit cast

   did not occur when I tried adding each field progressively to the concatenation -- until, that is, the very last field. Well, guess what? The last field is a char(2), so the error message appears to have been complaining about an incompatibility between the concatenated varchar's and the final char!!!

Anyhow, without further ado, here is the fix:

drop function user_contact_summary (integer);
create function user_contact_summary (integer)
returns varchar
as '
declare
  contact_row           users_contact%ROWTYPE;
  v_return              varchar(4000);
begin
  select * into contact_row from users_contact where user_id = $1;
 
  IF NOT FOUND then return '''';
  END IF;
  IF contact_row.m_address = ''w'' THEN
    v_return:= contact_row.wa_line1 || '' '' ||
      case when contact_row.wa_line2 is null then ''''
      else contact_row.wa_line2 end || '' '' || 
      contact_row.wa_city || '', '' ||
      contact_row.wa_state || '' '' ||
      contact_row.wa_postal_code || '' '' ||
      cast(contact_row.wa_country_code as varchar(2));
  ELSE
    v_return:= contact_row.ha_line1 || '' '' ||
      case when contact_row.ha_line2 is null then ''''
      else contact_row.ha_line2 end || '' '' || 
      contact_row.ha_city || '', '' ||
      contact_row.ha_state || '' '' ||
      contact_row.ha_postal_code || '' '' ||
      cast(contact_row.ha_country_code as varchar(2));
  END IF;
 
  return v_return;
end;
' language 'plpgsql';

Presumably this should make it possible for me to test the original code for view-verbose.tcl successfully now, since that was one of the modules that was broken because of user_contact_summary.