This is one of my  PL/pgsql trigger function.

(makes a cashier shift report)


-- Function: margeimport()

-- DROP FUNCTION margeimport();

CREATE OR REPLACE FUNCTION margeimport()
  RETURNS "trigger" AS
$BODY$
DECLARE 
SQL VARCHAR;
StartRecord INTEGER;
EndRecord INTEGER;
DummyRec Record;
ReportNumber bigint;
ReportExists bool;
f92table VARCHAR;
UpdateSQL VARCHAR;
LastFid INTEGER;
ReportType INTEGER;
DayOfWeek INTEGER;
I INTEGER;
RawData VARCHAR;
LTN INTEGER;
Cashier INTEGER;
RecordSQL VARCHAR;
DataExtractSQL VARCHAR;
MARGERecord RECORD;
LoopCounter INTEGER;
CheckForRecordSQL VARCHAR;
CheckForRecord Record;
LTNList INTEGER[8];
CashierList INTEGER[8];
BEGIN
if (NEW.Fid_24<>'F2009F') or (NEW.fid_24 is null) or (ExtractString(NEW.fid_25, 'F6', '0') <> '99') then 
  RETURN New;
END IF;

f92table:='file_92_'||to_char(NEW.fid_2, 'FMMMyy');

-- Check if the MARGE table is there, otherwise quit

SQL:='SELECT Check_For_Table(''marge'') as test';
FOR DummyRec IN EXECUTE SQL LOOP
	IF DummyRec.test=false THEN
		RAISE NOTICE 'MARGE TABLE DOES NOT EXIST';
		RETURN NEW; 
	END IF;	   
END LOOP;

RAISE NOTICE 'SIGNOFF FOUND';  
EndRecord:=New.Record_number;
SQL:='SELECT Record_Number FROM '||f92table||' where fid_24=''F2009F'' and extractstring(fid_25, ''F6'', ''0'')=''01'' and record_number < '||EndRecord||' and fid_1='||quote_literal(New.Fid_1)||' order by record_number desc limit 1';
FOR DummyRec IN EXECUTE SQL LOOP
   StartRecord:=DummyRec.Record_Number;	
END LOOP;

IF StartRecord IS NULL THEN
	RAISE NOTICE 'STARTRECORD NOT FOUND';
	RETURN NEW;
END IF;

RAISE NOTICE 'StartRecord: % EndRecord: %', StartRecord, EndRecord;

SQL:='CREATE OR REPLACE VIEW MARGEView as select * from '||f92table||' where fid_24=''F2009F'' and fid_1='||quote_literal(New.Fid_1)||' and record_number between '||quote_literal(StartRecord)||' and '||quote_literal(EndRecord); 
EXECUTE SQL;


-- Check if it looks like we have a actuall full report, if not exit

IF (EndRecord-StartRecord) < 30 THEN 
	RAISE NOTICE 'REPORT TO SMALL';
	RETURN NEW; 
END IF;

IF (EndRecord-StartRecord) > 100 THEN 
	RAISE NOTICE 'REPORT TO LARGE';
	RETURN NEW; 
END IF;


-- Now we have the start and end record
-- The unique identifier in the MARGE table will be "Report Number", which is the Date + time, For Example 2312061459. This needs to be an int64
-- First create the unique identifier then go look and see if it is already in the MARGE table

SQL:='SELECT to_char(fid_2, ''ddmmyy'')||to_char(fid_1, ''HH24MISS'') as ReportNo, ExtractString(fid_25, ''F4'', ''0'') as ReportType, To_Char(fid_2, ''D'') as DayOfWeek, fid_25 from MARGEView where record_number='||quote_literal(StartRecord);
FOR DummyRec IN EXECUTE SQL LOOP 
   ReportNumber:=DummyRec.ReportNo;
   ReportType:=DummyRec.ReportType;
   RawData:=DummyRec.Fid_25; -- Used to find out how many LTNs are in the report
   -- Secom has Monday being Day 1 of the week but PostGreSQL has Sunday being day 1, so we need to adjust 
   -- We need to adjust the day by additionally because the report prints after 23:59. 
   -- NOTE: NUMBERS COULD GET SCREWED UP IF SOMEONE MANUALLY PRINTS A REVENUE BY DAY REPORT
   I:=DummyRec.DayOfWeek;
   
   IF I=1 THEN -- "NOW" IS Sunday, NEED TO SUBTRACT 2
	DayOfWeek:=6; 
        ELSEIF  I=2 THEN -- "NOW IS MONDAY, NEED TO SUBTRACT 2"
   	DayOfWeek:=7;
        ELSE DayOfWeek:=I - 2; -- just subtract 2
   END IF;
END LOOP;
RAISE NOTICE 'Report %', ReportNumber;

-- Now we have the unique report number, next step is checking if the report is already in the table. If it is, we update it, if not we add it

ReportExists:=false;
SQL:='SELECT ReportNumber from MARGE where ReportNumber='||quote_literal(ReportNumber);
FOR DummyRec IN EXECUTE SQL LOOP 
  ReportExists:=true;
