-- first some tests of basic functionality
--
-- better succeed
--
select stupid();
 stupid 
--------
 zarkon
(1 row)

-- check static and global data
--
SELECT static_test();
 static_test 
-------------
           1
(1 row)

SELECT static_test();
 static_test 
-------------
           2
(1 row)

SELECT global_test_one();
                    global_test_one                     
--------------------------------------------------------
 SD: set by global_test_one, GD: set by global_test_one
(1 row)

SELECT global_test_two();
                    global_test_two                     
--------------------------------------------------------
 SD: set by global_test_two, GD: set by global_test_one
(1 row)

-- import python modules
--
SELECT import_fail();
NOTICE:  ('import socket failed -- No module named foosocket',)
    import_fail     
--------------------
 failed as expected
(1 row)

SELECT import_succeed();
     import_succeed     
------------------------
 succeeded, as expected
(1 row)

-- test import and simple argument handling
--
SELECT import_test_one('sha hash of this string');
             import_test_one              
------------------------------------------
 a04e23cb9b1a09cd1051a04a7c571aae0f90346c
(1 row)

-- test import and tuple argument handling
--
select import_test_two(users) from users where fname = 'willem';
                          import_test_two                          
-------------------------------------------------------------------
 sha hash of willemdoe is 3cde6b574953b0ca937b4d76ebc40d534d910759
(1 row)

-- test multiple arguments
--
select argument_test_one(users, fname, lname) from users where lname = 'doe' order by 1;
                           argument_test_one                           
-----------------------------------------------------------------------
 jane doe => {fname: jane, lname: doe, userid: 1, username: j_doe}
 john doe => {fname: john, lname: doe, userid: 2, username: johnd}
 willem doe => {fname: willem, lname: doe, userid: 3, username: w_doe}
(3 rows)

-- spi and nested calls
--
select nested_call_one('pass this along');
                         nested_call_one                         
-----------------------------------------------------------------
 {'nested_call_two': "{'nested_call_three': 'pass this along'}"}
(1 row)

select spi_prepared_plan_test_one('doe');
 spi_prepared_plan_test_one 
----------------------------
 there are 3 does
(1 row)

select spi_prepared_plan_test_one('smith');
 spi_prepared_plan_test_one 
----------------------------
 there are 1 smiths
(1 row)

select spi_prepared_plan_test_nested('smith');
 spi_prepared_plan_test_nested 
-------------------------------
 there are 1 smiths
(1 row)

-- quick peek at the table
--
SELECT * FROM users;
 fname  | lname | username | userid 
--------+-------+----------+--------
 jane   | doe   | j_doe    |      1
 john   | doe   | johnd    |      2
 willem | doe   | w_doe    |      3
 rick   | smith | slash    |      4
(4 rows)

-- should fail
--
UPDATE users SET fname = 'william' WHERE fname = 'willem';
-- should modify william to willem and create username
--
INSERT INTO users (fname, lname) VALUES ('william', 'smith');
INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle');
SELECT * FROM users;
  fname  | lname  | username | userid 
---------+--------+----------+--------
 jane    | doe    | j_doe    |      1
 john    | doe    | johnd    |      2
 willem  | doe    | w_doe    |      3
 rick    | smith  | slash    |      4
 willem  | smith  | w_smith  |      5
 charles | darwin | beagle   |      6
(6 rows)

SELECT join_sequences(sequences) FROM sequences;
 join_sequences 
----------------
 ABCDEFGHIJKL
 ABCDEF
 ABCDEF
 ABCDEF
 ABCDEF
 ABCDEF
(6 rows)

SELECT join_sequences(sequences) FROM sequences
	WHERE join_sequences(sequences) ~* '^A';
 join_sequences 
----------------
 ABCDEFGHIJKL
 ABCDEF
 ABCDEF
 ABCDEF
 ABCDEF
 ABCDEF
(6 rows)

SELECT join_sequences(sequences) FROM sequences
	WHERE join_sequences(sequences) ~* '^B';
 join_sequences 
----------------
(0 rows)

-- error in trigger
--
--
-- Check Universal Newline Support
--
SELECT newline_lf();
 newline_lf 
------------
        123
(1 row)

SELECT newline_cr();
 newline_cr 
------------
        123
(1 row)

SELECT newline_crlf();
 newline_crlf 
--------------
          123
(1 row)

-- Tests for functions returning void
SELECT test_void_func1(), test_void_func1() IS NULL AS "is null";
 test_void_func1 | is null 
-----------------+---------
                 | f
(1 row)

SELECT test_void_func2(); -- should fail
ERROR:  PL/Python function with return type "void" did not return None
SELECT test_return_none(), test_return_none() IS NULL AS "is null";
 test_return_none | is null 
------------------+---------
                  | t
(1 row)

-- Test for functions with named parameters
SELECT test_param_names1(1,'text');
 test_param_names1 
-------------------
 t
(1 row)

SELECT test_param_names2(users) from users;
                             test_param_names2                              
----------------------------------------------------------------------------
 {'lname': 'doe', 'username': 'j_doe', 'userid': 1, 'fname': 'jane'}
 {'lname': 'doe', 'username': 'johnd', 'userid': 2, 'fname': 'john'}
 {'lname': 'doe', 'username': 'w_doe', 'userid': 3, 'fname': 'willem'}
 {'lname': 'smith', 'username': 'slash', 'userid': 4, 'fname': 'rick'}
 {'lname': 'smith', 'username': 'w_smith', 'userid': 5, 'fname': 'willem'}
 {'lname': 'darwin', 'username': 'beagle', 'userid': 6, 'fname': 'charles'}
(6 rows)

SELECT test_param_names3(1);
 test_param_names3 
-------------------
 t
(1 row)

