How to get a row, and all of it’s dependencies?

This question was asked at least twice on some support channel. Getting a row is trivial: select * from table where id = ?. But what about dependencies – the rows that this exported row references?

Decided to take a look at this task.

Continue reading How to get a row, and all of it's dependencies?

Waiting for PostgreSQL 15 – JSON_TABLE

On 4th of April 2022, Andrew Dunstan committed patch:

JSON_TABLE
 
This feature allows jsonb data to be treated as a table and thus used in
a FROM clause like other tabular data. Data can be selected from the
jsonb using jsonpath expressions, and hoisted out of nested structures
in the jsonb to form multiple rows, more or less like an outer join.
 
Nikita Glukhov
 
Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zhihong Yu (whose
name I previously misspelled), Himanshu Upadhyaya, Daniel Gustafsson,
Justin Pryzby.
 
Discussion: https://postgr.es/m/7e2cb85d-24cf-4abb-30a5-1a33715959bd@postgrespro.ru

Continue reading Waiting for PostgreSQL 15 – JSON_TABLE

Getting value from dynamic column in pl/PgSQL triggers?

Every so often, on irc, someone asks how to get value from column that is passed as argument.

This is generally seen as not possible, as pl/PgSQL doesn't have support for dynamic column names.

We can work around it, though. Are the workarounds usable, in terms of performance?

Continue reading Getting value from dynamic column in pl/PgSQL triggers?

Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function

On 7th of April 2018, Teodor Sigaev committed patch:

Add json(b)_to_tsvector function
 
Jsonb has a complex nature so there isn't best-for-everything way to convert it
to tsvector for full text search. Current to_tsvector(json(b)) suggests to
convert only string values, but it's possible to index keys, numerics and even
booleans value. To solve that json(b)_to_tsvector has a second required
argument contained a list of desired types of json fields. Second argument is
a jsonb scalar or array right now with possibility to add new options in a
future.
 
Bump catalog version
 
Author: Dmitry Dolgov with some editorization by me
Reviewed by: Teodor Sigaev
Discussion: https://www.postgresql.org/message-id/CA+q6zcXJQbS1b4kJ_HeAOoOc=unfnOrUEL=KGgE32QKDww7d8g@mail.gmail.com

Continue reading Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function

Waiting for PostgreSQL 11 – Transforms for jsonb to PL/Python and to PL/Perl

On 28th of March 2018, Peter Eisentraut committed patch:

Transforms for jsonb to PL/Python
 
Add a new contrib module jsonb_plpython that provide a transform between
jsonb and PL/Python.  jsonb values are converted to appropriate Python
types such as dicts and lists, and vice versa.
 
Author: Anthony Bykov <a.bykov@postgrespro.ru>

and then, on 3rd of April 2018, he also committed patch:

Transforms for jsonb to PL/Perl
 
Add a new contrib module jsonb_plperl that provides a transform between
jsonb and PL/Perl.  jsonb values are converted to appropriate Perl types
such as arrays and hashes, and vice versa.
 
Author: Anthony Bykov <a.bykov@postgrespro.ru>

Continue reading Waiting for PostgreSQL 11 – Transforms for jsonb to PL/Python and to PL/Perl

Waiting for PostgreSQL 10 – Full Text Search support for json and jsonb

On 31st of March 2017, Andrew Dunstan committed patch:

Full Text Search support for json and jsonb
 
The new functions are ts_headline() and to_tsvector.
 
Dmitry Dolgov, edited and documented by me.

Continue reading Waiting for PostgreSQL 10 – Full Text Search support for json and jsonb

Waiting for 9.5 – Rename jsonb_replace to jsonb_set and allow it to add new values

On 1st of June, Andrew Dunstan committed patch:

Rename jsonb_replace to jsonb_set and allow it to add new values
 
The function is given a fourth parameter, which defaults to true. When
this parameter is true, if the last element of the path is missing
in the original json, jsonb_set creates it in the result and assigns it
the new value. If it is false then the function does nothing unless all
elements of the path are present, including the last.
 
Based on some original code from Dmitry Dolgov, heavily modified by me.
 
Catalog version bumped.

Continue reading Waiting for 9.5 – Rename jsonb_replace to jsonb_set and allow it to add new values

Waiting for 9.5 – Additional functions and operators for jsonb

On 12th of May, Andrew Dunstan committed patch:

Additional functions and operators for jsonb
 
jsonb_pretty(jsonb) produces nicely indented json output.
jsonb || jsonb concatenates two jsonb values.
jsonb - text removes a key and its associated value from the json
jsonb - int removes the designated array element
jsonb - text[] removes a key and associated value or array element at
the designated path
jsonb_replace(jsonb,text[],jsonb) replaces the array element designated
by the path or the value associated with the key designated by the path
with the given value.
 
Original work by Dmitry Dolgov, adapted and reworked for PostgreSQL core
by Andrew Dunstan, reviewed and tidied up by Petr Jelinek.

Continue reading Waiting for 9.5 – Additional functions and operators for jsonb

Waiting for 9.4 – Introduce jsonb, a structured format for storing json.

Portuguese Brazil Version

On 23rd of March, Andrew Dunstan committed patch:

Introduce jsonb, a structured format for storing json.
 
The new format accepts exactly the same data as the json type. However, it is
stored in a format that does not require reparsing the orgiginal text in order
to process it, making it much more suitable for indexing and other operations.
Insignificant whitespace is discarded, and the order of object keys is not
preserved. Neither are duplicate object keys kept - the later value for a given
key is the only one stored.
 
The new type has all the functions and operators that the json type has,
with the exception of the json generation functions (to_json, json_agg etc.)
and with identical semantics. In addition, there are operator classes for
hash and btree indexing, and two classes for GIN indexing, that have no
equivalent in the json type.
 
This feature grew out of previous work by Oleg Bartunov and Teodor Sigaev, which
was intended to provide similar facilities to a nested hstore type, but which
in the end proved to have some significant compatibility issues.
 
Authors: Oleg Bartunov,  Teodor Sigaev, Peter Geoghegan and Andrew Dunstan.
Review: Andres Freund

Continue reading Waiting for 9.4 – Introduce jsonb, a structured format for storing json.