Greenplum leverages PostgreSQL internals which has great benefits from security, Multi-Version Concurrency Control, and languages. Greenplum supports a wide variety of languages but my favorite by far has to be PL/pgSQL.
PL/pgSQL feels a lot like Oracle PL/SQL. It has the same “Ada” look and feel which makes it easy for an Oracle developer to start writing PL/pgSQL.
Since Greenplum is supporting the same language that PostgreSQL does, you can find excellent information online about different techniques of writing quality PL/pgSQL functions. For example, here is good documentation on how to port from Oracle PL/SQL to PL/pgSQL => http://www.postgresql.org/docs/8.2/interactive/plpgsql-porting.html
I follow the same template for each PL/pgSQL function I write and below are each of the different things I always follow.
- Functions– prefix with fn_ to denote it is a function
- Table names– all lower case with underscores. The data dictionary stores objects in all lower case so it is easier to read table names with underscores.
- Variables– prefix with v_
- Parameters– prefix with p_
- Function Name– I add the function name to each function I write so that I can include this in the exception message. This is very helpful when you have functions executing other functions and you need to know what function really failed.
- Location– I prefer to add a location variable to functions and denote where I am in the code frequently. I use the location value to know where the error occurred.
- Exception Block– I raise the exception with:
- RAISE EXCEPTION ‘(%:%:%)’, v_function_name, v_location, sqlerrm;
The parenthesis surrounds the error message from this function. If the function executes another function and the second function is the one that failed, the parenthesis help to indicate what function, the location the error, and the error message that occurred.
CREATE OR REPLACE FUNCTION fn_name() RETURNS void AS $$ DECLARE v_function_name text := 'fn_name'; v_location int; BEGIN v_location := 1000; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION '(%:%:%)', v_function_name, v_location, sqlerrm; END; $$ LANGUAGE plpgsql;
As you develop your code, increment the v_location variable so if you do get an error, it will be raised the approximate location in your code.
More examples of my PL/pgSQL code can be found in the Outsourcer code. I have about 8 functions that you can reference for examples of writing PL/pgSQL functions.