END LOOP;

LTN:=-1;
-- Check if report has multiple columns (Rev Today by LTN) and if so we need to create more than one record and insert the LTN number in fid_2
IF ReportType=1 THEN
	RAISE NOTICE 'LTN REVENUE BY DAY REPORT FOUND';
	FOR i IN 1..7 LOOP -- LOOP THREW D1 THREW D7 AND LOOK FOR LTN NUMBER
		SQL:='SELECT ExtractString('||quote_literal(RawData)||', '||quote_literal('D'||i)||', ''0'') as ltn';
		FOR DummyRec in EXECUTE SQL LOOP
			LTN:=DummyRec.LTN;
			EXIT WHEN LTN=0;
			LTNList[i]:=LTN; -- this contains a list of the LTNs, needed later when we put the data in 
			CheckForRecordSQL:='SELECT ReportNumber from MARGE where ReportNumber = '||quote_literal(ReportNumber)||' and fid_2= '||quote_literal(LTN); 
			ReportExists:=false;
			FOR CheckForRecord in EXECUTE CheckForRecordSQL LOOP	
				RAISE NOTICE '3 % %', ReportNumber, LTN;
				ReportExists:=true;
			END LOOP;
			IF ReportExists=false THEN
				SQL:='INSERT INTO MARGE(ReportNumber, fid_2) SELECT '||quote_literal(ReportNumber)||','||quote_literal(LTN);
				EXECUTE SQL;
			END IF;
			ReportExists:=true; --this is here just so that the standard report checker-wecker guy does not add any new records
		END LOOP;
	END LOOP;
END IF;

Cashier=-1;
IF ReportType=10 THEN --Revenue Emp By Day
	RAISE NOTICE 'DAILY REVENUE BY EMPLOYEE REPORT FOUND';
	FOR i IN 1..7 LOOP -- LOOP THREW D1 THREW D7 AND LOOK FOR CASHIER CARD NUMBER
		SQL:='SELECT ExtractString('||quote_literal(RawData)||', '||quote_literal('D'||i)||', ''0'') as Cashier';  
		FOR DummyRec in EXECUTE SQL LOOP
			Cashier:=DummyRec.Cashier;  
			EXIT WHEN Cashier=0;
			CashierList[i]:=Cashier; -- this contains a list of the Cashier Numbers, needed later when we put the data in 
			CheckForRecordSQL:='SELECT ReportNumber from MARGE where ReportNumber = '||quote_literal(ReportNumber)||' and fid_3= '||quote_literal(Cashier); 
			ReportExists:=false;
			FOR CheckForRecord in EXECUTE CheckForRecordSQL LOOP	
				RAISE NOTICE '3 % %', ReportNumber, Cashier;
				ReportExists:=true;
			END LOOP;
			IF ReportExists=false THEN
				SQL:='INSERT INTO MARGE(ReportNumber, fid_3) SELECT '||quote_literal(ReportNumber)||','||quote_literal(Cashier);
				EXECUTE SQL;
			END IF;
			ReportExists:=true; --this is here just so that the standard report checker-wecker guy does not add any new records
		END LOOP;
	END LOOP;
END IF;


-- If report is not there then create it
IF ReportExists = false THEN
SQL:='INSERT INTO MARGE(ReportNumber) SELECT '||quote_literal(ReportNumber);
EXECUTE SQL;
END IF;


-- First we want to insert the report TYPE, date and time into MARGE
SQL:='SELECT Extractint(fid_25, ''F4'', ''0'') as ReportType, fid_1, fid_2 from MARGEView WHERE Record_Number='||quote_literal(StartRecord);
FOR DummyRec IN EXECUTE SQL LOOP 
	UpdateSQL:='UPDATE MARGE SET TIME='||quote_literal(DummyRec.fid_1)||', DATE='||quote_literal(DummyRec.fid_2)||', ReportType='||quote_literal(DummyRec.ReportType)||' WHERE ReportNumber='||quote_literal(ReportNumber);
	EXECUTE UpdateSQL;
END LOOP;

-- Now we have the report number in table MARGE, its time to extact the report data itself

RecordSQL:='SELECT ReportNumber, fid_2 from MARGE where ReportNumber='||quote_literal(ReportNumber); -- this will pick up all the records in that report (could be multiple LTNs)

