#!/bin/env perl
use strict;
use warnings;
use DBI; 

  my $dbh = DBI->connect("dbi:Pg:",undef,undef, { AutoCommit=>0, RaiseError=>1, PrintError=>0, } ) or die "oops - $!\n";

  $dbh->{pg_placeholder_dollaronly} = 1;

  my @sql   = (); my @expr  = (); my @sql2 = (); 

  # Table F-6. hstore Operators
  #  
  # Page 2592 (pdf)

  push(@expr, {
				  operator    => "'hstore -> text'"
				, returns     => "'text'"
                , description => "'get value for key (NULL if not present)'"
                , expression  => "'a=>x,b=>y'::hstore -> 'a'"
                , result      => "'x'"
  });

  ######## END OF PAGE 2592 (A4 pdf) ##############################3
  push(@expr, ''); # empty line = new page (pdf)

  push(@expr, {
				  operator    => "'hstore -> integer'"
				, returns     => "'text'"
                , description => "'get value for array index (NULL if not present)'"
                , expression  => "'[foo,bar,baz]'::hstore -> 1"
                , result      => "'bar'"
  });
  push(@expr, {
				  operator    => "'hstore ^> text'"
				, returns     => "'numeric'"
                , description => "'get numeric value for key (NULL if not numeric or not present)'"
                , expression  => "'a=>42.0,b=>y'::hstore ^> 'a'"
                , result      => "'42.0'"
  });
  push(@expr, {
				  operator    => "'hstore ^> integer'"
				, returns     => "'numeric'"
                , description => "'get numeric value for array index (NULL if not numeric or not present)'"
                , expression  => "'[foo,null,44]'::hstore ^> 2"
                , result      => "'44'"
  });
  push(@expr, {
				  operator    => "'hstore ?> text'"
				, returns     => "'boolean'"
                , description => "'get boolean value for key (NULL if not boolean or not present)'"
                , expression  => "'a => 42.0, b => true'::hstore ?> 'b'"
                , result      => "'t'"
  });
  push(@expr, {
				  operator    => "'hstore ?> integer'"
				, returns     => "'boolean'"
                , description => "'get boolean value for array index (NULL if not boolean or not present)'"
                , expression  => "'[false,null,44]'::hstore ?> 0"
                , result      => "'f'"
  });
  push(@expr, {
				  operator    => "'hstore #> text[]'"
				, returns     => "'text'"
                , description => "'get value for key path (NULL if not present)'"
                , expression  => "'foo => {bar => yellow}'::hstore #> '{foo,bar}'"
                , result      => "'yellow'"
  });
  push(@expr, {
				  operator    => "'hstore #^> text[]'"
				, returns     => "'numeric'"
                , description => "'get numeric value for key path (NULL if not numeric or not present)'"
                , expression  => "'foo => {bar => 99}'::hstore #^> '{foo,bar}'"
                , result      => "'99'"
  });
  push(@expr, {
				  operator    => "'hstore #?> text[]'"
				, returns     => "'boolean'"
                , description => "'get boolean value for key path (NULL if not boolean or not present)'"
                , expression  => "'foo => {bar => true}'::hstore #?> '{foo,bar}'"
                , result      => "'t'"
  });
  push(@expr, {
				  operator    => "'hstore %> text'"
				, returns     => "'hstore'"
                , description => "'get hstore value for key (NULL if not present)'"
                , expression  => "'foo => {bar => 99}'::hstore %> 'foo'"
                , result      => "'\"bar\"=>99'"
  });
  push(@expr, {
				  operator    => "'hstore %> integer'"
				, returns     => "'hstore'"
                , description => "'get hstore value array index (NULL if not present)'"
                , expression  => "'[1, 2, {foo=>hi}]'::hstore %> 2"
                , result      => "'\"foo\"=>\"hi\"'"
  });
  push(@expr, {
				  operator    => "'hstore #%> text[]'"
				, returns     => "'hstore'"
                , description => "'get hstore value for key path (NULL if not present)'"
                , expression  => "'a => 1, b =>{c =>[44,44]}'::hstore #%> '{b,c}'"
                , result      => "'[44, 44]'"
  });

  ######## END OF PAGE 2593 ##############################3
  push(@expr, '');

  push(@expr, {
				  operator    => "'hstore -> text[]'"
				, returns     => "'text[]'"
                , description => "'get values for keys (NULL if not present)'"
                , expression  => "'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']"
                , result      => "'{\"z\",\"x\"}'"
  });
  push(@expr, {
				  operator    => "'hstore || hstore'"
				, returns     => "'hstore'"
                , description => "'concatenate hstores'"
                , expression  => "'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore"
                , result      => "'\"a\"=>\"b\", \"c\"=>\"x\", \"d\"=>\"q\"'"
  });
  push(@expr, {
				  operator    => "'hstore ? text'"
				, returns     => "'boolean'"
                , description => "'does hstore contain key?'"
                , expression  => "'a=>1'::hstore ? 'a'"
                , result      => "'t'"
  });
  push(@expr, {
				  operator    => "'hstore ?  integer'"
				, returns     => "'boolean'"
                , description => "'does hstore contain array index?'"
                , expression  => "'[a,b,c]'::hstore ? 2"
                , result      => "'t'"
  });
  push(@expr, {
				  operator    => "'hstore #? text[]'"
				, returns     => "'boolean'"
                , description => "'does hstore contain key path?'"
                , expression  => "'[1, 2, {foo=>hi}]'::hstore #? '{2, foo}'"
                , result      => "'t'"
  });
  push(@expr, {
				  operator    => "'hstore ?& text[]'"
				, returns     => "'boolean'"
                , description => "'does hstore contain all specified keys?'"
                , expression  => "'a=>1,b=>2'::hstore ?& ARRAY['a','b']"
                , result      => "'t'"
  });
  push(@expr, {
				  operator    => "'hstore ?| text[]'"
				, returns     => "'boolean'"
                , description => "'does hstore contain any of the specified keys?'"
                , expression  => "'a=>1,b=>2'::hstore ?| ARRAY['b','c']"
                , result      => "'t'"
  });
  push(@expr, {
				  operator    => "'hstore @> hstore'"
				, returns     => "'boolean'"
                , description => "'does left operand contain right?'"
                , expression  => "'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'"
                , result      => "'t'"
  });
  push(@expr, {
				  operator    => "'hstore <@ hstore'"
				, returns     => "'boolean'"
                , description => "'is left operand contained in right?'"
                , expression  => "'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'"
                , result      => "'f'"
  });
  push(@expr, {
				  operator    => "'hstore - text'"
				, returns     => "'hstore'"
                , description => "'delete key from left operand'"
                , expression  => "'a=>1, b=>2, c=>3'::hstore - 'b'::text"
                , result      => "'\"a\"=>\"1\", \"c\"=>\"3\"'"
  });
  push(@expr, {
				  operator    => "'hstore - integer'"
				, returns     => "'hstore'"
                , description => "'delete index from left operand'"
                , expression  => "'[2, 3, 4, 6, 8]'::hstore - 1"
                , result      => "'[2, 4, 6, 8]'"
  });
  push(@expr, {
				  operator    => "'hstore - text[]'"
				, returns     => "'hstore'"
                , description => "'delete keys from left operand'" 
                , expression  => "'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']"
                , result      => "'\"c\"=>\"3\"'"
  });
  push(@expr, {
				  operator    => "'hstore - hstore'"
				, returns     => "'hstore'"
                , description => "'delete matching pairs from left operand'"
                , expression  => "'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore"
                , result      => "'\"a\"=>\"1\", \"c\"=>\"3\"'"
  });

  ######## END OF PAGE 2594 ##############################3
  push(@expr, '');

  push(@expr, {
				  operator    => "'hstore #- text[]'"
				, returns     => "'hstore'"
                , description => "'delete key path from left operand'"
                , expression  => "'{a => {b => { c => [1,2]}}}'::hstore #- '{a,b,c,0}'"
                , result      => "'\"a\"=>{\"b\"=>{\"c\"=>[2]}}'"
  });

  # skipped one:   record #= hstore  as it says "See Examples" 
  #
  push(@expr, {
				  operator    => "'%% hstore'"
				, returns     => "'text[]'"
                , description => "'convert hstore to array of alternating keys and values'"
                , expression  => "%% 'a=>foo, b=>bar'::hstore"
                , result      => "'{a,foo,b,bar}'"
  });
  push(@expr, {
				  operator    => "'%# hstore'"
				, returns     => "'text[]'"
                , description => "'convert hstore to two-dimensional key/value array'"
                , expression  => "%# 'a=>foo, b=>bar'::hstore"
                , result      => "'{{a,foo},{b,bar}}'"
  });

