Forum OpenACS Development: Response to How do I get the creation_user from acs

Dan, I tried using the cast and I get some really strange behavior. If I run one line of the query in psql there are no problems:

select person__name(acs_object__get_attribute(r.response_id,'creation_user')::text::integer) as respondent_name
from
  survsimp_responses r,
  survsimp_question_responses qr
where
  qr.response_id = r.response_id
  and qr.question_id = '2216'

The only things that show up are the following:

NOTICE:  identifier "acs_object__get_attribute_storage" will be truncated to "acs_object__get_attribute_stora"
NOTICE:  identifier "acs_object__get_attr_storage_column" will be truncated to "acs_object__get_attr_storage_co"
NOTICE:  identifier "acs_object__get_attr_storage_table" will be truncated to "acs_object__get_attr_storage_ta"
NOTICE:  identifier "acs_object__get_attr_storage_sql" will be truncated to "acs_object__get_attr_storage_sq"

Now if I do it again for creation_date right after doing the query for creation_user, it will bomb and say "ERROR: type of v_rec.return doesn't match that when preparing the plan"

select acs_object__get_attribute(r.response_id,'creation_date') as submission_date
from
  survsimp_responses r,
  survsimp_question_responses qr
where
  qr.response_id = r.response_id
  and qr.question_id = '2216'

If I exit psql and restart it and do the same queries again, the same problem comes up (the second call of the function on a different object attribute fails). If I reverse the order of the queries, the second query still fails. If I do the full query (see code below) it also fails! It looks like I can run the acs_objects__get_attribute function once per query (maybe per session). The first call of the function always works, but the rest of the calls fail. However, I can repeat the first query indefinitely and it will always work.

After you updated the acs_object__get_attribute function, I dropped it and reloaded the new function. I even dropped the whole database and reloaded it. Do you have any idea what is going on? I hope my explaination was clear.

[04/Jul/2001:23:15:23][723.4101][-conn0-] Notice: Querying '

select
  varchar_answer as response,
  person__name(acs_object__get_attribute(r.response_id,'creation_user')::text::integer) as respondent_name,
  acs_object__get_attribute(r.response_id,'creation_date') as submission_date,
  acs_object__get_attribute(r.response_id,'creation_ip') as ip_address
from
  survsimp_responses r,
  survsimp_question_responses qr
where
  qr.response_id = r.response_id
  and qr.question_id = '2216'
  order by submission_date;'
[04/Jul/2001:23:15:23][723.4101][-conn0-] Error: Ns_PgExec: result status: 7 mes
sage: ERROR:  type of v_rec.return doesn't match that when preparing the plan

[04/Jul/2001:23:15:23][723.4101][-conn0-] Error: dbinit: error(localhost::openacs4,ERROR:  type of v_rec.return doesn't match that when preparing the plan
): '
select
  varchar_answer as response, person__name(acs_object__get_attribute(r.response_id,'creation_user')::text::integer) as respondent_name,
  acs_object__get_attribute(r.response_id,'creation_date') as submission_date,
  acs_object__get_attribute(r.response_id,'creation_ip') as ip_address
from
  survsimp_responses r,
  survsimp_question_responses qr
where
  qr.response_id = r.response_id
  and qr.question_id = '2216'
  order by submission_date

      '
[04/Jul/2001:23:15:23][723.4101][-conn0-] Notice: RP (109.983 ms): error in rp_handler: serving GET /surveys/admin/view-text-responsesquestion_id=2216
        ad_url "/surveys/admin/view-text-responses" maps to file "/web/openacs-4/packages/simple-survey/www/admin/view-text-responses.tcl"
errmsg is Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
[04/Jul/2001:23:15:23][723.4101][-conn0-] Warning: APM: RestrictErrorsToAdminsP does not exist
[04/Jul/2001:23:15:23][723.4101][-conn0-] Warning: APM: AutomaticErrorReportingP does not exist
[04/Jul/2001:23:15:23][723.4101][-conn0-] Warning: APM: EnabledP does not exist
[04/Jul/2001:23:15:23][723.4101][-conn0-] Error: GET /surveys/admin/view-text-responses question_id=2216 Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind select nsdb0 {
...

Forum OpenACS Development: Response to How do I get the creation_user from acs_objects?