BEGIN;

SET client_min_messages = warning;
\pset format unaligned
\pset tuples_only true
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true


CREATE TABLE try (
    id SERIAL PRIMARY KEY,
    myhash HSTORE NOT NULL
);

CREATE INDEX h1idx ON try USING GIST(myhash);
CREATE INDEX h2idx ON try USING GIN(myhash);
  

INSERT INTO try (myhash)
VALUES ('foo => bar, baz => 2.2, "u 2" => "some band"' ),
       ('larry => 48, damian => 50, tom => 42')
;

SELECT myhash -> 'foo' = 'bar' FROM try WHERE id = 1;
SELECT myhash -> ARRAY['foo', 'baz'] = ARRAY['bar', '2.2'] FROM try WHERE id = 1;
SELECT myhash -> 'u 2' = 'some band' FROM try WHERE id = 1;
SELECT 'a' => 'b' = '"a"=>"b"'::hstore;
SELECT ARRAY['a', 'c'] => ARRAY['b', 'd'] = '"a"=>"b",c => d'::hstore;
SELECT 'a=>1,b=>2,c=>3'::hstore => ARRAY['b','c'] = 'b=>2,c=>3'::hstore;
SELECT (myhash || ('a' => 'b'))::text = '"a"=>"b", "tom"=>"42", "larry"=>"48", "damian"=>"50"' FROM try WHERE id = 2;
SELECT myhash ? 'tom' FROM try WHERE id = 2;
SELECT myhash ?& ARRAY['tom', 'larry'] FROM try WHERE id = 2;
SELECT myhash ?& ARRAY['tom', 'larry', 'bob'] = false FROM try WHERE id = 2;
SELECT myhash ?| ARRAY['tom', 'larry', 'bob'] FROM try WHERE id = 2;
SELECT myhash ?| ARRAY['tod', 'jerry', 'bob'] = false FROM try WHERE id = 2;
SELECT 'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1';
SELECT 'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL' = false;
select akeys('a=>1,b=>2') = ARRAY['a', 'b'];
select ARRAY( SELECT skeys('a=>1,b=>2')) = ARRAY['a', 'b'];
select avals('a=>1,b=>2') = ARRAY['1', '2'];
select ARRAY( SELECT svals('a=>1,b=>2')) = ARRAY['1', '2'];

select * from each('a=>1,b=>2');
select exist('a=>1','a');
select defined('a=>NULL','a') = false;
select delete('a=>1,b=>2','b') = '"a"=>"1"'::HSTORE;
SELECT 'a=>1, b =>2'::hstore - 'a=>1' = 'b=>2';
SELECT 'a=>1, b =>2'::hstore - 'a=>2' = 'a=>1,b=>2';
SELECT 'a=>1, b =>2'::hstore - 'a'::text = 'b=>2';
SELECT 'a=>1, b =>2,c=>3'::hstore - ARRAY['a','c'] = 'b=>2';

create table testhstore1 (a integer, b text, c numeric, d float8);
insert into testhstore1 values (1, 'foo', 1.2, 3::float8);
select v #= ('c' => '3.45') = row(1::int, 'foo'::text, 3.45::numeric, 3::float8) from testhstore1 v;
SELECT hstore(v) = 'a=>1,b=>foo,c=>1.2,d=>3'::hstore FROM testhstore1 v;
select populate_record(v, ('c' => '3.45')) = row(1::int, 'foo'::text, 3.45::numeric, 3::float8) from testhstore1 v;

UPDATE try SET myhash = myhash || ('josh' => '37') WHERE id = 2;
SELECT myhash -> 'josh' = '37' FROM try WHERE id = 2;

SELECT id, (each(myhash)).key, (each(myhash)).value FROM try;

INSERT INTO try (myhash)
VALUES ('larry => 10, damian => 22');


SELECT key, count(*) FROM
  (SELECT (each(myhash)).key FROM try) AS stat
  GROUP BY key
  ORDER BY count DESC, key;

--FAILURES.
SELECT 'a' => 'b' = 'a => b'::hstore;
SELECT pg_typeof('a' => 'b') = 'hstore'::regtype;

ROLLBACK;
