Re: case_preservation_and_insensitivity = on

On Thu, Feb 23, 2017 at 8:04 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> It doesn't sound like a good solution to me, because there can be SQL
> code inside stored procedures that clients never see.

In our code base, we use CamelCase in all PL/pgSQL functions, both for
columns and variables,
e.g. SELECT UserID INTO _UserID FROM Users WHERE Username = 'foo';

Here, it's not a problem that the column name is e.g. "userid",
since the case-insensitive feature makes it work.

What type of case problem do you foresee for stored procedures?

I've only experienced the case-folding to be a problem outside of SPs,
since the casing *is* preserved in the PL/pgSQL source code
(since it's stored as-is, without any modifications).

What *would* be a problem though, is if in a future PL/pgSQL 3,
a PL/pgSQL query like,
SELECT UserID FROM Users WHERE Username = 'foo';
would automatically export the column "UserID" to the current scope as
a PL/pgSQL 3 variable named "userid",
since then you would actually want the value of the userid column to
be exported to a variable named "UserID".

Such a feature would be nice, since a very common code-pattern in
PL/pgSQL is to just have lots of meaningless identical lists of
columns and then an identical list of variables with the same names as
the columns.
When the list is short, it's not a problem, but when selecting lots of
columns, it gets ugly.

What I usually end up with is to align the columns and variables on
two rows, e.g.:

SELECT SomeCol, OtherCol, FooCol, BarCol, MyCol, ExtraCol, LastCol
INTO _SomeCol, _OtherCol, _FooCol, _BarCol, _MyCol, _ExtraCol, _LastCol
FROM Foo
WHERE Bar = 'Baz';

This is to avoid typos that are then visually easy to spot, thanks to
all chars being aligned.

Imagine if, thanks to case-preservation, if you should simply do:
SELECT SomeCol, OtherCol, FooCol, BarCol, MyCol, ExtraCol, LastCol
FROM Foo
WHERE Bar = 'Baz';

And all the columns would be exported to the variables SomeCol,
OtherCol, FooCol, BarCol, MyCol, ExtraCol, LastCol,
instead of somecol, othercol, foocol, barcol, mycol, extracol, lastcol;

This would be a huge win in avoiding unnecessary code repetition.

Then of course, if you want a column Foo to instead be exported to
Bar, then you simply do "SELECT Foo AS Bar".

Thoughts?