almost all Oracle PL/SQL guidlines are ok for PL/pgSQL postgres.cz/wiki/PL/…
–
user69725Oct 23 '12 at 14:48

Not a complete answer, but definitely create error messaging (using ORA error messaging or logging of other error info to a file or table) that spits out section numbers within functions/procedures to track down failures. Nothing worse than debugging a 400 line procedure that has nothing to narrow down where the error occurred.
–
DallasOct 24 '12 at 4:34

1 Answer
1

A few points from our experience both in terms of maintainability and security. A lot of things like code formatting are things that can be done any number of ways as long as you are consistent and you use white space appropriately.

As for what is unsafe from a security perspective the only thing that comes to mind is combining SECURITY DEFINER, EXECUTE, and string interpolation. This leads to the possibility of SQL injection that will run as the owner of the function. This is sometimes necessary (so use quote_ident and quote_literal as appropriate) but when it is, code needs to be additionally reviewed for weaknesses. This comes up when executing DDL inside stored procs, particularly for user management.

A second thing I prefer to do is to add COMMENT ON FUNCTION .... after each function definition. I think this is preferable to explanatory API comments at the top because it can be pulled up by automatic documentation tools.

A third thing I highly recommend is to try to keep PLPGSQL functions to be in the form of a main db query with small amounts of supporting procedural logic. This helps with both clarity and debugging. The more you can consolidate into a single query the faster the procedure as a whole can be debugged, because there are fewer possible places for any given error to lurk.

Finally one big trap that occurs with user defined functions being called by the application is that the functions often require fixed numbers of arguments, and this can be an issue in terms of maintainability. I would recommend reading this http://ledgersmbdev.blogspot.com/2011/10/introduction-to-soda.html which is an attempt to apply what can be learned from web services to PostgreSQL stored procedures.