Commit: d422966d7d4edf71a032078ad882e64625593968 - pg_hint_plan (git)

firtst release

Commit MetaInfo

• Revision: d422966d7d4edf71a032078ad882e64625593968 (tree)
• Zeit: 2015-01-05 16:55:23
• Autor: Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
• Commiter: Kyotaro Horiguchi

Log Message

Fixed a bug related to SQL statements in PL/pgSQL functions.

At least since 9.1, true query strings of SQL statements executed in
PL/pgSQL functions were found that were not obtained correctly by the
previous implement, it is because that PLpgSQL_stmt variable did not
have proper query string to be read for hints. Instead, it is changed
to read them from the top of error_context_stack in
pg_hint_plan_planner(). This change made a slight difference in
behavior which doesn't affect its work so a part of the regtest was
also changed. And added some regression tests for it.

Ändern Zusammenfassung

• modified: expected/pg_hint_plan.out
• modified: expected/ut-A.out
• modified: pg_hint_plan.c
• modified: sql/pg_hint_plan.sql

Diff

--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -7943,14 +7943,16 @@ EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
    Index Cond: (id = 1)
 (2 rows)
 
-DO LANGUAGE plpgsql $$
+-- static function
+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
 DECLARE
-    id integer;
+  ret record;
 BEGIN
-	SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
-	RETURN;
+  SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
+  RETURN ret;
 END;
-$$;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
 LOG:  pg_hint_plan:
 used hint:
 SeqScan(t1)
@@ -7958,8 +7960,209 @@ not used hint:
 duplication hint:
 error hint:
 
-CONTEXT:  SQL statement "SELECT /*+SeqScan(t1)*/ t1.id         FROM t1 WHERE t1.id = 1"
-PL/pgSQL function inline_code_block line 5 at SQL statement
+CONTEXT:  SQL statement "SELECT /*+ SeqScan(t1) */ *          FROM t1 LIMIT 1"
+PL/pgSQL function testfunc() line 5 at SQL statement
+ testfunc 
+----------
+ (1,1)
+(1 row)
+
+-- dynamic function
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+  EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "/*+ SeqScan(t1) */ SELECT * FROM t1"
+PL/pgSQL function testfunc() line 3 at EXECUTE statement
+ testfunc 
+----------
+ 
+(1 row)
+
+-- This should not use SeqScan(t1)
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val 
+----+-----
+  1 |   1
+(1 row)
+
+-- Perform
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+  PERFORM  1, /*+ SeqScan(t1) */ * from t1;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1"
+PL/pgSQL function testfunc() line 3 at PERFORM
+ testfunc 
+----------
+ 
+(1 row)
+
+-- FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+  sum int;
+  v int;
+BEGIN
+  sum := 0;
+  FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
+    sum := sum + v;
+  END LOOP;
+  RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 7 at FOR over SELECT rows
+ testfunc 
+----------
+         
+(1 row)
+
+-- Dynamic FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+  sum int;
+  v int;
+  i   int;
+BEGIN
+  sum := 0;
+  FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
+    sum := sum + v;
+  END LOOP;
+  RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 8 at FOR over EXECUTE statement
+ testfunc 
+----------
+        0
+(1 row)
+
+-- Cursor FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+  ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+  rec record;
+  sum int := 0;
+BEGIN
+  FOR rec IN ref LOOP
+    sum := sum + rec.val;
+  END LOOP;
+  RETURN sum;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 7 at FOR over cursor
+ testfunc 
+----------
+   495000
+(1 row)
+
+-- RETURN QUERY
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+  RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 3 at RETURN QUERY
+ id | val 
+----+-----
+  1 |   1
+(1 row)
+
+-- Test for error exit from inner SQL statement.
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+  RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+ERROR:  relation "ttx" does not exist
+LINE 1: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
+                                         ^
+QUERY:  SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
+CONTEXT:  PL/pgSQL function testfunc() line 3 at RETURN QUERY
+-- this should not use SeqScan(t1) hint.
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val 
+----+-----
+  1 |   1
+(1 row)
+
+DROP FUNCTION testfunc();
 DROP EXTENSION pg_hint_plan;
 --
 -- Rows hint tests

--- a/expected/ut-A.out
+++ b/expected/ut-A.out
@@ -4258,37 +4258,7 @@ SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1)
 	 ORDER BY t_1.c1 LIMIT 1"
 PL/pgSQL function nested_planner(integer) line 12 at SQL statement
 LOG:  pg_hint_plan:
-used hint:
-not used hint:
-IndexScan(t_1)
-duplication hint:
-error hint:
-
-CONTEXT:  SQL statement "SELECT 0"
-PL/pgSQL function nested_planner(integer) line 9 at RETURN
-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1)                 FROM s1.t1 t_1
-	  JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
-	 ORDER BY t_1.c1 LIMIT 1"
-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1)                 FROM s1.t1 t_1
-	  JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
-	 ORDER BY t_1.c1 LIMIT 1"
-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1)                 FROM s1.t1 t_1
-	  JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
-	 ORDER BY t_1.c1 LIMIT 1"
-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1)                 FROM s1.t1 t_1
-	  JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
-	 ORDER BY t_1.c1 LIMIT 1"
-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
-LOG:  pg_hint_plan:
-used hint:
-IndexScan(t_1)
-not used hint:
-duplication hint:
-error hint:
-
+no hint
 CONTEXT:  SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1)                 FROM s1.t1 t_1
 	  JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
 	 ORDER BY t_1.c1 LIMIT 1"

--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -432,7 +432,13 @@ static int debug_level = 0;
 static int	pg_hint_plan_message_level = INFO;
 /* Default is off, to keep backward compatibility. */
 static bool	pg_hint_plan_enable_hint_table = false;
-static bool	hidestmt = false;
+
+/* Internal static variables. */
+static bool	hidestmt = false;				/* Allow or inhibit STATEMENT: output */
+
+static int plpgsql_recurse_level = 0;		/* PLpgSQL recursion level            */
+static int hint_inhibit_level = 0;			/* Inhibit hinting if this is above 0 */
+											/* (This could not be above 1)        */
 
 static const struct config_enum_entry parse_messages_level_options[] = {
 	{"debug", DEBUG2, true},
@@ -518,13 +524,6 @@ static const HintParser parsers[] = {
 	{NULL, NULL, HINT_KEYWORD_UNRECOGNIZED}
 };
 
-/*
- * PL/pgSQL plugin for retrieving string representation of each query during
- * function execution.
- */
-static const char *plpgsql_query_string = NULL;
-static enum PLpgSQL_stmt_types plpgsql_query_string_src;
-
 PLpgSQL_plugin  plugin_funcs = {
 	NULL,
 	NULL,
@@ -535,9 +534,6 @@ PLpgSQL_plugin  plugin_funcs = {
 	NULL,
 };
 
-/* Current nesting depth of SPI calls, used to prevent recursive calls */
-static int	nested_level = 0;
-
 /*
  * Module load callbacks
  */
@@ -1126,7 +1122,7 @@ HintStateDump2(HintState *hstate)
 	if (!hstate)
 	{
 		elog(pg_hint_plan_message_level,
-			 "pg_hint_plan%s: HintStateDump:\nno hint", qnostr);
+			 "pg_hint_plan%s: HintStateDump: no hint", qnostr);
 		return;
 	}
 
@@ -1589,7 +1585,7 @@ get_hints_from_table(const char *client_query, const char *client_application)
 
 	PG_TRY();
 	{
-		++nested_level;
+		hint_inhibit_level++;
 	
 		SPI_connect();
 	
@@ -1627,11 +1623,11 @@ get_hints_from_table(const char *client_query, const char *client_application)
 	
 		SPI_finish();
 	
-		--nested_level;
+		hint_inhibit_level--;
 	}
 	PG_CATCH();
 	{
-		--nested_level;
+		hint_inhibit_level--;
 		PG_RE_THROW();
 	}
 	PG_END_TRY();
@@ -1647,15 +1643,21 @@ get_query_string(void)
 {
 	const char *p;
 
-	if (stmt_name)
+	if (plpgsql_recurse_level > 0)
+	{
+		/*
+		 * This is quite ugly but this is the only point I could find where
+		 * we can get the query string.
+		 */
+		p = (char*)error_context_stack->arg;
+	}
+	else if (stmt_name)
 	{
 		PreparedStatement  *entry;
 
 		entry = FetchPreparedStatement(stmt_name, true);
 		p = entry->plansource->query_string;
 	}
-	else if (plpgsql_query_string)
-		p = plpgsql_query_string;
 	else
 		p = debug_query_string;
 
@@ -2319,7 +2321,7 @@ pg_hint_plan_ProcessUtility(Node *parsetree, const char *queryString,
 	 * Use standard planner if pg_hint_plan is disabled or current nesting 
 	 * depth is nesting depth of SPI calls. 
 	 */
-	if (!pg_hint_plan_enable_hint || nested_level > 0)
+	if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
 	{
 		if (debug_level > 1)
 			ereport(pg_hint_plan_message_level,
@@ -2487,13 +2489,13 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
 	 * depth is nesting depth of SPI calls. Other hook functions try to change
 	 * plan with current_hint if any, so set it to NULL.
 	 */
-	if (!pg_hint_plan_enable_hint || nested_level > 0)
+	if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
 	{
 		if (debug_level > 1)
 			elog(pg_hint_plan_message_level,
 				 "pg_hint_plan%s: planner: enable_hint=%d,"
-				 " nested_level=%d",
-				 qnostr, pg_hint_plan_enable_hint, nested_level);
+				 " hint_inhibit_level=%d",
+				 qnostr, pg_hint_plan_enable_hint, hint_inhibit_level);
 		hidestmt = true;
 
 		goto standard_planner_proc;
@@ -3095,17 +3097,17 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
 	 * Do nothing if we don't have a valid hint in this context or current
 	 * nesting depth is at SPI calls.
 	 */
-	if (!current_hint || nested_level > 0)
+	if (!current_hint || hint_inhibit_level > 0)
 	{
 		if (debug_level > 1)
 			ereport(pg_hint_plan_message_level,
 					(errhidestmt(true),
 					 errmsg ("pg_hint_plan%s: get_relation_info"
 							 " no hint to apply: relation=%u(%s), inhparent=%d,"
-							 " current_hint=%p, nested_level=%d",
+							 " current_hint=%p, hint_inhibit_level=%d",
 							 qnostr, relationObjectId,
 							 get_rel_name(relationObjectId),
-							 inhparent, current_hint, nested_level)));
+							 inhparent, current_hint, hint_inhibit_level)));
 		return;
 	}
 
@@ -3123,10 +3125,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
 					(errhidestmt(true),
 					 errmsg ("pg_hint_plan%s: get_relation_info"
 							 " skipping inh parent: relation=%u(%s), inhparent=%d,"
-							 " current_hint=%p, nested_level=%d",
+							 " current_hint=%p, hint_inhibit_level=%d",
 							 qnostr, relationObjectId,
 							 get_rel_name(relationObjectId),
-							 inhparent, current_hint, nested_level)));
+							 inhparent, current_hint, hint_inhibit_level)));
 		return;
 	}
 
