From 3b6cad3f6ecb615442bd0d0f365fbdec91cf9317 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date: Wed, 11 Jul 2018 19:47:43 +0300
Subject: [PATCH 1/1] Add support for EXECUTE <stmt> USING <params> syntax.

This is the SQL-standard equivalent of "EXECUTE <stmt> (<params>)".

TODO: docs.
---
 src/backend/parser/gram.y                          |  1 +
 src/interfaces/ecpg/preproc/check_rules.pl         |  2 +-
 src/interfaces/ecpg/preproc/ecpg.addons            |  2 +-
 src/interfaces/ecpg/preproc/ecpg.trailer           |  9 ++++
 src/interfaces/ecpg/preproc/parse.pl               |  2 +-
 src/interfaces/ecpg/test/expected/sql-execute.c    | 51 ++++++++++++++++++----
 .../ecpg/test/expected/sql-execute.stderr          | 24 +++++++---
 .../ecpg/test/expected/sql-execute.stdout          |  1 +
 src/interfaces/ecpg/test/sql/execute.pgc           | 14 ++++++
 src/test/regress/expected/prepare.out              |  7 +++
 src/test/regress/sql/prepare.sql                   |  3 ++
 11 files changed, 100 insertions(+), 16 deletions(-)

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 90dfac2cb1..851363fa4e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10779,6 +10779,7 @@ ExecuteStmt: EXECUTE name execute_param_clause
 		;
 
 execute_param_clause: '(' expr_list ')'				{ $$ = $2; }
+					| USING expr_list		{ $$ = $2; }
 					| /* EMPTY */					{ $$ = NIL; }
 					;
 
diff --git a/src/interfaces/ecpg/preproc/check_rules.pl b/src/interfaces/ecpg/preproc/check_rules.pl
index 6c8b004854..ee67817be0 100644
--- a/src/interfaces/ecpg/preproc/check_rules.pl
+++ b/src/interfaces/ecpg/preproc/check_rules.pl
@@ -37,7 +37,7 @@ if ($verbose)
 
 my %replace_line = (
 	'ExecuteStmtEXECUTEnameexecute_param_clause' =>
-	  'EXECUTE prepared_name execute_param_clause execute_rest',
+	  'EXECUTE prepared_name execute_rest',
 
 	'ExecuteStmtCREATEOptTempTABLEcreate_as_targetASEXECUTEnameexecute_param_clause'
 	  => 'CREATE OptTemp TABLE create_as_target AS EXECUTE prepared_name execute_param_clause',
diff --git a/src/interfaces/ecpg/preproc/ecpg.addons b/src/interfaces/ecpg/preproc/ecpg.addons
index ca3efadc48..9606ad4783 100644
--- a/src/interfaces/ecpg/preproc/ecpg.addons
+++ b/src/interfaces/ecpg/preproc/ecpg.addons
@@ -283,7 +283,7 @@ ECPG: PrepareStmtPREPAREprepared_nameprep_type_clauseASPreparableStmt block
 		$$.type = NULL;
 		$$.stmt = $4;
 	}
-ECPG: ExecuteStmtEXECUTEprepared_nameexecute_param_clauseexecute_rest block
+ECPG: ExecuteStmtEXECUTEprepared_nameexecute_rest block
 	{ $$ = $2; }
 ECPG: DeclareCursorStmtDECLAREcursor_namecursor_optionsCURSORopt_holdFORSelectStmt block
 	{
diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer
index 19dc781885..22ad65c257 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -1881,7 +1881,16 @@ Iresult:        Iconst				{ $$ = $1; }
 						}
                 ;
 
+/*
+ * The backend grammar supports EXECUTE <stmt> USING, but in ECPG, we also
+ * support optional INTO, before or after the USING clause. This replaces the
+ * opt_execute_param_clause rule in the backend grammar.
+ *
+ * We also support the non-standard EXECUTE <stmt> (<params>) syntax. To keep
+ * things simple, any INTO clause must come after the params with that syntax.
+ */
 execute_rest: /* EMPTY */	{ $$ = EMPTY; }
