July 24th, 2003, 02:46 PM
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 '
--cursors declaration section
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
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...
OPEN barcode_refcursor FOR SELECT allocated
WHERE bar_code = NEW.bar_code;
FETCH barcode_refcursor INTO bc_sanity_check;
OPEN combinations_refcursor FOR SELECT combination_key
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
FETCH mbc_count_cursor INTO incremented_count;
--SHOULD BE AN UPDATE
--INSERT INTO mediacombinations ( total_count ) VALUES ( incremented_count );
--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
FETCH get_current_key INTO new_combination_key;
INSERT INTO mediacombinations (
--DBA can change this legal_flag at a later date
' LANGUAGE 'plpgsql';
July 24th, 2003, 02:47 PM
here is the trigger:
CREATE TRIGGER mbc_insert_trigger
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!
July 25th, 2003, 11:06 AM
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'
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!