@@ -3225,10 +3227,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
 					 errmsg("pg_hint_plan%s: get_relation_info:"
 							" index deletion by parent hint: "
 							"relation=%u(%s), inhparent=%d, current_hint=%p,"
-							" nested_level=%d",
+							" hint_inhibit_level=%d",
 							qnostr, relationObjectId,
 							get_rel_name(relationObjectId),
-							inhparent, current_hint, nested_level)));
+							inhparent, current_hint, hint_inhibit_level)));
 		return;
 	}
 
@@ -3246,10 +3248,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
 					 errmsg ("pg_hint_plan%s: get_relation_info"
 							 " index deletion:"
 							 " relation=%u(%s), inhparent=%d, current_hint=%p,"
-							 " nested_level=%d, scanmask=0x%x",
+							 " hint_inhibit_level=%d, scanmask=0x%x",
 							 qnostr, relationObjectId,
 							 get_rel_name(relationObjectId),
-							 inhparent, current_hint, nested_level,
+							 inhparent, current_hint, hint_inhibit_level,
 							 hint->enforce_mask)));
 	}
 	else
@@ -3260,10 +3262,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
 					 errmsg ("pg_hint_plan%s: get_relation_info"
 							 " no hint applied:"
 							 " relation=%u(%s), inhparent=%d, current_hint=%p,"
-							 " nested_level=%d, scanmask=0x%x",
+							 " hint_inhibit_level=%d, scanmask=0x%x",
 							 qnostr, relationObjectId,
 							 get_rel_name(relationObjectId),
-							 inhparent, current_hint, nested_level,
+							 inhparent, current_hint, hint_inhibit_level,
 							 current_hint->init_scan_mask)));
 		set_scan_config_options(current_hint->init_scan_mask,
 								current_hint->context);
@@ -3933,7 +3935,7 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed,
 	 * valid hint is supplied or current nesting depth is nesting depth of SPI
 	 * calls.
 	 */
