Horton

Re: [HACKERS] [PATCH] Generic type subscripting

Dmitry Dolgov <9erthalion6@gmail.com>

View thread View on postgr.es
From: Dmitry Dolgov <9erthalion6@gmail.com>
To: Oleksandr Shulgin <oleksandr.shulgin@zalando.de>
Cc: Arthur Zakirov <a.zakirov@postgrespro.ru>, Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, Michael Paquier <michael.paquier@gmail.com>, Oleg Bartunov <obartunov@gmail.com>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, David Steele <david@pgmasters.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2018-03-22T22:25:02Z
Lists: pgsql-hackers

Attachments

> On 20 March 2018 at 11:09, Oleksandr Shulgin <oleksandr.shulgin@zalando.de> wrote:
>> On Tue, Mar 6, 2018 at 6:21 PM, Dmitry Dolgov <9erthalion6@gmail.com> wrote:
>>
>>
>> One more small update after fd1a421fe6 in attachments.
>
>
> Before looking at the code I have a few comments about documentation:
>
> ...
>
> In other words, I would like to see this part of documentation to be
> extended beyond just showcasing the syntax.

Good point, thanks for noticing. The thing is that the implementation of
subscripting for jsonb data type in this patch relies on the `setPath` function
and follows the same rules as e.g. `jsonb_set`, but I need to mention this
explicitly in the documentation. Speaking about your questions:

> +-- Extract value by key
> +SELECT ('{"a": 1}'::jsonb)['a'];
>
> What is the result of running this query?  What is the resulting data type?
>

Jsonb subscripting expression always returns another jsonb

> +-- Extract element by index
> +SELECT ('[1, "2", null]'::jsonb)['1'];
>
> What is the result here?  Why subscript is a string and not a number?  Are
> subscription indexes 0- or 1-based?
>

For jsonb arrays an index is 0 based. It's also not necessary to have an index
as a string in this situation (so `data['1']` and `data[1]` are actually equal)

> +-- Select records using where clause with subscripting
> +SELECT * from table_name where jsonb_field['key'] = '"value"';
>
> Use of double quotes around "value" requires some explanation, I think.

In case of comparison, since a subscripting expression returns something of
jsonb data type, we're going to compare two objects of type jsonb. Which means
we need to convert 'value' to a jsonb scalar, and for that purpose it should be
in double quotes.

> Should the user expect that a suitable index is used by the query planner
> for this query?

There is no specific indexing support for subscripting expressions, so if you
need you can create a functional index using it.

Here is the updated version of patch, rebased after recent conflicts and with
suggested documentation improvements.

Commits

  1. Throw error when assigning jsonb scalar instead of a composite object

  2. Filling array gaps during jsonb subscripting

  3. Implementation of subscripting for jsonb

  4. Allow ALTER TYPE to update an existing type's typsubscript value.

  5. Allow subscripting of hstore values.

  6. Support subscripting of arbitrary types, not only arrays.

  7. jit: Reference function pointer types via llvmjit_types.c.

  8. Teach contain_leaked_vars that assignment SubscriptingRefs are leaky.

  9. jit: Correct parameter type for generated expression evaluation functions.

  10. Renaming for new subscripting mechanism

  11. Fix assertion failure for SSL connections.

  12. Teach eval_const_expressions() to handle some more cases.