Re: My first PL/pgSQL function

On Tue, Oct 20, 2015 at 12:43 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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
>
​
Thank you Pavel and Merlin for the feedback. I'm delighted that my first
PL/pgSQL function wasn't rubbish. I think the credit goes to the authors of
the [fine] PostgreSQL manual.

Pavel, I've taken your recommendation to heart but I'll need to do some
more reading on composite types because I didn't realize they were on
option in this context (i.e., the fields/columns aren't fixed).

Merlin:
I went w/ || on purpose because I want/need its NULL behavior. The
relationship between the columns with which || is used is a binary
(mutually exclusive) relationship. So they both can't be NULL nor NOT NULL.

I understand that right justification is an issue of personal taste. For me
SQL is such a verbose and dense language that I use the justification to
help break it up into visually manageable chunks. In traditional
programming languages we have curly braces and/or indentation to help us
visually organize and parse the code. I try to use justification to the
same effect. And since most code is read more frequently than it's written
I think a little realigning is a small price to pay.

I haven't investigated or encountered any doxygen processing tools. As a
matter of fact I wasn't even aware that the commenting style that I used
was called doxygen! Until recently I used to program in Java regularly
(since the Java 1.1 days) so I have a tendency to bring that style of
commenting w/ me to other languages. The version on display is a PHP'ified
variation of JavaDoc which thanks to you I just learned is called doxygen.

Like I said I'm an old Java hack and used to use IntelliJ/IDEA to sling
Java. But even though I rarely code in Java anymore I continue to use IDEA
for coding everything, except shell scripts. IDEA has support for "jump to
definition" and (more importantly) renames across files (i.e., refactoring).

Thanks again for the feedback it is truly appreciated.

Regards,

Dane
​