Repository: incubator-hawq Updated Branches: refs/heads/master 706882c20 -> 01a44739b
HAWQ-869. Add regression test for less tuple is inserted issue in prepared statement Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/01a44739 Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/01a44739 Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/01a44739 Branch: refs/heads/master Commit: 01a44739b0a701fc8b81ed91fdda52e618ef6b37 Parents: 706882c Author: Ruilong Huo <r...@pivotal.io> Authored: Mon Jun 27 16:04:46 2016 +0800 Committer: Ruilong Huo <r...@pivotal.io> Committed: Wed Jun 29 10:28:13 2016 +0800 ---------------------------------------------------------------------- .../PreparedStatement/TestPreparedStatement.cpp | 38 +++ .../feature/PreparedStatement/ans/insert.ans | 266 +++++++++++++++++++ .../feature/PreparedStatement/ans/proba.ans | 230 ++++++++++++++++ .../PreparedStatement/ans/proba_execute.ans | 228 ++++++++++++++++ .../feature/PreparedStatement/sql/insert.sql | 130 +++++++++ .../feature/PreparedStatement/sql/proba.sql | 147 ++++++++++ .../PreparedStatement/sql/proba_execute.sql | 145 ++++++++++ 7 files changed, 1184 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/01a44739/src/test/feature/PreparedStatement/TestPreparedStatement.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/PreparedStatement/TestPreparedStatement.cpp b/src/test/feature/PreparedStatement/TestPreparedStatement.cpp new file mode 100755 index 0000000..a8684ca --- /dev/null +++ b/src/test/feature/PreparedStatement/TestPreparedStatement.cpp @@ -0,0 +1,38 @@ +#include "gtest/gtest.h" + +#include "lib/sql_util.h" + + +class TestPreparedStatement: public ::testing::Test +{ + public: + TestPreparedStatement() {} + ~TestPreparedStatement() {} +}; + +// HAWQ-800: https://issues.apache.org/jira/browse/HAWQ-800 +// HAWQ-835: https://issues.apache.org/jira/browse/HAWQ-835 +TEST_F(TestPreparedStatement, TestPreparedStatementPrepare) +{ + hawq::test::SQLUtility util; + util.execSQLFile("PreparedStatement/sql/proba.sql", + "PreparedStatement/ans/proba.ans"); +} + +// HAWQ-800: https://issues.apache.org/jira/browse/HAWQ-800 +// HAWQ-835: https://issues.apache.org/jira/browse/HAWQ-835 +TEST_F(TestPreparedStatement, TestPreparedStatementExecute) +{ + hawq::test::SQLUtility util; + util.execSQLFile("PreparedStatement/sql/proba_execute.sql", + "PreparedStatement/ans/proba_execute.ans"); +} + +// HAWQ-800: https://issues.apache.org/jira/browse/HAWQ-800 +// HAWQ-835: https://issues.apache.org/jira/browse/HAWQ-835 +TEST_F(TestPreparedStatement, TestPreparedStatementInsert) +{ + hawq::test::SQLUtility util; + util.execSQLFile("PreparedStatement/sql/insert.sql", + "PreparedStatement/ans/insert.ans"); +} http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/01a44739/src/test/feature/PreparedStatement/ans/insert.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/PreparedStatement/ans/insert.ans b/src/test/feature/PreparedStatement/ans/insert.ans new file mode 100644 index 0000000..32b2f4f --- /dev/null +++ b/src/test/feature/PreparedStatement/ans/insert.ans @@ -0,0 +1,266 @@ +-- start_ignore +SET SEARCH_PATH=TestPreparedStatement_TestPreparedStatementInsert; +SET +-- end_ignore +-- start_ignore +drop function if exists f1(); +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:5: NOTICE: function f1() does not exist, skipping +DROP FUNCTION +drop function if exists f2(); +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:6: NOTICE: function f2() does not exist, skipping +DROP FUNCTION +drop table if exists t1; +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:7: NOTICE: table "t1" does not exist, skipping +DROP TABLE +drop table if exists t2; +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:8: NOTICE: table "t2" does not exist, skipping +DROP TABLE +-- end_ignore +create table t1 (id int); +CREATE TABLE +insert into t1 values (1); +INSERT 0 1 +create table t2 (id int); +CREATE TABLE +CREATE OR REPLACE FUNCTION f1() + RETURNS text + LANGUAGE plpgsql +AS +$body$ +DECLARE + l_rec record; + l_item record; + l_count integer; +BEGIN + + RAISE NOTICE '--- Initial content of t1: begin ---'; + SELECT count(*) INTO l_count FROM t1; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t1 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Initial content of t1: end ---'; + + FOR l_rec IN ( SELECT generate_series(1, 3) AS idx ) + LOOP + INSERT INTO t1 SELECT * FROM t1; + + RAISE NOTICE '--- Content of t1 after %d insert in loop: begin ---', l_rec.idx; + SELECT count(*) INTO l_count FROM t1; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t1 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Content of t1 after %d insert in loop: end ---', l_rec.idx; + END LOOP; + + RAISE NOTICE '--- Final content of t1: begin ---'; + SELECT count(*) INTO l_count FROM t1; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t1 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Final content of t1: end ---'; + + RETURN 'done'; +END +$body$ +; +CREATE FUNCTION +CREATE OR REPLACE FUNCTION f2() + RETURNS text + LANGUAGE plpgsql +AS +$body$ +DECLARE + l_rec record; + l_item record; + l_count integer; +BEGIN + RAISE NOTICE '--- Initial content of t2: begin ---'; + SELECT count(*) INTO l_count FROM t2; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t2 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Initial content of t2: end ---'; + + insert into t2 values (1); + + RAISE NOTICE '--- Content of t2 after seed data inserted: begin ---'; + SELECT count(*) INTO l_count FROM t2; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t2 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Content of t2 after seed data inserted: end ---'; + + FOR l_rec IN ( SELECT generate_series(1, 3) AS idx ) + LOOP + INSERT INTO t2 SELECT * FROM t2; + + RAISE NOTICE '--- Content of t2 after %d insert in loop: begin ---', l_rec.idx; + SELECT count(*) INTO l_count FROM t2; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t2 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Content of t2 after %d insert in loop: end ---', l_rec.idx; + END LOOP; + + RAISE NOTICE '--- Final content of t2: begin ---'; + SELECT count(*) INTO l_count FROM t2; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t2 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Final content of t2: end ---'; + + RETURN 'done'; +END +$body$ +; +CREATE FUNCTION +select f1(); +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Initial content of t1: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- # tuple: 1 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Initial content of t1: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Content of t1 after 1d insert in loop: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- # tuple: 2 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Content of t1 after 1d insert in loop: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Content of t1 after 2d insert in loop: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- # tuple: 4 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Content of t1 after 2d insert in loop: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Content of t1 after 3d insert in loop: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- # tuple: 8 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Content of t1 after 3d insert in loop: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Final content of t1: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- # tuple: 8 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:122: NOTICE: --- Final content of t1: end --- + f1 +------ + done +(1 row) + +select f2(); +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Initial content of t2: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- # tuple: 0 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Initial content of t2: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Content of t2 after seed data inserted: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- # tuple: 1 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Content of t2 after seed data inserted: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Content of t2 after 1d insert in loop: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- # tuple: 2 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Content of t2 after 1d insert in loop: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Content of t2 after 2d insert in loop: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- # tuple: 4 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Content of t2 after 2d insert in loop: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Content of t2 after 3d insert in loop: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- # tuple: 8 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Content of t2 after 3d insert in loop: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Final content of t2: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- # tuple: 8 +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: id +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: "1" +psql:/tmp/TestPreparedStatement_TestPreparedStatementInsert.sql:124: NOTICE: --- Final content of t2: end --- + f2 +------ + done +(1 row) + +select * from t1; + id +---- + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(8 rows) + +select * from t2; + id +---- + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(8 rows) + +drop function if exists f1(); +DROP FUNCTION +drop function if exists f2(); +DROP FUNCTION +drop table if exists t1; +DROP TABLE +drop table if exists t2; +DROP TABLE http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/01a44739/src/test/feature/PreparedStatement/ans/proba.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/PreparedStatement/ans/proba.ans b/src/test/feature/PreparedStatement/ans/proba.ans new file mode 100644 index 0000000..0e01005 --- /dev/null +++ b/src/test/feature/PreparedStatement/ans/proba.ans @@ -0,0 +1,230 @@ +-- start_ignore +SET SEARCH_PATH=TestPreparedStatement_TestPreparedStatementPrepare; +SET +-- end_ignore +-- start_ignore +drop function if exists f_load(); +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:5: NOTICE: function f_load() does not exist, skipping +DROP FUNCTION +drop table if exists src_table; +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:6: NOTICE: table "src_table" does not exist, skipping +DROP TABLE +drop table if exists main_table; +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:7: NOTICE: table "main_table" does not exist, skipping +DROP TABLE +drop table if exists map_table; +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:8: NOTICE: table "map_table" does not exist, skipping +DROP TABLE +-- end_ignore +create table src_table ( id_natural integer, value varchar, id_file int); +CREATE TABLE +create table main_table (id_auto integer, id_natural integer, value varchar, record_type varchar, id_file integer); +CREATE TABLE +create table map_table (id_auto integer, id_natural integer); +CREATE TABLE +insert into src_table values ( 1, 'sth', 10); +INSERT 0 1 +insert into src_table values ( 1, 'sht else', 11); +INSERT 0 1 +CREATE OR REPLACE FUNCTION f_load() + RETURNS text + LANGUAGE plpgsql +AS +$body$ +DECLARE + + l_count integer:=0; + l_rec record; + l_tuple integer; + l_item record; + +BEGIN + + RAISE NOTICE '--- Initial content of main_table: begin ---'; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Initial content of main_table: end ---'; + + INSERT INTO main_table + ( id_natural + , value + , record_type + , id_file + ) + SELECT id_natural + , value + , 'P' + , id_file + FROM src_table; + + GET DIAGNOSTICS l_tuple = ROW_COUNT; + RAISE NOTICE 'INSERT INTO main_table with seed data from src_table: % tuple inserted', l_tuple; + + RAISE NOTICE '--- Content of main_table after seed data inserted: begin ---'; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Content of main_table after seed data inserted: end ---'; + +FOR l_rec IN ( select id_file from main_table group by id_file order by 1) + LOOP + l_count:=l_count+1; + + INSERT INTO main_table + ( id_natural + , value + , record_type + , id_file + ) + SELECT id_natural + , value + , 'N' + , l_rec.id_file + FROM main_table pxf + WHERE pxf.id_file=l_rec.id_file AND pxf.record_type='P' + ; + + GET DIAGNOSTICS l_tuple = ROW_COUNT; + RAISE NOTICE 'Insert into main_table in loop % with first insert statement: % tuple inserted', l_count, l_tuple; + + RAISE NOTICE '--- Content of main_table after loop % with first insert statement: begin ---', l_count; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Content of main_table after loop % with first insert statement: end ---', l_count; + + INSERT INTO main_table + ( id_auto + , id_natural + , value + , record_type + , id_file + ) + SELECT l_count + , ma.id_natural + , value + , CASE WHEN mt.id_natural IS NULL THEN 'I' ELSE 'U' END AS record_type + , id_file + FROM main_table ma + LEFT JOIN map_table mt on mt.id_natural=ma.id_natural + WHERE ma.record_type='N' AND ma.id_file=l_rec.id_file + ; + + GET DIAGNOSTICS l_tuple = ROW_COUNT; + RAISE NOTICE 'Insert into main_table in loop % with second insert statement: % tuple inserted', l_count, l_tuple; + + RAISE NOTICE '--- Content of main_table after loop % with second insert statement: begin ---', l_count; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Content of main_table after loop % with second insert statement: end ---', l_count; + + execute 'truncate table map_table'; + + INSERT INTO map_table + ( id_auto + , id_natural + ) + SELECT ma.id_auto + , ma.id_natural + FROM main_table ma + WHERE record_type NOT IN ('N','P') AND id_file=l_rec.id_file + ; + + END LOOP; + + RAISE NOTICE '--- Final content of main_table: begin ---'; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Final content of main_table: end ---'; + + RETURN 'done'; + +END; +$body$ +; +CREATE FUNCTION +select f_load(); +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Initial content of main_table: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Initial content of main_table: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: INSERT INTO main_table with seed data from src_table: 2 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after seed data inserted: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after seed data inserted: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: Insert into main_table in loop 1 with first insert statement: 1 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after loop 1 with first insert statement: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after loop 1 with first insert statement: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: Insert into main_table in loop 1 with second insert statement: 1 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after loop 1 with second insert statement: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: "1", "1", sth, "I", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after loop 1 with second insert statement: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: Insert into main_table in loop 2 with first insert statement: 1 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after loop 2 with first insert statement: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: "1", "1", sth, "I", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", "sht else", "N", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after loop 2 with first insert statement: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: Insert into main_table in loop 2 with second insert statement: 1 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after loop 2 with second insert statement: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", "sht else", "N", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: "1", "1", sth, "I", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: "2", "1", "sht else", "U", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Content of main_table after loop 2 with second insert statement: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Final content of main_table: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: "2", "1", "sht else", "U", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: "1", "1", sth, "I", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: <NULL>, "1", "sht else", "N", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementPrepare.sql:143: NOTICE: --- Final content of main_table: end --- + f_load +-------- + done +(1 row) + +select * from main_table; + id_auto | id_natural | value | record_type | id_file +---------+------------+----------+-------------+--------- + 1 | 1 | sth | I | 10 + 2 | 1 | sht else | U | 11 + | 1 | sht else | P | 11 + | 1 | sth | N | 10 + | 1 | sht else | N | 11 + | 1 | sth | P | 10 +(6 rows) + +drop function if exists f_load(); +DROP FUNCTION +drop table if exists src_table; +DROP TABLE +drop table if exists main_table; +DROP TABLE +drop table if exists map_table; +DROP TABLE http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/01a44739/src/test/feature/PreparedStatement/ans/proba_execute.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/PreparedStatement/ans/proba_execute.ans b/src/test/feature/PreparedStatement/ans/proba_execute.ans new file mode 100644 index 0000000..853a092 --- /dev/null +++ b/src/test/feature/PreparedStatement/ans/proba_execute.ans @@ -0,0 +1,228 @@ +-- start_ignore +SET SEARCH_PATH=TestPreparedStatement_TestPreparedStatementExecute; +SET +-- end_ignore +drop function if exists f_load_exe(); +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:4: NOTICE: function f_load_exe() does not exist, skipping +DROP FUNCTION +drop table if exists src_table_exe; +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:5: NOTICE: table "src_table_exe" does not exist, skipping +DROP TABLE +drop table if exists main_table_exe; +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:6: NOTICE: table "main_table_exe" does not exist, skipping +DROP TABLE +drop table if exists map_table_exe; +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:7: NOTICE: table "map_table_exe" does not exist, skipping +DROP TABLE +create table src_table_exe ( id_natural integer, value varchar, id_file int); +CREATE TABLE +create table main_table_exe (id_auto integer, id_natural integer, value varchar, record_type varchar, id_file integer); +CREATE TABLE +create table map_table_exe (id_auto integer, id_natural integer); +CREATE TABLE +insert into src_table_exe values ( 1, 'sth', 10); +INSERT 0 1 +insert into src_table_exe values ( 1, 'sht else', 11); +INSERT 0 1 +CREATE OR REPLACE FUNCTION f_load_exe() + RETURNS text + LANGUAGE plpgsql +AS +$body$ +DECLARE + + l_count integer:=0; + l_rec record; + l_tuple integer; + l_item record; + +BEGIN + +RAISE NOTICE '--- Initial content of main_table_exe: begin ---'; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Initial content of main_table_exe: end ---'; + +execute 'INSERT INTO main_table_exe + ( id_natural + , value + , record_type + , id_file + ) + SELECT id_natural + , value + , ''P'' + , id_file + FROM src_table_exe'; + +GET DIAGNOSTICS l_tuple = ROW_COUNT; +RAISE NOTICE 'INSERT INTO main_table_exe with seed data from src_table_exe: % tuple inserted', l_tuple; + +RAISE NOTICE '--- Content of main_table_exe after seed data inserted: begin ---'; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Content of main_table_exe after seed data inserted: end ---'; + +FOR l_rec IN ( select id_file from main_table_exe group by id_file order by 1) + LOOP + l_count:=l_count+1; + +execute 'INSERT INTO main_table_exe + ( id_natural + , value + , record_type + , id_file + ) + SELECT id_natural + , value + , ''N'' + , '||l_rec.id_file||' + FROM main_table_exe pxf + WHERE pxf.id_file='||l_rec.id_file||' AND pxf.record_type=''P''' + ; + +GET DIAGNOSTICS l_tuple = ROW_COUNT; +RAISE NOTICE 'Insert into main_table_exe in loop % with first insert statement: % tuple inserted', l_count, l_tuple; + +RAISE NOTICE '--- Content of main_table_exe after loop % with first insert statement: begin ---', l_count; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Content of main_table_exe after loop % with first insert statement: end ---', l_count; + +execute 'INSERT INTO main_table_exe + ( id_auto + , id_natural + , value + , record_type + , id_file + ) + SELECT '||l_count||' + , ma.id_natural + , value + , CASE WHEN mt.id_natural IS NULL THEN ''I'' ELSE ''U'' END AS record_type + , id_file + FROM main_table_exe ma + LEFT JOIN map_table_exe mt on mt.id_natural=ma.id_natural + WHERE ma.record_type=''N'' AND ma.id_file='||l_rec.id_file + ; + + execute 'truncate table map_table_exe'; + +GET DIAGNOSTICS l_tuple = ROW_COUNT; +RAISE NOTICE 'Insert into main_table_exe in loop % with second insert statement: % tuple inserted', l_count, l_tuple; + +RAISE NOTICE '--- Content of main_table_exe after loop % with second insert statement: begin ---', l_count; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Content of main_table_exe after loop % with second insert statement: end ---', l_count; + +execute 'INSERT INTO map_table_exe + ( id_auto + , id_natural + ) + SELECT ma.id_auto + , ma.id_natural + FROM main_table_exe ma + WHERE record_type NOT IN (''N'',''P'') AND id_file='||l_rec.id_file + ; + + END LOOP; + +RAISE NOTICE '--- Final content of main_table_exe: begin ---'; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Final content of main_table_exe: end ---'; + + RETURN 'done'; + +END; +$body$ +; +CREATE FUNCTION +select f_load_exe(); +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Initial content of main_table_exe: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Initial content of main_table_exe: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: INSERT INTO main_table_exe with seed data from src_table_exe: 2 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after seed data inserted: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after seed data inserted: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: Insert into main_table_exe in loop 1 with first insert statement: 1 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after loop 1 with first insert statement: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after loop 1 with first insert statement: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: Insert into main_table_exe in loop 1 with second insert statement: 0 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after loop 1 with second insert statement: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: "1", "1", sth, "I", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after loop 1 with second insert statement: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: Insert into main_table_exe in loop 2 with first insert statement: 1 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after loop 2 with first insert statement: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: "1", "1", sth, "I", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", "sht else", "N", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after loop 2 with first insert statement: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: Insert into main_table_exe in loop 2 with second insert statement: 0 tuple inserted +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after loop 2 with second insert statement: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: "1", "1", sth, "I", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", "sht else", "N", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: "2", "1", "sht else", "U", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Content of main_table_exe after loop 2 with second insert statement: end --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Final content of main_table_exe: begin --- +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: id_auto, id_natural, value, record_type, id_file +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "P", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: "2", "1", "sht else", "U", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", "sht else", "P", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: "1", "1", sth, "I", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", "sht else", "N", "11" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: <NULL>, "1", sth, "N", "10" +psql:/tmp/TestPreparedStatement_TestPreparedStatementExecute.sql:141: NOTICE: --- Final content of main_table_exe: end --- + f_load_exe +------------ + done +(1 row) + +select * from main_table_exe; + id_auto | id_natural | value | record_type | id_file +---------+------------+----------+-------------+--------- + | 1 | sht else | P | 11 + | 1 | sth | P | 10 + 2 | 1 | sht else | U | 11 + | 1 | sth | N | 10 + 1 | 1 | sth | I | 10 + | 1 | sht else | N | 11 +(6 rows) + +drop function if exists f_load_exe(); +DROP FUNCTION +drop table if exists src_table_exe; +DROP TABLE +drop table if exists main_table_exe; +DROP TABLE +drop table if exists map_table_exe; +DROP TABLE http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/01a44739/src/test/feature/PreparedStatement/sql/insert.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/PreparedStatement/sql/insert.sql b/src/test/feature/PreparedStatement/sql/insert.sql new file mode 100644 index 0000000..01867c0 --- /dev/null +++ b/src/test/feature/PreparedStatement/sql/insert.sql @@ -0,0 +1,130 @@ +-- start_ignore +drop function if exists f1(); +drop function if exists f2(); +drop table if exists t1; +drop table if exists t2; +-- end_ignore + +create table t1 (id int); +insert into t1 values (1); + +create table t2 (id int); + +CREATE OR REPLACE FUNCTION f1() + RETURNS text + LANGUAGE plpgsql +AS +$body$ +DECLARE + l_rec record; + l_item record; + l_count integer; +BEGIN + + RAISE NOTICE '--- Initial content of t1: begin ---'; + SELECT count(*) INTO l_count FROM t1; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t1 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Initial content of t1: end ---'; + + FOR l_rec IN ( SELECT generate_series(1, 3) AS idx ) + LOOP + INSERT INTO t1 SELECT * FROM t1; + + RAISE NOTICE '--- Content of t1 after %d insert in loop: begin ---', l_rec.idx; + SELECT count(*) INTO l_count FROM t1; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t1 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Content of t1 after %d insert in loop: end ---', l_rec.idx; + END LOOP; + + RAISE NOTICE '--- Final content of t1: begin ---'; + SELECT count(*) INTO l_count FROM t1; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t1 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Final content of t1: end ---'; + + RETURN 'done'; +END +$body$ +; + +CREATE OR REPLACE FUNCTION f2() + RETURNS text + LANGUAGE plpgsql +AS +$body$ +DECLARE + l_rec record; + l_item record; + l_count integer; +BEGIN + RAISE NOTICE '--- Initial content of t2: begin ---'; + SELECT count(*) INTO l_count FROM t2; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t2 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Initial content of t2: end ---'; + + insert into t2 values (1); + + RAISE NOTICE '--- Content of t2 after seed data inserted: begin ---'; + SELECT count(*) INTO l_count FROM t2; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t2 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Content of t2 after seed data inserted: end ---'; + + FOR l_rec IN ( SELECT generate_series(1, 3) AS idx ) + LOOP + INSERT INTO t2 SELECT * FROM t2; + + RAISE NOTICE '--- Content of t2 after %d insert in loop: begin ---', l_rec.idx; + SELECT count(*) INTO l_count FROM t2; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t2 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Content of t2 after %d insert in loop: end ---', l_rec.idx; + END LOOP; + + RAISE NOTICE '--- Final content of t2: begin ---'; + SELECT count(*) INTO l_count FROM t2; + RAISE NOTICE '--- # tuple: %', l_count; + RAISE NOTICE 'id'; + FOR l_item IN SELECT * FROM t2 LOOP + RAISE NOTICE '%', quote_ident(l_item.id); + END LOOP; + RAISE NOTICE '--- Final content of t2: end ---'; + + RETURN 'done'; +END +$body$ +; + +select f1(); + +select f2(); + +select * from t1; + +select * from t2; + +drop function if exists f1(); +drop function if exists f2(); +drop table if exists t1; +drop table if exists t2; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/01a44739/src/test/feature/PreparedStatement/sql/proba.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/PreparedStatement/sql/proba.sql b/src/test/feature/PreparedStatement/sql/proba.sql new file mode 100644 index 0000000..b9653a6 --- /dev/null +++ b/src/test/feature/PreparedStatement/sql/proba.sql @@ -0,0 +1,147 @@ +-- start_ignore +drop function if exists f_load(); +drop table if exists src_table; +drop table if exists main_table; +drop table if exists map_table; +-- end_ignore + +create table src_table ( id_natural integer, value varchar, id_file int); +create table main_table (id_auto integer, id_natural integer, value varchar, record_type varchar, id_file integer); +create table map_table (id_auto integer, id_natural integer); + +insert into src_table values ( 1, 'sth', 10); +insert into src_table values ( 1, 'sht else', 11); + +CREATE OR REPLACE FUNCTION f_load() + RETURNS text + LANGUAGE plpgsql +AS +$body$ +DECLARE + + l_count integer:=0; + l_rec record; + l_tuple integer; + l_item record; + +BEGIN + + RAISE NOTICE '--- Initial content of main_table: begin ---'; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Initial content of main_table: end ---'; + + INSERT INTO main_table + ( id_natural + , value + , record_type + , id_file + ) + SELECT id_natural + , value + , 'P' + , id_file + FROM src_table; + + GET DIAGNOSTICS l_tuple = ROW_COUNT; + RAISE NOTICE 'INSERT INTO main_table with seed data from src_table: % tuple inserted', l_tuple; + + RAISE NOTICE '--- Content of main_table after seed data inserted: begin ---'; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Content of main_table after seed data inserted: end ---'; + +FOR l_rec IN ( select id_file from main_table group by id_file order by 1) + LOOP + l_count:=l_count+1; + + INSERT INTO main_table + ( id_natural + , value + , record_type + , id_file + ) + SELECT id_natural + , value + , 'N' + , l_rec.id_file + FROM main_table pxf + WHERE pxf.id_file=l_rec.id_file AND pxf.record_type='P' + ; + + GET DIAGNOSTICS l_tuple = ROW_COUNT; + RAISE NOTICE 'Insert into main_table in loop % with first insert statement: % tuple inserted', l_count, l_tuple; + + RAISE NOTICE '--- Content of main_table after loop % with first insert statement: begin ---', l_count; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Content of main_table after loop % with first insert statement: end ---', l_count; + + INSERT INTO main_table + ( id_auto + , id_natural + , value + , record_type + , id_file + ) + SELECT l_count + , ma.id_natural + , value + , CASE WHEN mt.id_natural IS NULL THEN 'I' ELSE 'U' END AS record_type + , id_file + FROM main_table ma + LEFT JOIN map_table mt on mt.id_natural=ma.id_natural + WHERE ma.record_type='N' AND ma.id_file=l_rec.id_file + ; + + GET DIAGNOSTICS l_tuple = ROW_COUNT; + RAISE NOTICE 'Insert into main_table in loop % with second insert statement: % tuple inserted', l_count, l_tuple; + + RAISE NOTICE '--- Content of main_table after loop % with second insert statement: begin ---', l_count; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Content of main_table after loop % with second insert statement: end ---', l_count; + + execute 'truncate table map_table'; + + INSERT INTO map_table + ( id_auto + , id_natural + ) + SELECT ma.id_auto + , ma.id_natural + FROM main_table ma + WHERE record_type NOT IN ('N','P') AND id_file=l_rec.id_file + ; + + END LOOP; + + RAISE NOTICE '--- Final content of main_table: begin ---'; + RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; + FOR l_item IN SELECT * FROM main_table LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); + END LOOP; + RAISE NOTICE '--- Final content of main_table: end ---'; + + RETURN 'done'; + +END; +$body$ +; + +select f_load(); + +select * from main_table; + +drop function if exists f_load(); +drop table if exists src_table; +drop table if exists main_table; +drop table if exists map_table; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/01a44739/src/test/feature/PreparedStatement/sql/proba_execute.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/PreparedStatement/sql/proba_execute.sql b/src/test/feature/PreparedStatement/sql/proba_execute.sql new file mode 100644 index 0000000..05625f3 --- /dev/null +++ b/src/test/feature/PreparedStatement/sql/proba_execute.sql @@ -0,0 +1,145 @@ +drop function if exists f_load_exe(); +drop table if exists src_table_exe; +drop table if exists main_table_exe; +drop table if exists map_table_exe; + +create table src_table_exe ( id_natural integer, value varchar, id_file int); +create table main_table_exe (id_auto integer, id_natural integer, value varchar, record_type varchar, id_file integer); +create table map_table_exe (id_auto integer, id_natural integer); + +insert into src_table_exe values ( 1, 'sth', 10); +insert into src_table_exe values ( 1, 'sht else', 11); + +CREATE OR REPLACE FUNCTION f_load_exe() + RETURNS text + LANGUAGE plpgsql +AS +$body$ +DECLARE + + l_count integer:=0; + l_rec record; + l_tuple integer; + l_item record; + +BEGIN + +RAISE NOTICE '--- Initial content of main_table_exe: begin ---'; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Initial content of main_table_exe: end ---'; + +execute 'INSERT INTO main_table_exe + ( id_natural + , value + , record_type + , id_file + ) + SELECT id_natural + , value + , ''P'' + , id_file + FROM src_table_exe'; + +GET DIAGNOSTICS l_tuple = ROW_COUNT; +RAISE NOTICE 'INSERT INTO main_table_exe with seed data from src_table_exe: % tuple inserted', l_tuple; + +RAISE NOTICE '--- Content of main_table_exe after seed data inserted: begin ---'; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Content of main_table_exe after seed data inserted: end ---'; + +FOR l_rec IN ( select id_file from main_table_exe group by id_file order by 1) + LOOP + l_count:=l_count+1; + +execute 'INSERT INTO main_table_exe + ( id_natural + , value + , record_type + , id_file + ) + SELECT id_natural + , value + , ''N'' + , '||l_rec.id_file||' + FROM main_table_exe pxf + WHERE pxf.id_file='||l_rec.id_file||' AND pxf.record_type=''P''' + ; + +GET DIAGNOSTICS l_tuple = ROW_COUNT; +RAISE NOTICE 'Insert into main_table_exe in loop % with first insert statement: % tuple inserted', l_count, l_tuple; + +RAISE NOTICE '--- Content of main_table_exe after loop % with first insert statement: begin ---', l_count; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Content of main_table_exe after loop % with first insert statement: end ---', l_count; + +execute 'INSERT INTO main_table_exe + ( id_auto + , id_natural + , value + , record_type + , id_file + ) + SELECT '||l_count||' + , ma.id_natural + , value + , CASE WHEN mt.id_natural IS NULL THEN ''I'' ELSE ''U'' END AS record_type + , id_file + FROM main_table_exe ma + LEFT JOIN map_table_exe mt on mt.id_natural=ma.id_natural + WHERE ma.record_type=''N'' AND ma.id_file='||l_rec.id_file + ; + + execute 'truncate table map_table_exe'; + +GET DIAGNOSTICS l_tuple = ROW_COUNT; +RAISE NOTICE 'Insert into main_table_exe in loop % with second insert statement: % tuple inserted', l_count, l_tuple; + +RAISE NOTICE '--- Content of main_table_exe after loop % with second insert statement: begin ---', l_count; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Content of main_table_exe after loop % with second insert statement: end ---', l_count; + +execute 'INSERT INTO map_table_exe + ( id_auto + , id_natural + ) + SELECT ma.id_auto + , ma.id_natural + FROM main_table_exe ma + WHERE record_type NOT IN (''N'',''P'') AND id_file='||l_rec.id_file + ; + + END LOOP; + +RAISE NOTICE '--- Final content of main_table_exe: begin ---'; +RAISE NOTICE 'id_auto, id_natural, value, record_type, id_file'; +FOR l_item IN SELECT * FROM main_table_exe LOOP + RAISE NOTICE '%, %, %, %, %', quote_ident(l_item.id_auto), quote_ident(l_item.id_natural), quote_ident(l_item.value), quote_ident(l_item.record_type), quote_ident(l_item.id_file); +END LOOP; +RAISE NOTICE '--- Final content of main_table_exe: end ---'; + + RETURN 'done'; + +END; +$body$ +; + +select f_load_exe(); + +select * from main_table_exe; + +drop function if exists f_load_exe(); +drop table if exists src_table_exe; +drop table if exists main_table_exe; +drop table if exists map_table_exe;
