Array element access in ADQL

[Markus Demleitner]

Hi Walter,

On Mon, Jul 24, 2017 at 01:34:06PM -0700, Walter Landry wrote:
> Markus Demleitner <msdemlei at ari.uni-heidelberg.de> wrote:
> > I'm much less enthusiastic about introducing array literals into ADQL
> > ('{1,2,3}') -- what would these be used for?
> 
> SQL 99 uses ARRAY[1,2,3].  It is nice to have some way to construct
> arrays in line.  I have a column 'observing_mode' that is '1' for
> spectra, and '2' for images.  I can write a query that returns a
> string with an expression like
> 
>   ARRAY['spectra','image'][observing_mode]

Well, yes, that's neat, but I'm unconvinced it's enough to introduce
something as fundamental as creating arrays (with all that's coming
with it) into the language.  SQL92's conventional way to achive
this is

CASE WHEN observing_mode=1 THEN 'spectra'
WHEN observing_mode=2 THEN 'image'
ELSE 'junk'

-- it's a bit more verbose, yes, but it's also much more general, and
it's SQL92 (which still is the notional basis for ADQL), although the
creators of ADQL chose to not include it.

So, if this use case is considered important, I'd count is as an
argument for pulling CASE into ADQL from SQL92 and not so much one
for array literals.

       -- Markus