+	| '(' using_list ')' opt_ecpg_into { $$ = EMPTY; }
 	| ecpg_using opt_ecpg_into  { $$ = EMPTY; }
 	| ecpg_into ecpg_using  { $$ = EMPTY; }
 	| ecpg_into				{ $$ = EMPTY; }
diff --git a/src/interfaces/ecpg/preproc/parse.pl b/src/interfaces/ecpg/preproc/parse.pl
index b20383ab17..ba556c2063 100644
--- a/src/interfaces/ecpg/preproc/parse.pl
+++ b/src/interfaces/ecpg/preproc/parse.pl
@@ -100,7 +100,7 @@ my %replace_line = (
 	'returning_clauseRETURNINGtarget_list' =>
 	  'RETURNING target_list opt_ecpg_into',
 	'ExecuteStmtEXECUTEnameexecute_param_clause' =>
-	  'EXECUTE prepared_name execute_param_clause execute_rest',
+	  'EXECUTE prepared_name execute_rest',
 	'ExecuteStmtCREATEOptTempTABLEcreate_as_targetASEXECUTEnameexecute_param_clause'
 	  => 'CREATE OptTemp TABLE create_as_target AS EXECUTE prepared_name execute_param_clause',
 	'PrepareStmtPREPAREnameprep_type_clauseASPreparableStmt' =>
diff --git a/src/interfaces/ecpg/test/expected/sql-execute.c b/src/interfaces/ecpg/test/expected/sql-execute.c
index cac91dc599..871cb266bd 100644
--- a/src/interfaces/ecpg/test/expected/sql-execute.c
+++ b/src/interfaces/ecpg/test/expected/sql-execute.c
@@ -302,29 +302,64 @@ if (sqlca.sqlcode < 0) sqlprint();}
 		printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, n, i, a, i, l);
 	}
 
