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?
On 11st of December 2020, Tom Lane committed patch:
Allow subscripting of hstore values. This is basically a finger exercise to prove that it's possible for an extension module to add subscripting ability. Subscripted fetch from an hstore is not different from the existing "hstore -> text" operator. Subscripted update does seem to be a little easier to use than the traditional update method using hstore concatenation, but it's not a fundamentally new ability. However, there may be some value in the code as sample code, since it shows what's basically the minimum-complexity way to implement subscripting when one needn't consider nested container objects. Discussion: https://postgr.es/m/3724341.1607551174@sss.pgh.pa.us
Continue reading Waiting for PostgreSQL 14 – Allow subscripting of hstore values.
On 26th of April, Peter Eisentraut committed patch:
Add transforms feature This provides a mechanism for specifying conversions between SQL data types and procedural languages. As examples, there are transforms for hstore and ltree for PL/Perl and PL/Python. reviews by Pavel Stěhule and Andres Freund
On 10th of March, Andrew Dunstan committed patch:
JSON generation improvements.
This adds the following:
json_agg(anyrecord) -> json
to_json(any) -> json
hstore_to_json(hstore) -> json (also used as a cast)
hstore_to_json_loose(hstore) -> json
The last provides heuristic treatment of numbers and booleans.
Also, in json generation, if any non-builtin type has a cast to json,
that function is used instead of the type's output function.
Andrew Dunstan, reviewed by Steve Singer.
Catalog version bumped.Continue reading Waiting for 9.3 – JSON generation improvements.
Andrew Gierth (RhodiumToad on irc) wrote, and Tom Lane committed:
Assorted improvements in contrib/hstore. Remove the 64K limit on the lengths of keys and values within an hstore. (This changes the on-disk format, but the old format can still be read.) Add support for btree/hash opclasses for hstore --- this is not so much for actual indexing purposes as to allow use of GROUP BY, DISTINCT, etc. Add various other new functions and operators. Andrew Gierth
You might be familiar with hstore datatype in PostgreSQL – if you're not – check it out, as it is really cool.
Basically it's hash in database, which you can search in.
Continue reading Deserialization of hstore data structure in Perl
in postgresql 8.2, in contrib, is great new datatype called hstore.
if you're not familiar with it – check the docs.
in short – this is indexable associative-array (hash) in one field.
i was given a task to convert some text field to hstore field.