Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I need to standardize coding practices for project that compromises, among others, of pl/pgsql database, that has some amount of nontrivial code.

I look for:

  • Code formatting guidelines, especially inside procedures.
  • Guidelines on what constructs are considered unsafe (if any)
  • Naming conventions.
  • Code documentation conventions (if this is practiced)

Any hints to documents that define good practices in pl/pgsql code? If not I'm looking for hints to practices that you consider good.

There is related question regarding TSQL: Can anyone recommend coding standards for TSQL?, which is relevant to psql as well, but I need more information on stored procedures.

Other related questions:

share|improve this question
    
almost all Oracle PL/SQL guidlines are ok for PL/pgSQL postgres.cz/wiki/PL/… –  user69725 Oct 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. –  Dallas Oct 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.

One caution however is the possibility of search path playing issues with SECURITY DEFINER functions. You may want to look at this particularly in cases where users are going to have the ability to create relations in other schemas (or even temporary relations). See http://www.postgresql.org/docs/9.2/static/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY

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.

My blog, http://ledgersmbdev.blogspot.com, regularly features thoughts and experience on this issue so it may be helpful :-)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.