Re: Pamona follow up on functions / triggers / views

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.