+	/* test the non-standard syntax of passing parameters without USING */
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "f", 
+	ECPGt_const,"2",(long)1,(long)1,strlen("2"), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, 
+	ECPGt_char,(name),(long)8,(long)8,(8)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,(amount),(long)1,(long)8,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(letter),(long)1,(long)8,(1)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 108 "execute.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 108 "execute.pgc"
+
+
+	for (i=0, j=sqlca.sqlerrd[2]; i<j; i++)
+	{
+		/* exec sql begin declare section */
+		    
+		   
+		
+#line 113 "execute.pgc"
+ char n [ 8 ] , l = letter [ i ] [ 0 ] ;
+ 
+#line 114 "execute.pgc"
+ int a = amount [ i ] ;
+/* exec sql end declare section */
+#line 115 "execute.pgc"
+
+
+		strncpy(n, name[i], 8);
+		printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, n, i, a, i, l);
+	}
+
 	{ ECPGdeallocate(__LINE__, 0, NULL, "f");
-#line 107 "execute.pgc"
+#line 121 "execute.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 107 "execute.pgc"
+#line 121 "execute.pgc"
 
 	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table test", ECPGt_EOIT, ECPGt_EORT);
-#line 108 "execute.pgc"
+#line 122 "execute.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 108 "execute.pgc"
+#line 122 "execute.pgc"
 
 	{ ECPGtrans(__LINE__, NULL, "commit");
-#line 109 "execute.pgc"
+#line 123 "execute.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 109 "execute.pgc"
+#line 123 "execute.pgc"
 
 	{ ECPGdisconnect(__LINE__, "CURRENT");
-#line 110 "execute.pgc"
+#line 124 "execute.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 110 "execute.pgc"
+#line 124 "execute.pgc"
 
 
 	return 0;
diff --git a/src/interfaces/ecpg/test/expected/sql-execute.stderr b/src/interfaces/ecpg/test/expected/sql-execute.stderr
index 96b46bd158..f8eae9b61e 100644
--- a/src/interfaces/ecpg/test/expected/sql-execute.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-execute.stderr
@@ -156,15 +156,29 @@
 [NO_PID]: sqlca: code: 0, state: 00000
 [NO_PID]: ecpg_get_data on line 94: RESULT: t offset: -1; array: no
 [NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: deallocate_one on line 107: name f
+[NO_PID]: ecpg_execute on line 108: query: select * from test where amount = $1; with 1 parameter(s) on connection main
 [NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_execute on line 108: query: drop table test; with 0 parameter(s) on connection main
+[NO_PID]: ecpg_execute on line 108: using PQexecPrepared for "select * from test where amount = $1"
 [NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_execute on line 108: using PQexec
+[NO_PID]: ecpg_free_params on line 108: parameter 1 = 2
 [NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_process_output on line 108: OK: DROP TABLE
+[NO_PID]: ecpg_process_output on line 108: correctly got 1 tuples with 3 fields
 [NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ECPGtrans on line 109: action "commit"; connection "main"
+[NO_PID]: ecpg_get_data on line 108: RESULT: db: 'r1' offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 108: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 108: RESULT: t offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 121: name f
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 122: query: drop table test; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 122: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 122: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 123: action "commit"; connection "main"
 [NO_PID]: sqlca: code: 0, state: 00000
 [NO_PID]: deallocate_one on line 0: name i
 [NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-execute.stdout b/src/interfaces/ecpg/test/expected/sql-execute.stdout
index 5f9295ae4c..3b2c9f37eb 100644
--- a/src/interfaces/ecpg/test/expected/sql-execute.stdout
+++ b/src/interfaces/ecpg/test/expected/sql-execute.stdout
@@ -10,3 +10,4 @@ name[6]=db: 'r1'	amount[6]=111	letter[6]=f
 name[7]=db: 'r1'	amount[7]=112	letter[7]=t
 name[0]=db: 'r1'	amount[0]=1	letter[0]=f
 name[0]=db: 'r1'	amount[0]=2	letter[0]=t
+name[0]=db: 'r1'	amount[0]=2	letter[0]=t
diff --git a/src/interfaces/ecpg/test/sql/execute.pgc b/src/interfaces/ecpg/test/sql/execute.pgc
index cc9814e9be..f272836e1e 100644
--- a/src/interfaces/ecpg/test/sql/execute.pgc
+++ b/src/interfaces/ecpg/test/sql/execute.pgc
@@ -104,6 +104,20 @@ exec sql end declare section;
 		printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, n, i, a, i, l);
 	}
 
+	/* test the non-standard syntax of passing parameters without USING */
+	exec sql execute f (2) into :name, :amount, :letter;
+
+	for (i=0, j=sqlca.sqlerrd[2]; i<j; i++)
+	{
+		exec sql begin declare section;
+		char n[8], l = letter[i][0];
+		int a = amount[i];
+		exec sql end declare section;
+
+		strncpy(n, name[i], 8);
+		printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, n, i, a, i, l);
+	}
+
 	exec sql deallocate f;
 	exec sql drop table test;
 	exec sql commit;
diff --git a/src/test/regress/expected/prepare.out b/src/test/regress/expected/prepare.out
index 7016e82bd4..eda94fd525 100644
--- a/src/test/regress/expected/prepare.out
+++ b/src/test/regress/expected/prepare.out
@@ -64,6 +64,13 @@ EXECUTE q2('postgres');
  postgres | f             | t
 (1 row)
 
+-- the SQL standard way of passing parameters, with USING
+EXECUTE q2 USING 'postgres';
+ datname  | datistemplate | datallowconn 
+----------+---------------+--------------
+ postgres | f             | t
+(1 row)
+
 PREPARE q3(text, int, float, boolean, oid, smallint) AS
 	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
 	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int)
diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql
index 25f814b466..85803e60a4 100644
--- a/src/test/regress/sql/prepare.sql
+++ b/src/test/regress/sql/prepare.sql
@@ -36,6 +36,9 @@ PREPARE q2(text) AS
 
 EXECUTE q2('postgres');
 
+-- the SQL standard way of passing parameters, with USING
+EXECUTE q2 USING 'postgres';
+
 PREPARE q3(text, int, float, boolean, oid, smallint) AS
 	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
 	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int)
-- 
2.11.0