On Tue, Oct 20, 2015 at 12:43 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On Tue, Oct 20, 2015 at 9:45 AM, Dane Foster <studdugie@xxxxxxxxx> 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
