Re: Pamona follow up on functions / triggers / views

["m(dot)wetmore(at)secomintl(dot)com" <m(dot)wetmore(at)secomintl(dot)com>]

I was asked about C server side functions as opposed to straight PL/pgsql.
Here are a few reasons why doing functions in C/Ruby/Pearl/Java work for 
me.

1. *Overhead*.  Why make pg do the work, let the os do it. (less concern 
of postgresql.conf tweaking)
2. *Implicit control* of what it's doing
3. *Security* - if someone gets access to db, they cannot understand the 
function. Look at how C function (attached .c file) is shown in db:

   /-- DROP FUNCTION extractsubstring(character varying, character 
varying, character varying, integer, integer);

   CREATE OR REPLACE FUNCTION extractsubstring(character varying, 
character varying, character varying, integer, integer)
     RETURNS text AS
   '/usr/lib/pgsql/SecomSQLib.so', 'ExtractSubString'
     LANGUAGE 'c' VOLATILE;
   ALTER FUNCTION extractsubstring(character varying, character 
varying, character varying, integer, integer) OWNER TO secom;
/
since source code is not on production server, just compiled (unreadable 
by humans) *.so, there is no risk.

...as opposed to a human readable/dumpable PL/pgsql function shown in 
database

  / -- Function: rangetest(integer, integer, integer)

   -- DROP FUNCTION rangetest(integer, integer, integer);

   CREATE OR REPLACE FUNCTION rangetest(integer, integer, integer)
     RETURNS integer AS
   $BODY$
   DECLARE
   Data ALIAS FOR $1;
   LowerLimit ALIAS FOR $2;
   UpperLimit ALIAS FOR $3;
   BEGIN
   IF Data >= LowerLimit and Data <= UpperLimit THEN
   RETURN 1;
   END IF;
   RETURN 0;
   END;
   $BODY$
   LANGUAGE 'plpgsql' VOLATILE;
   ALTER FUNCTION rangetest(integer, integer, integer) OWNER TO secom;
/
this could allow an unauth user to understand, exploit or rewrite the 
function.
(it could also save you from potential lawsuit)



4. *Failures* (HW/SW/USER) - I'll never erase the function in prog. lang
5. *Testing* - much easier to trap errors in compiled code.
6. *Debug* - Broken functions are had to debug in PL/pgsql

While C may not be the neatest lang., I feel the peace of mind outweighs 
the pain-in-the-ass!

Also attached is a sample trigger in PL/pgsql.


Thanks Again - Great Presentation Keith...will make me think.

/matthew

--

Matthew Wetmore

Secom International, Inc.
9610 Bellanca, Ave.
Los Angeles, CA 90045
310-641-1290

Download my Public PGP encryption key
from http://cryptonomicon.mit.edu/ (or any other public key server)

PCI COMPLIANCE UPDATE:
SECOM CANNOT RECEIVE ANY CREDIT CARD CARD HOLDER DATA FROM CLIENTS.
IF YOUR EMAIL IS SCANNED AND FOUND TO CONTAIN CARD HOLDER DATA,
IT WILL BE DELETED AUTOMATICALLY.

This e-mail is intended for the addressee shown. It contains information
that is confidential and protected from disclosure.  Any review,
dissemination or use of this transmission or its contents by persons or
unauthorized employees of the intended organizations is strictly
prohibited. The contents of this email do not necessarily represent the
views or policies of Secom International Inc., or its employees.



/******************************************************************************
* ExtractSubString(Haystack,, First Marker, Default, startindex, numberofchar)

DROP   FUNCTION ExtractSubString(VARCHAR, VARCHAR, VARCHAR, INT, INT);
CREATE FUNCTION	ExtractSubString(VARCHAR, VARCHAR, VARCHAR, INT, INT)
 RETURNS text
 AS '/usr/lib/pgsql/SecomSQLib.so', 'ExtractSubString' LANGUAGE 'C';


******************************************************************************/

PG_FUNCTION_INFO_V1(ExtractSubString);