LoopCounter:=0;
FOR MARGERecord IN EXECUTE RecordSQL LOOP -- This loops for every new (Gateway) MARGE record we have (Could be multiple as in the case of LTN Today)
	LoopCounter:=LoopCounter+1;	
  	IF ReportType=1 THEN -- Daily revenue by LTN
      		DataExtractSQL:='SELECT Extractint(fid_25, ''F6'', ''0'') as DataType, FindBaseMARGEFid(Extractint(fid_25, ''F7'', ''0'')) as fid, Extractint(fid_25, ''F8'', ''0'') as valbucketnumber, Cast(ExtractString(fid_25, '||quote_literal('E'||LoopCounter)||', ''0'') as float) as dollars, Extractint(fid_25, '||quote_literal('D'||LoopCounter)||', ''0'') as numbers from MARGEView';

  	ELSIF ReportType=4 THEN -- Revenue by Day 
      		DataExtractSQL:='SELECT Extractint(fid_25, ''F6'', ''0'') as DataType, FindBaseMARGEFid(Extractint(fid_25, ''F7'', ''0'')) as fid, Extractint(fid_25, ''F8'', ''0'') as valbucketnumber, Cast(ExtractString(fid_25, '||quote_literal('E'||DayOfWeek)||', ''0'') as float) as dollars, Extractint(fid_25, '||quote_literal('D'||DayOfWeek)||', ''0'') as numbers from MARGEView';
  
  	ELSIF ReportType=21 THEN -- This works for Shift Report by LTN
      		DataExtractSQL:='SELECT Extractint(fid_25, ''F6'', ''0'') as DataType, FindBaseMARGEFid(Extractint(fid_25, ''F7'', ''0'')) as fid, Extractint(fid_25, ''F8'', ''0'') as valbucketnumber, Cast(ExtractString(fid_25, ''E1'', ''0'') as float) as dollars, Extractint(fid_25, ''D1'', ''0'') as numbers from MARGEView';
  
  	ELSIF ReportType=10 THEN -- This works for Daily Revenue by Emp
      		DataExtractSQL:='SELECT Extractint(fid_25, ''F6'', ''0'') as DataType, FindBaseMARGEFid(Extractint(fid_25, ''F7'', ''0'')) as fid, Extractint(fid_25, ''F8'', ''0'') as valbucketnumber, Cast(ExtractString(fid_25, '||quote_literal('E'||LoopCounter)||', ''0'') as float) as dollars, Extractint(fid_25, '||quote_literal('D'||LoopCounter)||', ''0'') as numbers from MARGEView';

  	ELSE 
 		RETURN NEW; -- Report Not Supported
  	END IF;
	UpdateSQL:='';

	FOR DummyRec IN EXECUTE DataExtractSQL LOOP  -- loop threw and extract the data. This Loops once for each file_92 row
		IF DummyRec.DataType=3 THEN -- regular fids
			IF DummyRec.fid=Lastfid THEN
				RAISE NOTICE 'DUPLICATE FIDS FOUND ABORTING';
				RETURN NEW;
			END IF;
			UpdateSQL:=UpdateSQL||'fid_'||DummyRec.fid||'=';
			-- if numbers are zero then grab the data from "Dollars" instead
			IF DummyRec.Numbers=0 THEN 
				UpdateSQL:=UpdateSQL||DummyRec.dollars||',';
				ELSE 						
				UpdateSQL:=UpdateSQL||DummyRec.Numbers||',';
			END IF;
			LastFid:=DummyRec.fid;
		END IF;

		IF DummyRec.DataType=4 THEN -- Val counts
			UpdateSQL:=UpdateSQL||'fid_'||DummyRec.valbucketnumber+128||'='||DummyRec.Numbers||',';
		END IF;

		IF DummyRec.DataType=5 THEN -- Bucket counts
			UpdateSQL:=UpdateSQL||'fid_'||DummyRec.valbucketnumber*2+255||'='||DummyRec.Numbers||',';
			UpdateSQL:=UpdateSQL||'fid_'||DummyRec.valbucketnumber*2+256||'='||DummyRec.Dollars||',';   
		END IF;

	END LOOP;

	IF length(UpdateSQL) < 3 THEN
		RETURN NEW;
	END IF;

	-- Strip out last comma
	SQL:='SELECT substring('||Quote_Literal(UpdateSQL)||', 1, length('||Quote_Literal(UpdateSQL)||')-1) as UpdateSQL';
	FOR DummyRec IN EXECUTE SQL LOOP 
		UpdateSQL:=DummyRec.UpdateSQL;
	END LOOP;

	RAISE NOTICE 'Report%', ReportNumber;
	SQL:='UPDATE MARGE SET '||UpdateSQL||' WHERE ReportNumber='||ReportNumber;
	
	IF LTN <> -1 THEN -- Are we doing multiple LTNs ? 
		SQL:=SQL||' AND fid_2='||quote_literal(LTNList[LoopCounter]);  -- if so add LTN in there as well
	END IF;

	IF Cashier <> -1 THEN -- Are we doing multiple Cashiers ? 
		SQL:=SQL||' AND fid_3='||quote_literal(CashierList[LoopCounter]);  -- if so add Cashier Number in there as well
	END IF;

	RAISE NOTICE 'COMPLETE UPDATE SQL: %', SQL; 
	IF length(SQL) > 5 THEN  -- otherwise we get Null-SQL execution problems
		EXECUTE SQL;
	END IF;
END LOOP;
RETURN NEW;
END; 
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION margeimport() OWNER TO secom;