Re: My first PL/pgSQL function

2015-10-20 16:45 GMT+02:00 Dane Foster <studdugie(at)gmail(dot)com>:

> Hello,
>
> 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.

Regards

Pavel

>