#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12

    problems getting a trigger function to run properly


    It's an old version, 6.5.1...
    I've tried making a trigger and a PL/pgSQL stored procedure/function to run when it fires. When I do a statement that would make it fire, it returns this error:

    wpd_jregan=> INSERT INTO mediabarcodes (bar_code, employee_id, creation_date, media_size, media_type, sheets_per_media_cassette, target_printer, allocated, on_cassette_in_shipping_carton, valid_encoding) VALUES ('23516273648756489023', 999, '07/24/03', 1, 1, 40, 1, 'f', 'f', 'l');
    ERROR: stat failed on file '/usr/local/pgsql/lib/pl
    sql.so': No such file or directory


    Which looks to me like it has something to do with the way the database is set to look up running the backend for stored functions... I don't really know. I will be talking with the DBA but he may not have any ideas either. Figured I'd throw it out there.

    Here is the text of the procedure:

    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';
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    here is the trigger:

    CREATE TRIGGER mbc_insert_trigger
    BEFORE INSERT
    ON mediabarcodes
    FOR EACH ROW
    EXECUTE PROCEDURE mbc_insert_function();

    to continue with what I think might be possible issues, the way you write and open/fetch/close the reference cursors seems wrong to me, but I can't find true examples of the way I am doing it in the 6.5 or the most recent (I guess 7.3) documentation.

    Any ideas? your help would be greatly appreciated!

    j
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    ok!! so we figured it out, here's the solution.

    When I created the procedural language, the function handler was pointing to the wrong plpgsql.so file.

    I had to change it to:

    CREATE FUNCTION plpgsql_call_handler()
    RETURNS OPAQUE AS '/opt/postgresq-6.5.1/lib/plpgsql.so'
    LANGUAGE 'C';

    This is different than it would be for 7.2.2. or near there version-wise.. Just make sure you have the right location of this file!

IMN logo majestic logo threadwatch logo seochat tools logo