#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0

    Data calculation


    I need some help for which I could not find much help for within the Postgres book I am looking at, or the online tutorial. I don't know if this is even possible, but here goes...

    Scenario: Grading results. I have two tables set up - one with the answerkey and one with the students results from a test:

    answerkey:

    question_number int,
    answer varchar(2)

    So something like this (select * from answerkey):

    1 | 2 | 3 ...... | 30
    ------------------
    A | B | C..... | D

    Student results are similar as the answerkey table, but contain a unique student id:

    student_id | 1 | 2 | 3 .....
    -------------------------
    010019 | B | C | C ....
    029393 | B | B | C.....

    Currently, to calculate how each student did, within Perl, I obtain the table results in an array and do an array calculation:

    if ($student_answer->[$i] eq $correct_answer[$i-1]){$answer_correct++;}

    This works fine and is quite fast, though could postgres be used to do this faster?

    The reason being is that once I have the number of correct answers for a student, I then calculate the % .

    Now, all this data that Perl calculates is displayed for the end user in a table. The user can also sort by a field, such as %.

    Because Perl did the % calculations, I have to re-calculate everything and dump it into a temporary table, to make sorting easier:

    student_id, answer_correct, weights_score, percentage_score

    Then, if the user wants to sort by the percentage field, I do a select * from temp_answers order by $field.

    This works fine, and of a class with 500 students, all this happens in about 10 seconds.

    I am new to the Postgres world, and am just wondering: is there any way I can make Postgres do all the calculations, therefore, bypassing the need to create a temporary table upon every lookup?

    Thank you

    Ogden
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    France
    Posts
    55
    Rep Power
    12

    Re: Data calculation


    Hello,
    Originally posted by onefix

    answerkey:
    question_number int,
    answer varchar(2)
    So something like this (select * from answerkey):

    1 | 2 | 3 ...... | 30
    ------------------
    A | B | C..... | D
    You say your table is the following :
    (it is how it sould be)
    Code:
    answerkey (question_number int, answer varchar(2))
    But when you do a select * from answer key you get :
    Code:
    1 | 2 | 3 ...... | 30
    ------------------
    A | B | C.....  | D
    It means your table has the following structure :
    Code:
    answerkey (1 varchar, 2 varchar, 3 varchar, ..., 30 varchar)
    Can you clarify this please?
    Same remark for the student_answer table, is it (it is how it sould be)
    Code:
    student_answer(student_id int, question_number int, answer varchar(2))
    or
    Code:
    student_answer(student_id int, 1 varchar(2), 2 varchar(2) , ....)
    ?
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0
    I'm sorry about the confusion.

    The data types of the answers are of varchar, as the answers are in the form of "A", "B", "C"..., or combinations of them.

    The "1", "2", "3", were the column headers and are the question numbers.

    So they are:

    student_answer(student_id int, question_number int, answer varchar(2))

    and

    answerkey (1 varchar, 2 varchar, 3 varchar, ..., 30 varchar)

    Thank you very much.

    Ogden
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    I think there's a command called createlang or from psql you can do CREATE LANGUAGE plpgsql USING HANDLER plpgsql_handler() which is the interpreter program for plpgsql functions. You would set up a trigger on the students' table for when you insert the values (put them in a comma-delineated file and parse w/ perl into the insert statements, but I think you already have that part) and triggers for postgres with plpgsql functions have to return OPAQUE or something, I think it depends on the version of postgres you are using. For me in 7.2.4 they return OPAQUE then in the trigger you just say USING this_plpgsql_function () and it will run that for you ON INSERT if you so specify in the TRIGGER.

    then in the plpgsql function open up the results table and fetch the row out, then compare across them counting with a local plpgsql variable that you declared in the DECLARE section and then get your AVERAGE and INSERT IT into the grading field. Then when you run the inserts it will calculate based on the NEW.row that you inserted.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    here is an example of a plpgsql function for inserts that I wrote to help you figure out some of the syntax b/c the documentation isn't really great:

    CREATE FUNCTION mbc_insert_function () RETURNS OPAQUE AS '
    DECLARE
    --cursors declaration section
    barcode_refcursor refcursor;
    combinations_refcursor refcursor;
    get_current_key CURSOR FOR SELECT MAX(combination_key) FROM mediacombinations;
    mbc_count_cursor CURSOR FOR SELECT count(*) FROM mediabarcodes;

    invalid_counts_cursor CURSOR FOR --cursor def below
    SELECT bar_code, media_size, media_type, sheets_per_media_cassette, allocated
    FROM mediabarcodes
    ORDER BY media_size, media_type, sheets_per_media_cassette, allocated, bar_code;
    --regular variables declaration section
    bc_sanity_check CHARACTER(20);--if inserted barcode is already allocated...
    combinations_match INT4;
    next_combination_key INT4;
    BEGIN
    OPEN barcode_refcursor FOR SELECT allocated
    FROM mediabarcodes
    WHERE bar_code = NEW.bar_code;
    FETCH barcode_refcursor INTO bc_sanity_check;
    CLOSE barcode_refcursor;

    OPEN combinations_refcursor FOR SELECT combination_key
    FROM mediacombinations
    WHERE media_size = NEW.media_size
    AND media_type = NEW.media_type
    AND media_orientation = NEW.media_orientation
    AND sheets_per_media_cassette = NEW.sheets_per_media_cassette
    AND target_printer = NEW.target_printer;

    FETCH combinations_refcursor INTO combinations_match;
    IF combinations_match IS NOT NULL THEN
    --get the current count and fetch it into incremented_count
    OPEN mbc_count_cursor;
    FETCH mbc_count_cursor INTO incremented_count;
    incremented_count:=incremented_count+1;
    --SHOULD BE AN UPDATE
    --INSERT INTO mediacombinations ( total_count ) VALUES ( incremented_count );
    ELSE
    --the combination was not found, it is an unknown combination
    --insert into the mediacombinations table with legal_flag = 'f' and count = 1;
    --get current highest combination_key
    OPEN get_current_key;
    FETCH get_current_key INTO new_combination_key;
    CLOSE get_current_key;
    new_combination_key:=new_combination_key+1;
    INSERT INTO mediacombinations (
    combination_key,
    media_size,
    media_type,
    media_orientation,
    sheets_per_media_cassette,
    target_printer,
    total_count,
    legal_flag
    )
    VALUES (
    new_combination_key,
    NEW.media_size,
    NEW.media_type,
    NEW.media_orientation,
    NEW.sheets_per_media_cassette,
    NEW.target_printer,
    1,
    ''f''
    );
    --DBA can change this legal_flag at a later date

    END IF;
    CLOSE combinations_refcursor;
    END;
    ' LANGUAGE 'plpgsql';
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    France
    Posts
    55
    Rep Power
    12
    So they are:
    Code:
    student_answer(student_id int, question_number int, answer varchar(2))
    seems good
    and
    Code:
    answerkey (1 varchar, 2 varchar, 3 varchar, ..., 30 varchar)
    why not rather :
    Code:
    answerkey (question_number int, answer varchar(2))
    This way the number of answer can vary... And you can get the number of right answer per student directly with the following query :
    Code:
    select
     student_id, count(*) as nb_good
    FROM
     student_answer
      INNER JOIN
     answer_key USING ( question_number, answer)
    GROUP BY
     student_id;
    Thank you very much.
    Ogden
    de rien...
    Last edited by niico.c; October 1st, 2003 at 02:42 PM.
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0
    Thank you everyone for helping me on this. You see, I was kind of thrown into this project not knowing much about PLPGSQL and I don't even know where to start. The documentation out there is not helpful for newbies on this topic.

    Basically, how does one even get a PLPGSQL script going? I know Perl quite well, but how do I create these functions and run them and store them in Postgres?

    Here is a scenario that I would like advice on, and I would be grateful if anyone could guide me through this:

    The school database includes details on a student (for example, their race, their gender, etc).

    Now if I want to run a report on all Female students who did a test, I check the student id from the test answerkey_table (which has a student_id) and then check it against the main student database:

    SELECT s.student_id, t.answers FROM classanswers_3 t, student_table s WHERE (s.student_id = t.student_id) AND (s.gender='F')

    So that works fine.

    But here is the problem...After the user selected the options (ie. "Females") they are then taken to a different page with the reporting options (ie. they can see an alphabetical list of students, item analysis of those group of students, and what the students answered).

    My question is: how do I pass the matching students to those other pages?

    This is how I do it:

    1. Find the students which match their criteria - so, run a query like I did above to find students who are female, and/or Asian, for example.
    2. Collect the student_ids from Postgres and dump the student_id's which match into a unique table. This is a table that is created and the name is created using Perl's time() function.
    3. Then pass the newly created tables to a main menu, which lists the different reports one can see: student_answers.pl?table_name=334334333 ..

    So when they open up that link, the student_answers.pl script reads the table_name and then it knows which student_ids it needs to look up.

    This works, is not that slow, but if they're running a LOT of reports, it will clutter up the database in no time at all. I think my design is horrible too. Again, I'm a newbie at all this Postgres connectivity stuff.

    Basically, how do I pass SQL queries from one Perl CGI script to another.

    Thank you very much for any insight.

    Ogden
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    installing one of the procedural languages differs by what version of postgres you are using, but you can do it for every version from the psql prompt. When you enter psql for your given database:

    ****** from
    the 7.2.4 documentation

    Example 22-1. Manual Installation of PL/pgSQL

    The following command tells the database server where to find the shared object for the PL/pgSQL language's call handler function.

    CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
    '$libdir/plpgsql' LANGUAGE C;

    The command

    CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
    HANDLER plpgsql_call_handler;
    then defines that the previously declared call handler function should be invoked for functions and trigger procedures where the language attribute is plpgsql.

    *****************

    to select just the females' student_id's you could do

    SELECT student_id
    FROM students_table
    WHERE student_id IN (
    SELECT student_id
    FROM students_table
    WHERE gender = 'f'
    )

    and then you've got your result set of females' id's

    you're using perl for the cgi so I guess you have to store it in a perl variable. For CGI you don't really have to go to a "new page" do you, to display the alphabetical list and whatnot? just set a flag that says this_area_submitted = 1, then when it is later on, you'll know to run the queries and sort the results for alphabetizing. I don't know if there are cursor variable types in perl that will hold your result set to check against but you can just build that data structure in perl with an array of their student_id's then when you do the selects, you'll get all of their names and other information, then sort the result array in perl. I think by default the sort function will do utf8ically sorted strings
    =-)


    U shouldn't have to pass queries or the results of queries from one program to another, just do them when they are necessary. Creating new tables every transaction or session is, you're right, probably a good indication that you can do things another way. I am not familiar with the other procedural languages (planning soon, whenever I have more time) like the plperl and plpython that are available with the db software, so actually you may want to look into plperl to see if you can still use cgi modules within the program and then you're set, because then you should also have the built-in datatypes for records, cursors, and results sets, etc.

    here 's the info on the plperl language for 7.2

IMN logo majestic logo threadwatch logo seochat tools logo