CREATE LANGUAGE plperl;
CREATE SCHEMA tools;
\set ECHO none
select tools.session__set('one', 'a');
 session__set 
--------------
 
(1 row)

select tools.session__get('one');
 session__get 
--------------
 a
(1 row)

-- fail, 'a' is not a hash reference --
select tools.session__set('one', 'a', 'b');
ERROR:  error from Perl function: %_SHARED{one} is not a hash reference at line 9.
-- clear the hash --
select tools.session__set('one', NULL);
 session__set 
--------------
 
(1 row)

-- fail, no such value in a hash
select tools.session__get('one');
 session__get 
--------------
 
(1 row)

select tools.session__set('one', 'I', 'a');
 session__set 
--------------
 
(1 row)

-- fail, the value is a hash reference --
select tools.session__get('one');
ERROR:  error from Perl function: $_SHARED{one} is a reference at line 7.
select tools.session__get('one', 'I');
 session__get 
--------------
 a
(1 row)

select tools.session__set('one', 'I', 'b');
 session__set 
--------------
 
(1 row)

select tools.session__set('one', 'II', 'c');
 session__set 
--------------
 
(1 row)

select tools.session__get('one', 'I');
 session__get 
--------------
 b
(1 row)

select tools.session__get('one', 'II');
 session__get 
--------------
 c
(1 row)

-- fail, no such key --
select tools.session__get('one', 'III');
 session__get 
--------------
 
(1 row)

-- clear the 'one/I' element --
select tools.session__set('one', 'I', NULL);
 session__set 
--------------
 
(1 row)

-- fail, no such key --
select tools.session__get('one', 'I');
 session__get 
--------------
 
(1 row)

-- clear the 'one' hash --
select tools.session__set('one', NULL);
 session__set 
--------------
 
(1 row)

-- fail, no such key --
select tools.session__get('one', 'II');
 session__get 
--------------
 
(1 row)

-- populate the hash --
select tools.session__set('one', 'I', 'a');
 session__set 
--------------
 
(1 row)

select tools.session__set('one', 'II', 'b');
 session__set 
--------------
 
(1 row)

select tools.session__set('two', 'II');
 session__set 
--------------
 
(1 row)

select tools.session__set('three', 'III');
 session__set 
--------------
 
(1 row)

select tools.session__set('four', 'IV');
 session__set 
--------------
 
(1 row)

select tools.session__set('five', 'V');
 session__set 
--------------
 
(1 row)

select tools.session__set('six', 'VI');
 session__set 
--------------
 
(1 row)

select tools.session__set('seven', 'VII', 'a');
 session__set 
--------------
 
(1 row)

select tools.session__set('seven', 'VIII', 'b');
 session__set 
--------------
 
(1 row)

-- clear --
select tools.session__clean('delete', 'one, two, three');
 session__clean 
----------------
 
(1 row)

select tools.session__list();
  session__list   
------------------
 five => V
 four => IV
 seven, VII => a
 seven, VIII => b
 six => VI
 
(1 row)

select tools.session__clean('keep', 'six, seven');
 session__clean 
----------------
 
(1 row)

select tools.session__list();
  session__list   
------------------
 seven, VII => a
 seven, VIII => b
 six => VI
 
(1 row)

select tools.session__set('first', 'second', 'third', 'forth');
 session__set 
--------------
 
(1 row)

select tools.session__get('first', 'second', 'third');
 session__get 
--------------
 forth
(1 row)

-- fail, the value is a hash reference
select tools.session__set('first', 'second', '5');
ERROR:  error from Perl function: %_SHARED{first}{second} is a reference at line 20.
-- fail, the value is a hash reference
select tools.session__get('first');
ERROR:  error from Perl function: $_SHARED{first} is a reference at line 7.
-- fail, the value is a hash reference
select tools.session__get('first', 'second');
ERROR:  error from Perl function: %_SHARED{first}{second} is a reference at line 11.
select tools.session__set('first', 'fifth', 'sixth', 'seventh');
 session__set 
--------------
 
(1 row)

select tools.session__get('first', 'fifth', 'sixth');
 session__get 
--------------
 seventh
(1 row)

select tools.session__list();
         session__list          
--------------------------------
 first, fifth, sixth => seventh
 first, second, third => forth
 seven, VII => a
 seven, VIII => b
 six => VI
 
(1 row)

select tools.session__set('first', 'fifth', 'sixth', NULL);
 session__set 
--------------
 
(1 row)

select tools.session__list();
         session__list         
-------------------------------
 first, second, third => forth
 seven, VII => a
 seven, VIII => b
 six => VI
 
(1 row)

select tools.session__set('first', 'fifth', 'sixth', 'eighth');
 session__set 
--------------
 
(1 row)

select tools.session__list();
         session__list         
-------------------------------
 first, fifth, sixth => eighth
 first, second, third => forth
 seven, VII => a
 seven, VIII => b
 six => VI
 
(1 row)

select tools.session__clean('delete', 'first');
 session__clean 
----------------
 
(1 row)

select tools.session__list();
  session__list   
------------------
 seven, VII => a
 seven, VIII => b
 six => VI
 
(1 row)

-- remove all keys form the hash --
select tools.session__reset();
 session__reset 
----------------
 
(1 row)

-- make sure it's empty --
select tools.session__list();
 session__list 
---------------
 
(1 row)

DROP SCHEMA tools CASCADE;
NOTICE:  drop cascades to function tools.session__reset()
NOTICE:  drop cascades to function tools.session__list()
NOTICE:  drop cascades to function tools.session__clean(text,text)
NOTICE:  drop cascades to function tools.session__get(text,text,text)
NOTICE:  drop cascades to function tools.session__get(text,text)
NOTICE:  drop cascades to function tools.session__get(text)
NOTICE:  drop cascades to function tools.session__set(text,text,text,text)
NOTICE:  drop cascades to function tools.session__set(text,text,text)
NOTICE:  drop cascades to function tools.session__set(text,text)
DROP LANGUAGE plperl CASCADE;