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

How do I get the creation_user from acs_objects? Should I just do a join against the acs_objects table? I tried using acs_object__get_attribute but that returns a varchar and I need it as an integer. Is there a way to do a type conversion? I want to write some sql code like this:

select ...
person__name(acs_object__get_attribute
(r.response_id, 'creation_user')) as respondant_name
...

Is there a simple way to convert varchar to integer?

In postgresql, you can cast a result as follows:

select ...
person__name(acs_object__get_attribute(r.response_id, 'creation_user')::integer) as respondent_name
...

Thanks Dan!

acs_object__get_attribute can't retrieve timestamps. Is there a function that will return the creation_date from acs_objects? Should I do a join against the acs_objects table or write a function to grab the timestamp? Thanks.

The error I get when using acs_object__get_attribute on creation_date:

ERROR:  type of v_rec.return doesn't match that when preparing the plan
I just fixed this in cvs so that acs_object__get_attribute will work correctly with timestamps. You just need to add a cast to varchar on the selected value:


create function acs_object__get_attribute (integer,varchar)
returns varchar as '
declare
  object_id_in           alias for $1;  
  attribute_name_in      alias for $2;  
  v_table_name           varchar(200);  
  v_column               varchar(200);  
  v_key_sql              text; 
  v_return               text; 
  v_storage              text;
  v_rec                  record;
begin

   v_storage := acs_object__get_attribute_storage(object_id_in, attribute_name_in);

   v_column     := acs_object__get_attr_storage_column(v_storage);
   v_table_name := acs_object__get_attr_storage_table(v_storage);
   v_key_sql    := acs_object__get_attr_storage_sql(v_storage);

   for v_rec in execute ''select '' || quote_ident(v_column) || ''::varchar as return from '' || quote_ident(v_table_name) || '' where '' || v_key_sql
      LOOP
        v_return := v_rec.return;
        exit;
   end loop;
   if not FOUND then 
       return null;
   end if;

   return v_return;

end;' language 'plpgsql';

Collapse
Posted by Luke Pond on
When I try following the above advice on casting to integer, postgresql tells me

[04/Jul/2001:13:53:25][1374.4101][-conn0-] Error: Ns_PgExec: result status: 7 message: ERROR:  Cannot cast type 'varchar' to 'int4'

Is there any way to work around this?

Yeah, that didn't work for me either.  I ended up doing a join against the acs_objects table.
Sorry about that. The correct coercion path is text => integer:


openacs4=# select '4'::varchar::integer;
ERROR:  Cannot cast type 'varchar' to 'int4'
openacs4=# select '4'::text::integer;
 ?column?
----------
        4
(1 row)                                                                                                               

What version are you guys using? I tested the above fix, so I assumed that it worked ok.

In general, you should avoid joins with acs_objects due to performance considerations.

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 {
...
Ok try changing acs_object__get_attribute to the following:

create function acs_object__get_attribute (integer,varchar)
returns text as '
declare
  object_id_in           alias for $1;  
  attribute_name_in      alias for $2;  
  v_table_name           varchar(200);  
  v_column               varchar(200);  
  v_key_sql              text; 
  v_return               text; 
  v_storage              text;
  v_rec                  record;
begin

   v_storage := acs_object__get_attribute_storage(object_id_in, attribute_name_in);

   v_column     := acs_object__get_attr_storage_column(v_storage);
   v_table_name := acs_object__get_attr_storage_table(v_storage);
   v_key_sql    := acs_object__get_attr_storage_sql(v_storage);

   for v_rec in execute ''select '' || quote_ident(v_column) || ''::text as return from '' || quote_ident(v_table_name) || '' where '' || v_key_sql
      LOOP
        v_return := v_rec.return;
        exit;
   end loop;
   if not FOUND then 
       return null;
   end if;

   return v_return;

end;' language 'plpgsql';

I was able to recreate your error, and I then changed it to this form and it worked fine. Let me know how it goes.

Hi Dan,

Thanks!  That did the trick.  Did you update the function in the cvs repository?  I don't see the changes in packages/acs-kernel/sql/postgresql/acs-object-create.sql.

No, I don't have access to cvs right now, so I'll do it tonight when I'm at home.