Postgresql Trigger Example(Appending Dynamic Column Name):-

Postgresql Trigger Example(Appending Dynamic Column Name):-

            In this example there are two tables REGISTRATION_PREFERENCES & PATIENT_HEADER_PREFERENCES.
            In registration_preferences there are 28 columns. out of this 28 , 19 column names are custom_field1_label,custom_field2_label,....,custom_field19_label other 9 columns are also samething. It start with custom_list1_name,custom_list2_name ,.....,custom_list9_name.
           In patient_header_preferences there is only one column i.e field_desc. This column value can contains all column value of registration_preferences. Mean field_desc column having 28 rows.

Question:-    When you update the registration_preferences table then we should update the field_desc column of the patient_header_preferences table.

           In registration_preferences out 0f 28, 19 column names are same except 1 to 19. appending 1 to 19 in function using while loop. other 9 columns are also samething. It start with custom_list1_name,custom_list2_name ,.....,custom_list9_name. Here also appended 1 to 9 in function using another while loop.

   First create function modify_patient_header_pref that you can call it in trigger.

DROP FUNCTION IF EXISTS modify_patient_header_pref() CASCADE;
CREATE FUNCTION modify_patient_header_pref() RETURNS TRIGGER AS $$
DECLARE
    i integer;
    pcolName character varying;
    regFieldName character varying;
    regColName character varying;
    defaultValue character varying;
BEGIN
   i:=1;
   while(i<20)
    loop
         regColName := 'custom_field' || i::text || '_label';
         pcolName := 'custom_field'|| i::text;
         regFieldName := '(' || quote_literal(NEW) || '::' || TG_RELID::regclass || ').' || regColName;
         defaultValue := 'Custom Field ' || i::text;

          EXECUTE $SomeTag$UPDATE patient_header_preferences SET field_desc=CASE WHEN ($SomeTag$ || regFieldName || $SomeTag$ !='') THEN $SomeTag$ || regFieldName || $SomeTag$ ELSE '$SomeTag$|| defaultValue || $SomeTag$' END WHERE field_name = '$SomeTag$ || pcolName || $SomeTag$'$SomeTag$;
         i:=i+1;
   END loop;

   i := 1;
   while(i<10)
     loop
        regColName := 'custom_list' || i::text || '_name';
        pcolName := 'custom_list' || i::text ||'_value';
        regFieldName := '(' || quote_literal(NEW) || '::' || TG_RELID::regclass || ').' || regColName;
        defaultValue := 'Custom List ' || i::text;

          EXECUTE $SomeTag$UPDATE patient_header_preferences SET field_desc=CASE WHEN ($SomeTag$ || regFieldName || $SomeTag$ !='') THEN $SomeTag$ || regFieldName || $SomeTag$ ELSE '$SomeTag$|| defaultValue || $SomeTag$' END WHERE field_name = '$SomeTag$ || pcolName || $SomeTag$'$SomeTag$;
        i:=i+1;
     END loop;

RETURN NEW;
END;
$$ language plpgsql;

DROP TRIGGER IF EXISTS update_patient_header_preferences ON registration_preferences;
CREATE TRIGGER update_patient_header_preferences after UPDATE ON registration_preferences FOR each row EXECUTE PROCEDURE modify_patient_header_pref();