# '{a => {b => { c => [1,2]}}}'::hstore #- '[a,b,c,0]'	
# "a"=>{"b"=>{"c"=>[2]}}

my $null = << 'DUMP';

# record #= hstore
# record
# replace fields in record with matching values from hstore 
# see Examples section

DUMP


  for(my $n=0;$n<scalar( @expr );$n++) {
      if ($expr[$n] eq "") {
          next;
      }
      my $expr = $expr[$n]->{ expression };
      $expr[$n]->{ result2 } = $dbh->selectrow_arrayref( "select cast(" . $expr[$n]->{ expression } . " as text);\n" )->[0]; 
      if (0) {
	      print $expr, "   ";
	      print
				(  defined $expr[$n]->{ result2 }
					? $expr[$n]->{ result2 }
	            	: 'NULL' 
				) ;
		  print "\n";
	  }
  }

  for(my $n=0;$n<scalar( @expr );$n++) {
      if ($expr[$n] eq "") {
          push(@sql, "select null, null, null, null, null, null");  #  empty line: page break in .pdf
          next;
      }
	  push(@sql, "select\n    " . join("\n  ,  " 
	       ,       $expr[$n]->{ operator    }         . ' as operator' 
	       ,       $expr[$n]->{ returns     }         . ' as returns'
	       ,  "E'\Q$expr[$n]->{ expression  }\E'"     . ' as expression'
	       ,       $expr[$n]->{ result      }         . ' as result_in_docs'
	       , "'" . $expr[$n]->{ result2     } . "'"   . ' as actual_fetch'  # actually really fetched
	       ,                   $expr[$n]->{ description }                     . ' as description'
	    #  ,    "substring(" . $expr[$n]->{ description } . ", 1, 50)"        . ' as description'
	  ));
      push(@sql2, "select " . $expr[$n]->{ expression } . ";\n" );
  }
  print join("\n union all ", @sql), ";\n";

# for(my $n=0;$n<scalar( @sql2 );$n++) { print $sql2[$n]; }