I'm in the very very very very early stages of migrating a MySQL/PHP app to PostgreSQL/PHP/Lua. Because we are moving to PostgreSQL one of the [many] things I intend to change is to move ALL the SQL code/logic out of the application layer and into the database where it belongs. So after months of reading the [fine] PostgreSQL manual my first experiment is to port some PHP/SQL code to a PostgreSQL function.
At this stage the function is a purely academic exercise because like I said before it's early days so no data has been migrated yet so I don't have data to test it against. My reason for sharing at such an early stage is because all I've done so far is read the [fine] manual and I'd like to know if I've groked at least some of the material.
I would appreciate any feedback you can provide. I am particularly interested in learning about the most efficient way to do things in PL/pgSQL because I would hate for the first iteration of the new version of the app to be slower than the old version.
Thank you for your consideration,
Hi
I have only one objection - the returning result in JSON. I prefer two functions - one: check_discount and second: format_to_JSON(result_of_check_discount). JSON is good format for usage outer of plpgsql - so formatting to JSON should be last operation. Processing tuple - internal PostgreSQL format for composites is significantly faster than JSON.