Datum
ExtractSubString(PG_FUNCTION_ARGS)
{
	text   	*t1 = PG_GETARG_TEXT_P(0);
	text	*t2 = PG_GETARG_TEXT_P(1);
	text	*t3 = PG_GETARG_TEXT_P(2);
	int     startindex = PG_GETARG_INT32(3);
	int     numberofchar = PG_GETARG_INT32(4);
	text 	*resulttext;

	int		len,i,
			foundstartmarker=0,	// Indicates field found
			foundendmarker=0;	// Indicates end of field found

	char	haystack[256],		// temp storage of source string
			workstring[256],
			*startmarker,		// Requested field header (For example 'D1')
			*searchpointer,		// Working pointer
			*startmarkerpos,	//this contains the location of the startmarker in the string of data
			*endmarkerpos;		//this contains the location of the endmarker in the string of data

	memcpy(haystack, VARDATA(t1),VARSIZE(t1)-VARHDRSZ);  //copy in the old data, No terminator VARDATA(t1) points to start of string
	len=(VARSIZE(t1)-VARHDRSZ); //get string length
	haystack[len]='\0'; //put in terminator
	startmarker   = VARDATA(t2);

	if ((VARSIZE(t2)-VARHDRSZ) > 0)  //Default marker supplied - start scanning for marker
	{
		for (searchpointer=haystack; (searchpointer<(haystack+len)) && (foundendmarker==0); searchpointer=searchpointer+2)
		{

			if ((foundstartmarker==0) && (*searchpointer==*startmarker)) {  //Check First character of startmarker
				if (*(searchpointer+1)==*(startmarker+1)) {  //Check Second character of startmarker
					startmarkerpos=searchpointer;
					foundstartmarker=1;
					continue;
				}
			}

			if ((foundstartmarker==1) && ((*searchpointer >= 0x41) && (*searchpointer <= 0x46))) {  //Check For A, B, C, D, E or F
				if (((*(searchpointer+1) >= 0x30) && (*(searchpointer+1) <= 0x39)) || (*(searchpointer+1) = 0x46  )) {  //Check for integer or F (for FF)
					endmarkerpos=searchpointer;
					foundendmarker=1;
	//    			fprintf(stderr, "Endmarker Found at Iteration: %d", i);
				}
			}
		}


		if ((foundstartmarker==1) && (foundendmarker==0))  //This could happen when we want the last field in the string
		{
			endmarkerpos=haystack+len; //we pick up from the Startpointer and include the rest of the string
			foundendmarker=1;
	//		fprintf(stderr, "Startmarker found, but no Endmarker");
		}


		if (foundendmarker==1) //Ok, everything went smooth, go on and return the string between the markers
		{
			startmarkerpos=startmarkerpos+2; //advance by two to skip the startmarker
			len=endmarkerpos-startmarkerpos;  //find the length of the string
			if (len>0) {  //if no data is found in the field, return Default
				memcpy(workstring, startmarkerpos, len);
				workstring[len]='\0';
/*
				resulttext = (text *) palloc(VARHDRSZ + len); //allocate memory
				VARATT_SIZEP(resulttext) = (VARHDRSZ + len);   //sizes the result ?
				memcpy(VARDATA(resulttext), startmarkerpos, len); //copy in our result to the result-memory address
				PG_RETURN_TEXT_P(resulttext);
*/
			}
		}
		else //startmarker was supplied but not found in string -- return default
		{
			PG_RETURN_TEXT_P(t3); //return default
		}
	}
	else //Startmarker not supplied -- copy in the whole string to workstring and do the substring
	{
		strcpy(workstring, haystack);
	}

	//now we need to do the substring-operation
	if (strlen(workstring) < startindex)  //does the start index point outside the workstring ?
	{
		PG_RETURN_TEXT_P(t3); //return default
	}

	if (strlen(workstring) < (startindex + numberofchar) -1)  //does the start index + number of chars point outside the workstring ?
	{
		numberofchar = strlen(workstring) - startindex+1; //reduce number of chars
	}

	strcpy(workstring, workstring + startindex -1);

	workstring[numberofchar]='\0';
	len = strlen(workstring);

	if (len==0) PG_RETURN_TEXT_P(t3); //return default

	//Now we need to scan threw and see if we got nothing but spaces. if we did return default

	for (i=0; i < len; i++)
	{
		if (workstring[i] != 0x20) break; //Non space found, return result
		if (i==len - 1) PG_RETURN_TEXT_P(t3); //Only spaces found,return default
	}


	resulttext = (text *) palloc(VARHDRSZ + len); //allocate memory
	VARATT_SIZEP(resulttext) = (VARHDRSZ + len);   //sizes the result ?
	memcpy(VARDATA(resulttext), workstring, len); //copy in our result to the result-memory address
	PG_RETURN_TEXT_P(resulttext);
}

/*****************************************************************************/


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;