November 10th, 2003, 08:42 AM
pl/pgsql: Columns recognition
I need to validate data being inserting into my database. The only I have to do is to check every field in a new record and if it is null, insert record into table with errors.
My problem is that I have to do it for several tables and I would like to prepare only one function and use it in different triggers, but i don't know how to make this function to check every field in given record independently on a number of these fields, and their names.
I would appreciate, If you have any ideas or can direct me to proper source.
November 11th, 2003, 04:29 PM
write one plpgsql function that is the multiple-use validation function
CREATE OR REPLACE FUNCTION validator_func (field INPUT_VAR) (this here is some pseudocode but you need to look it up in the manual to see how to do that correctly) AS bool DECLARE the_string VARCHAR(10000)
the_string = field
IF field = "" or field is null or (other predicates)
THEN return false;
ELSE return true;
and another function that is the more-encompassing trigger function. Within this trigger function you will call the validator_func function with
SELECT validator_func() AS result;
IF result == true THEN
does this help at all?
plpgsql manual page
November 12th, 2003, 02:10 AM
Thank you for your help.
But I don't quite understand how to use your solution.
I assume that, when a trigger(insert or update) is launched I have rekord NEW with some structure which is the same as the structure of a table for which the trigger was launched. As I wuold like to use the same function for different tables I need some method to recognize this structure inside my function. Are there any internal variables or special functions that let me get this structure. Or some construction like: for each field in record.
November 12th, 2003, 12:43 PM
you can have a rowtype, which could be fetched into from a cursor:
or, some code from one that I've written that does that:
The thing that I think you are having trouble with is that the reusable function should be GENERIC and based on a field as a string, and not as a particular field from a particular record. The trigger function itself may contain this data type, however. You will need to know the names of columns to fetch from a rowtype, it is built as an associative array.
retreive_max_key CURSOR FOR SELECT MAX(combination_key) FROM mediacombinations;
step_mediabarcodes CURSOR FOR SELECT * FROM mediabarcodes;
FOR barcodes_row IN SELECT * FROM mediabarcodes LOOP
OPEN find_match_in_mediacombinations FOR SELECT combination_key FROM mediacombinations WHERE media_size = barcodes_row.media_size AND media_type = barcodes_row.media_type AND media_orientation = barcodes_row.media_orientation AND sheets_per_media_cassette = barcodes_row.sheets_per_media_cassette AND target_printer = barcodes_row.target_printer;
FETCH find_match_in_mediacombinations INTO v_combination_key;
IF v_combination_key IS NULL THEN
FETCH retreive_max_key INTO new_max_key;
INSERT INTO mediacombinations (combination_key, media_size, media_type, media_orientation, sheets_per_media_cassette, target_printer, total_count, legal_flag) VALUES (new_max_key, barcodes_row.media_size, barcodes_row.media_type, barcodes_row.media_orientation, barcodes_row.sheets_per_media_cassette, barcodes_row.target_printer, 1, ''f'');
OPEN retreive_current_count FOR SELECT total_count FROM mediacombinations WHERE combination_key = v_combination_key;
FETCH retreive_current_count INTO new_total_count;
UPDATE mediacombinations SET total_count = new_total_count WHERE combination_key = v_combination_key;