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

    Join Date
    Nov 2003
    Posts
    2
    Rep Power
    0

    pl/pgsql: Columns recognition


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

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11
    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)
    BEGIN
    the_string = field
    IF field = "" or field is null or (other predicates)
    THEN return false;
    ELSE return true;
    END

    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
    continue;
    return true;
    ELSE
    return false;

    does this help at all?
    plpgsql manual page
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    2
    Rep Power
    0

    Question


    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11
    you can have a rowtype, which could be fetched into from a cursor:

    myrow tablename%ROWTYPE;

    or, some code from one that I've written that does that:

    Code:
    DECLARE
    		retreive_max_key			CURSOR FOR SELECT MAX(combination_key) FROM mediacombinations;
    		step_mediabarcodes 			CURSOR FOR SELECT * FROM mediabarcodes;
    		retreive_current_count			refcursor;
    		find_match_in_mediacombinations		refcursor;
    		barcodes_row 				mediabarcodes%ROWTYPE;
    		combinations_row			mediacombinations%ROWTYPE;
    		v_combination_key			INT4;
    		new_max_key				INT4;
    		new_total_count				INT4;
    		BEGIN
    		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;
    			CLOSE find_match_in_mediacombinations;
    			IF v_combination_key IS NULL THEN
    				OPEN retreive_max_key;
    				FETCH retreive_max_key INTO new_max_key;
    				new_max_key:=new_max_key+1;
    				CLOSE retreive_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'');
    			ELSE
    				OPEN retreive_current_count FOR SELECT total_count FROM mediacombinations WHERE combination_key = v_combination_key;
    				FETCH retreive_current_count INTO new_total_count;
    				new_total_count:=new_total_count+1;
    				CLOSE retreive_current_count;
    				UPDATE mediacombinations SET total_count = new_total_count WHERE combination_key = v_combination_key;
    			END IF;
    		END LOOP;
    	RETURN ''t'';
    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.

IMN logo majestic logo threadwatch logo seochat tools logo