-	if (!current_hint || nested_level > 0)
+	if (!current_hint || hint_inhibit_level > 0)
 	{
 		if (prev_join_search)
 			return (*prev_join_search) (root, levels_needed, initial_rels);
@@ -4029,48 +4031,7 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
 static void
 pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
 {
-	PLpgSQL_expr *expr = NULL;
-
-	switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
-	{
-		case PLPGSQL_STMT_FORS:
-			expr = ((PLpgSQL_stmt_fors *) stmt)->query;
-			break;
-		case PLPGSQL_STMT_FORC:
-				expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_forc *)stmt)->curvar]))->cursor_explicit_expr;
-			break;
-		case PLPGSQL_STMT_RETURN_QUERY:
-			if (((PLpgSQL_stmt_return_query *) stmt)->query != NULL)
-				expr = ((PLpgSQL_stmt_return_query *) stmt)->query;
-			else
-				expr = ((PLpgSQL_stmt_return_query *) stmt)->dynquery;
-			break;
-		case PLPGSQL_STMT_EXECSQL:
-			expr = ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt;
-			break;
-		case PLPGSQL_STMT_DYNEXECUTE:
-			expr = ((PLpgSQL_stmt_dynexecute *) stmt)->query;
-			break;
-		case PLPGSQL_STMT_DYNFORS:
-			expr = ((PLpgSQL_stmt_dynfors *) stmt)->query;
-			break;
-		case PLPGSQL_STMT_OPEN:
-			if (((PLpgSQL_stmt_open *) stmt)->query != NULL)
-				expr = ((PLpgSQL_stmt_open *) stmt)->query;
-			else if (((PLpgSQL_stmt_open *) stmt)->dynquery != NULL)
-				expr = ((PLpgSQL_stmt_open *) stmt)->dynquery;
-			else
-				expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_open *)stmt)->curvar]))->cursor_explicit_expr;
-			break;
-		default:
-			break;
-	}
-
-	if (expr)
-	{
-		plpgsql_query_string = expr->query;
-		plpgsql_query_string_src = (enum PLpgSQL_stmt_types) stmt->cmd_type;
-	}
+	plpgsql_recurse_level++;
 }
 
 /*
@@ -4081,9 +4042,7 @@ pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
 static void
 pg_hint_plan_plpgsql_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
 {
-	if (plpgsql_query_string &&
-		plpgsql_query_string_src == stmt->cmd_type)
-		plpgsql_query_string = NULL;
+	plpgsql_recurse_level--;
 }
 
 void plpgsql_query_erase_callback(ResourceReleasePhase phase,
@@ -4093,8 +4052,8 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase,
 {
 	if (phase != RESOURCE_RELEASE_AFTER_LOCKS)
 		return;
-	/* Force erase stored plpgsql query string */
-	plpgsql_query_string = NULL;
+	/* Cancel plpgsql nest level*/
+	plpgsql_recurse_level = 0;
 }
 
 #define standard_join_search pg_hint_plan_standard_join_search

--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -836,14 +836,110 @@ VACUUM ANALYZE hint_plan.hints;
 
 -- plpgsql test
 EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
-DO LANGUAGE plpgsql $$
+
+-- static function
+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
+DECLARE
+  ret record;
+BEGIN
+  SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
+  RETURN ret;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- dynamic function
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+  EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- This should not use SeqScan(t1)
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+
+-- Perform
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+  PERFORM  1, /*+ SeqScan(t1) */ * from t1;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+  sum int;
+  v int;
+BEGIN
+  sum := 0;
+  FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
+    sum := sum + v;
+  END LOOP;
+  RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- Dynamic FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
 DECLARE
-    id integer;
+  sum int;
+  v int;
+  i   int;
+BEGIN
+  sum := 0;
+  FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
+    sum := sum + v;
+  END LOOP;
+  RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- Cursor FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+  ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+  rec record;
+  sum int := 0;
+BEGIN
+  FOR rec IN ref LOOP
+    sum := sum + rec.val;
+  END LOOP;
+  RETURN sum;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- RETURN QUERY
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
 BEGIN
-	SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
-	RETURN;
+  RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
 END;
-$$;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+
+-- Test for error exit from inner SQL statement.
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+  RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+
+-- this should not use SeqScan(t1) hint.
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+
+DROP FUNCTION testfunc();
 DROP EXTENSION pg_hint_plan;
 
 --