-- 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
CONTEXT:  PL/Python function "import_fail"
    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 order by userid;
 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 order by userid;
  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)

-- Greenplum doesn't support functions that execute SQL from segments
--
--  SELECT join_sequences(sequences) FROM sequences;
--  SELECT join_sequences(sequences) FROM sequences
--  	WHERE join_sequences(sequences) ~* '^A';
--  SELECT join_sequences(sequences) FROM sequences
--  	WHERE join_sequences(sequences) ~* '^B';
SELECT result_nrows_test();
INFO:  True
CONTEXT:  PL/Python function "result_nrows_test"
 result_nrows_test 
-------------------
                 2
(1 row)

-- test of exception handling
SELECT queryexec('SELECT 2'); 
NOTICE:  {'?column?': 2}
CONTEXT:  PL/Python function "queryexec"
 queryexec 
-----------
 t
(1 row)

SELECT queryexec('SELECT X'); 
NOTICE:  Error Trapped
CONTEXT:  PL/Python function "queryexec"
 queryexec 
-----------
 f
(1 row)

select module_contents();
                                                     module_contents                                                      
--------------------------------------------------------------------------------------------------------------------------
 Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, spiexceptions, subtransaction, warning
(1 row)

SELECT elog_test();
INFO:  info
CONTEXT:  PL/Python function "elog_test"
INFO:  37
CONTEXT:  PL/Python function "elog_test"
INFO:  ()
CONTEXT:  PL/Python function "elog_test"
INFO:  ('info', 37, [1, 2, 3])
CONTEXT:  PL/Python function "elog_test"
NOTICE:  notice
CONTEXT:  PL/Python function "elog_test"
WARNING:  warning
CONTEXT:  PL/Python function "elog_test"
ERROR:  plpy.Error: error (plpython.c:4652)
CONTEXT:  Traceback (most recent call last):
  PL/Python function "elog_test", line 10, in <module>
    plpy.error('error')
PL/Python function "elog_test"
-- 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
CONTEXT:  while creating return value
PL/Python function "test_void_func2"
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 and nameless parameters
SELECT test_param_names0(2,7);
 test_param_names0 
-------------------
                 9
(1 row)

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': 'johnd', 'userid': 2, 'fname': 'john'}
 {'lname': 'smith', 'username': 'slash', 'userid': 4, 'fname': 'rick'}
 {'lname': 'darwin', 'username': 'beagle', 'userid': 6, 'fname': 'charles'}
 {'lname': 'doe', 'username': 'j_doe', 'userid': 1, 'fname': 'jane'}
 {'lname': 'doe', 'username': 'w_doe', 'userid': 3, 'fname': 'willem'}
 {'lname': 'smith', 'username': 'w_smith', 'userid': 5, 'fname': 'willem'}
(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)

SELECT test_setof_spi_in_iterator();
 test_setof_spi_in_iterator 
----------------------------
 Hello
 Brave
 New
 World
(4 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:  length of returned sequence did not match number of columns in row
CONTEXT:  while creating return value
PL/Python function "test_in_out_params_multi"
SELECT * FROM test_inout_params('test_in');
     first     
---------------
 test_in_inout
(1 row)

SELECT * FROM test_type_conversion_bool(true);
 test_type_conversion_bool 
---------------------------
 t
(1 row)

SELECT * FROM test_type_conversion_bool(false);
 test_type_conversion_bool 
---------------------------
 f
(1 row)

SELECT * FROM test_type_conversion_bool(null);
 test_type_conversion_bool 
---------------------------
 
(1 row)

SELECT * FROM test_type_conversion_char('a');
 test_type_conversion_char 
---------------------------
 a
(1 row)

SELECT * FROM test_type_conversion_char(null);
 test_type_conversion_char 
---------------------------
 
(1 row)

SELECT * FROM test_type_conversion_int2(100::int2);
 test_type_conversion_int2 
---------------------------
                       100
(1 row)

SELECT * FROM test_type_conversion_int2(null);
 test_type_conversion_int2 
---------------------------
                          
(1 row)

SELECT * FROM test_type_conversion_int4(100);
 test_type_conversion_int4 
---------------------------
                       100
(1 row)

SELECT * FROM test_type_conversion_int4(null);
 test_type_conversion_int4 
---------------------------
                          
(1 row)

SELECT * FROM test_type_conversion_int8(100);
 test_type_conversion_int8 
---------------------------
                       100
(1 row)

SELECT * FROM test_type_conversion_int8(null);
 test_type_conversion_int8 
---------------------------
                          
(1 row)

SELECT * FROM test_type_conversion_float4(100);
 test_type_conversion_float4 
-----------------------------
                         100
(1 row)

SELECT * FROM test_type_conversion_float4(null);
 test_type_conversion_float4 
-----------------------------
                            
(1 row)

SELECT * FROM test_type_conversion_float8(100);
 test_type_conversion_float8 
-----------------------------
                         100
(1 row)

SELECT * FROM test_type_conversion_float8(null);
 test_type_conversion_float8 
-----------------------------
                            
(1 row)

SELECT * FROM test_type_conversion_numeric(100);
 test_type_conversion_numeric 
------------------------------
                        100.0
(1 row)

SELECT * FROM test_type_conversion_numeric(null);
 test_type_conversion_numeric 
------------------------------
                             
(1 row)

SELECT * FROM test_type_conversion_text('hello world');
 test_type_conversion_text 
---------------------------
 hello world
(1 row)

SELECT * FROM test_type_conversion_text(null);
 test_type_conversion_text 
---------------------------
 
(1 row)

SELECT * FROM test_type_conversion_bytea('hello world');
 test_type_conversion_bytea 
----------------------------
 hello world
(1 row)

SELECT * FROM test_type_conversion_bytea(null);
 test_type_conversion_bytea 
----------------------------
 
(1 row)

SELECT test_type_unmarshal(x) FROM test_type_marshal() x;
 test_type_unmarshal 
---------------------
 hello world
(1 row)

SELECT (split(10)).*; 
 a  | b  
----+----
 10 | 11
 12 | 13
(2 rows)

SELECT * FROM split(100); 
  a  |  b  
-----+-----
 100 | 101
 102 | 103
(2 rows)

select unnamed_tuple_test(); 
 unnamed_tuple_test 
--------------------
 
(1 row)

select named_tuple_test(); 
 named_tuple_test 
------------------
 notice
(1 row)

select oneline() 
union all 
select oneline2()
union all 
select multiline() 
union all 
select multiline2() 
union all 
select multiline3()
                                                  oneline                                                   
------------------------------------------------------------------------------------------------------------
 No spaces
 "'a string with # and "" inside another string with #  and '' inside 
  One space                                                                                                 
   Two spaces                                                                                               
    Three spaces                                                                                            
 No spaces
                                                                                                            
 The ' in the comment should not cause this line to begin with a tab                                        
 This is a rather long string containing                                                                    
     several lines of text just as you would do in C.                                                       
      Note that whitespace at the beginning of the line issignificant. The string can contain both ' and ". 
 This is an another long string containing\n\                                                               
 two lines of text and defined with the r\"...\" syntax.
                                                                                                            
   # This is not a comment so the quotes at the end of the line do end the string 
(5 rows)