hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From h...@apache.org
Subject incubator-hawq git commit: HAWQ-869. Add regression test for less tuple is inserted issue in prepared statement
Date Wed, 29 Jun 2016 02:26:31 GMT
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 <rhuo@pivotal.io>
Authored: Mon Jun 27 16:04:46 2016 +0800
Committer: Ruilong Huo <rhuo@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;


Mime
View raw message