Re: My first PL/pgSQL function

On Tue, Oct 20, 2015 at 9:45 AM, Dane Foster <studdugie(at)gmail(dot)com> wrote:
> 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,

This is beautiful code. It in fact is an for all intents and purposes
an exact replica of my personal style.

Some notes:
*) I agree with Pavel; better to return specific columns if the result
is well defined (mark them in the argument list with OUT and I tend to
not prefix underscore them in that case). The caller can always do a
json production if necessary, or you can wrap the function.

Some other minor suggestions:
*) I tend to prefer format() to || concatenation in ALL usage these
days. It's more readable and tends to give better handling of NULL
strings by default.

*) this login should really be documented in line
IF 2 = array_length(cpnxtra, 1)
THEN

*) I avoid all right justified code (spaced out AS x, AS y, etc). I
understand the perceived readability improvements but none of them are
worth the cascading edits when variables get longer.

*) let's compare notes on your doxygen style code markup. I've been
trouble finding a good robust tool that does exactly what I want,
curious if you did better.

*) FYI, since you're obviously not using pgadmin, I use 'Sublime Text
3' for my code editor. I've significantly enhanced it to support
various postgresqlisms, so if you're maintaining code in a codebase,
you have reasonable support for 'jump to definition' and things like
that.

merlin