| -- 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) |
| |