-- Test set returning functions
SELECT test_setof_as_list(0, 'list');
 test_setof_as_list 
--------------------
(0 rows)

SELECT test_setof_as_list(1, 'list');
 test_setof_as_list 
--------------------
 list
(1 row)

SELECT test_setof_as_list(2, 'list');
 test_setof_as_list 
--------------------
 list
 list
(2 rows)

SELECT test_setof_as_list(2, null);
 test_setof_as_list 
--------------------
 
 
(2 rows)

SELECT test_setof_as_tuple(0, 'tuple');
 test_setof_as_tuple 
---------------------
(0 rows)

SELECT test_setof_as_tuple(1, 'tuple');
 test_setof_as_tuple 
---------------------
 tuple
(1 row)

SELECT test_setof_as_tuple(2, 'tuple');
 test_setof_as_tuple 
---------------------
 tuple
 tuple
(2 rows)

SELECT test_setof_as_tuple(2, null);
 test_setof_as_tuple 
---------------------
 
 
(2 rows)

SELECT test_setof_as_iterator(0, 'list');
 test_setof_as_iterator 
------------------------
(0 rows)

SELECT test_setof_as_iterator(1, 'list');
 test_setof_as_iterator 
------------------------
 list
(1 row)

SELECT test_setof_as_iterator(2, 'list');
 test_setof_as_iterator 
------------------------
 list
 list
(2 rows)

SELECT test_setof_as_iterator(2, null);
 test_setof_as_iterator 
------------------------
 
 
(2 rows)

-- Test tuple returning functions
SELECT * FROM test_table_record_as('dict', null, null, false);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_table_record_as('dict', 'one', null, false);
 first | second 
-------+--------
 one   |       
(1 row)

SELECT * FROM test_table_record_as('dict', null, 2, false);
 first | second 
-------+--------
       |      2
(1 row)

SELECT * FROM test_table_record_as('dict', 'three', 3, false);
 first | second 
-------+--------
 three |      3
(1 row)

SELECT * FROM test_table_record_as('dict', null, null, true);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_table_record_as('tuple', null, null, false);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_table_record_as('tuple', 'one', null, false);
 first | second 
-------+--------
 one   |       
(1 row)

SELECT * FROM test_table_record_as('tuple', null, 2, false);
 first | second 
-------+--------
       |      2
(1 row)

SELECT * FROM test_table_record_as('tuple', 'three', 3, false);
 first | second 
-------+--------
 three |      3
(1 row)

SELECT * FROM test_table_record_as('tuple', null, null, true);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_table_record_as('list', null, null, false);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_table_record_as('list', 'one', null, false);
 first | second 
-------+--------
 one   |       
(1 row)

SELECT * FROM test_table_record_as('list', null, 2, false);
 first | second 
-------+--------
       |      2
(1 row)

SELECT * FROM test_table_record_as('list', 'three', 3, false);
 first | second 
-------+--------
 three |      3
(1 row)

SELECT * FROM test_table_record_as('list', null, null, true);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_table_record_as('obj', null, null, false);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_table_record_as('obj', 'one', null, false);
 first | second 
-------+--------
 one   |       
(1 row)

SELECT * FROM test_table_record_as('obj', null, 2, false);
 first | second 
-------+--------
       |      2
(1 row)

SELECT * FROM test_table_record_as('obj', 'three', 3, false);
 first | second 
-------+--------
 three |      3
(1 row)

SELECT * FROM test_table_record_as('obj', null, null, true);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_type_record_as('dict', null, null, false);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_type_record_as('dict', 'one', null, false);
 first | second 
-------+--------
 one   |       
(1 row)

SELECT * FROM test_type_record_as('dict', null, 2, false);
 first | second 
-------+--------
       |      2
(1 row)

SELECT * FROM test_type_record_as('dict', 'three', 3, false);
 first | second 
-------+--------
 three |      3
(1 row)

SELECT * FROM test_type_record_as('dict', null, null, true);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_type_record_as('tuple', null, null, false);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_type_record_as('tuple', 'one', null, false);
 first | second 
-------+--------
 one   |       
(1 row)

SELECT * FROM test_type_record_as('tuple', null, 2, false);
 first | second 
-------+--------
       |      2
(1 row)

SELECT * FROM test_type_record_as('tuple', 'three', 3, false);
 first | second 
-------+--------
 three |      3
(1 row)

SELECT * FROM test_type_record_as('tuple', null, null, true);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_type_record_as('list', null, null, false);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_type_record_as('list', 'one', null, false);
 first | second 
-------+--------
 one   |       
(1 row)

SELECT * FROM test_type_record_as('list', null, 2, false);
 first | second 
-------+--------
       |      2
(1 row)

SELECT * FROM test_type_record_as('list', 'three', 3, false);
 first | second 
-------+--------
 three |      3
(1 row)

SELECT * FROM test_type_record_as('list', null, null, true);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_type_record_as('obj', null, null, false);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_type_record_as('obj', 'one', null, false);
 first | second 
-------+--------
 one   |       
(1 row)

SELECT * FROM test_type_record_as('obj', null, 2, false);
 first | second 
-------+--------
       |      2
(1 row)

SELECT * FROM test_type_record_as('obj', 'three', 3, false);
 first | second 
-------+--------
 three |      3
(1 row)

SELECT * FROM test_type_record_as('obj', null, null, true);
 first | second 
-------+--------
       |       
(1 row)

SELECT * FROM test_in_out_params('test_in');
      second       
-------------------
 test_in_in_to_out
(1 row)

-- this doesn't work yet :-(
SELECT * FROM test_in_out_params_multi('test_in');
ERROR:  PL/Python functions cannot return type record
SELECT * FROM test_inout_params('test_in');
     first     
---------------
 test_in_inout
(1 row)

