Re: Typed hstore proposal

Hi all,

I mean to create a typed hstore, called tstore for now. I'm open to
name suggestions. It'll only support a subset of core Postgres types
to begin with. Keys are always text, it's the value that's typed.

Usage is very similar to hstore; this is not a complete reference.

tstore( text, text) Creates a text key-value pair.

tstore( text, int4 ) Creates an integer key-value pair.

tstore -> text Returns a tvalue, which is basically an
oid-value pair.

typeof( tvalue ) Returns the oid.

tvalue::int4 The integer value.

tvalue::text The text value.

each_int( tstore ) Set of all keys and values where the value
is int4.

each_text( tstore ) Set of all keys and values where the value
is text.

each( tstore ) Set of all keys and values as tvalues.

Some examples:

# select 'text: "a"'::tvalue;
tvalue
----------
text: "a"
(1 row)

# select 'integer: 17'::tvalue;
tvalue
----------
int4: 17
(1 row)

# select each_int( '"a" -> text: "b", "b" -> int: 17'::tstore );
?column? | ?column?
----------+----------
"b" | 17
(1 row)

#select '"a" -> text: "b", "b" -> int: 17'::tstore -> 'a';
?column?
-----------
text: "a"
(1 row)

All comments are welcome. As the project matures, I'm sure other
needed functions will crop up.

--
Johann Oskarsson http://www.2ndquadrant.com/ |[]
PostgreSQL Development, 24x7 Support, Training and Services --+--
|
Blog: http://my.opera.com/myrkraverk/blog/

On Wed, Dec 21, 2011 at 8:32 PM, Johann 'Myrkraverk' Oskarsson
<johann(at)2ndquadrant(dot)com> wrote:
> I mean to create a typed hstore, called tstore for now.  I'm open to
> name suggestions.  It'll only support a subset of core Postgres types
> to begin with.  Keys are always text, it's the value that's typed.

Unfortunately, I'm not sure it'll be of much interest unless it heads
all the way to having nested data.

JSON is the "thing of the day" that it would be desirable for this to
be potent enough to represent, and JSON has the following types:

1. Number (in practice, FLOAT)
2. String (UTF-8)
3. Boolean (t/f)
4. Array (not necessarily of uniform type
5. Object (string key, JSON value pairs, unordered)
6. NULL

#4 and #5 are obviously entirely more "hairy."

But it seems pretty likely that people would be keen on additional
implementations until they get those.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Christopher Browne <cbbrowne(at)gmail(dot)com> writes:

> On Wed, Dec 21, 2011 at 8:32 PM, Johann 'Myrkraverk' Oskarsson
> <johann(at)2ndquadrant(dot)com> wrote:
>> I mean to create a typed hstore, called tstore for now.  I'm open
>> to name suggestions.  It'll only support a subset of core Postgres
>> types to begin with.  Keys are always text, it's the value that's
>> typed.

> JSON is the "thing of the day" that it would be desirable for this to
> be potent enough to represent, and JSON has the following types:
>
> 1. Number (in practice, FLOAT)
> 2. String (UTF-8)
> 3. Boolean (t/f)
> 4. Array (not necessarily of uniform type
> 5. Object (string key, JSON value pairs, unordered)
> 6. NULL
>
> #4 and #5 are obviously entirely more "hairy."

Not so much if I extend tstore to include itself.

Unless I'm mistaking the Object type. I'm not a user of JSON myself.
How are people likely to use it with it?

--
Johann Oskarsson http://www.2ndquadrant.com/ |[]
PostgreSQL Development, 24x7 Support, Training and Services --+--
|
Blog: http://my.opera.com/myrkraverk/blog/

"Johann 'Myrkraverk' Oskarsson" <johann(at)2ndquadrant(dot)com> writes:
> I mean to create a typed hstore, called tstore for now.

Um ... what is the point of this, exactly? From what I've seen, most
applications for hstore are pretty happy with the fact that hstore is
only weakly typed, and if an entry *is* an integer, or a float, or
whatever else, it's not hard to cast to and from text as needed.
So this idea looks like a solution in search of a problem, which is
going to need a whole lot more work before it even gets to the point of
being as useful as hstore. It's not for instance apparent what is the
use of iterating over only entries that were supplied as integers ---
there is no reason to think that they're related just because of that.

regards, tom lane

On 22/12/11 10:44, Tom Lane wrote:
> "Johann 'Myrkraverk' Oskarsson" <johann(at)2ndquadrant(dot)com> writes:
> > I mean to create a typed hstore, called tstore for now.
>
> Um ... what is the point of this, exactly? From what I've seen, most
> applications for hstore are pretty happy with the fact that hstore is
> only weakly typed, and if an entry *is* an integer, or a float, or
> whatever else, it's not hard to cast to and from text as needed.

More over it is also easy with the current hstore to add constraints like this:

"contracts_is_an_integer" CHECK ((tags -> 'contracts'::text) ~ '^[0-9]+$'::text)

to ensure that it actually is.

On 12/22/2011 10:44 AM, Tom Lane wrote:
> "Johann 'Myrkraverk' Oskarsson"<johann(at)2ndquadrant(dot)com> writes:
>> I mean to create a typed hstore, called tstore for now.
> Um ... what is the point of this, exactly? From what I've seen, most
> applications for hstore are pretty happy with the fact that hstore is
> only weakly typed, and if an entry *is* an integer, or a float, or
> whatever else, it's not hard to cast to and from text as needed.
> So this idea looks like a solution in search of a problem, which is
> going to need a whole lot more work before it even gets to the point of
> being as useful as hstore. It's not for instance apparent what is the
> use of iterating over only entries that were supplied as integers ---
> there is no reason to think that they're related just because of that.
>
>

Yeah, the thing that's annoying in some cases about hstore is not that
it's untyped but that it's flat.

That's what a JSON type would buy us, a lightweight tree structured
type, and moreover one that is widely and increasingly used and well
understood.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:

> On 12/22/2011 10:44 AM, Tom Lane wrote:
>> "Johann 'Myrkraverk' Oskarsson"<johann(at)2ndquadrant(dot)com> writes:
>>> I mean to create a typed hstore, called tstore for now.
>> Um ... what is the point of this, exactly? From what I've seen,
>> most applications for hstore are pretty happy with the fact that
>> hstore is only weakly typed, and if an entry *is* an integer, or a
>> float, or whatever else, it's not hard to cast to and from text as
>> needed. So this idea looks like a solution in search of a problem,
>> which is going to need a whole lot more work before it even gets to
>> the point of being as useful as hstore. It's not for instance
>> apparent what is the use of iterating over only entries that were
>> supplied as integers --- there is no reason to think that they're
>> related just because of that.

No, which is why each( tstore ) returns the whole thing, as tvalues.

Also, it can be quite helpful in some cases to ask "what is the type
of this key" rather than cast to an integer and hope it works.

Typed in this case means each value is typed. There are (as yet) no
reason nor facility to add type constraints for a given key within the
implementation itself.

> Yeah, the thing that's annoying in some cases about hstore is not
> that it's untyped but that it's flat.

As I already pointed out (well, implied) is that it's trivial to allow
tstore to be recursive.

> That's what a JSON type would buy us, a lightweight tree structured
> type, and moreover one that is widely and increasingly used and well
> understood.

While I have not meant tstore to be a JSON type, it's not hard to make
it fully compatible with JSON by providing such input/output
functions.

Here it's noteworthy that I mean tstore to be richer than JSON. Some
type ideas:

* boolean

* bytea

* float4

* float8

* int2

* int4

* int8

* null (or some provision to have unvalued keys)

Not all of the above may be supported by the first implementation.
Notably bytea may be skipped.

And later on, possibly some subset or all of the time types:

* timestamp with time zone

* timestamp without time zone

* interval

* date

* time with time zone

* time without time zone

For JSON compatibility and tree structures:

* tstore (nested)

* tvalue arrays (or another way to have JSON compatible arrays)

It might also be worthwhile to have a specific JSON type, possibly
using the same underlying structure just with different input/output
functions.

--
Johann Oskarsson http://www.2ndquadrant.com/ |[]
PostgreSQL Development, 24x7 Support, Training and Services --+--
|
Blog: http://my.opera.com/myrkraverk/blog/