On 07/06/18 22:42, Peter Eisentraut wrote:
I have developed a patch that allows declaring cursors over prepared
statements:
DECLARE cursor_name CURSOR FOR prepared_statement_name
[ USING param, param, ... ]
This is an SQL standard feature. ECPG already supports it (with
different internals).
Internally, this just connects existing functionality in different ways,
so it doesn't really introduce anything new.
One point worth pondering is how to pass the parameters of the prepared
statements. The actual SQL standard syntax would be
DECLARE cursor_name CURSOR FOR prepared_statement_name;
OPEN cursor_name USING param, param;
But since we don't have the OPEN statement in direct SQL, it made sense
to me to attach the USING clause directly to the DECLARE statement.
Hmm. I'm not excited about adding PostgreSQL-extensions to the SQL
standard. It's confusing, and risks conflicting with future additions to
the standard. ECPG supports the actual standard syntax, with OPEN,
right? So this wouldn't be consistent with ECPG, either.
Curiously, the direct EXECUTE statement uses the non-standard syntax
EXECUTE prep_stmt (param, param);
instead of the standard
EXECUTE prep_stmt USING param, param;
I tried to consolidate this. But using
DECLARE c CURSOR FOR p (foo, bar)
leads to parsing conflicts (and looks confusing?),
How about
DECLARE c CURSOR FOR EXECUTE p (foo, bar)
? As a user, I'm already familiar with the "EXECUTE p (foo, bar)"
syntax, so that's what I would intuitively try to use with DECLARE as
well. In fact, I think I tried doing just that once, and was
disappointed that it didn't work.
and instead allowing
EXECUTE + USING leads to a mess in the ECPG parser that exhausted me.
So I'm leaving it as is for now and might give supporting EXECUTE +
USING another try later on.
The attached patch seems to do the trick, of allowing EXECUTE + USING.
I'm not sure this is worth the trouble, though, since EXECUTE as a plain
SQL command is a PostgreSQL-extension anyway.
This also adds a test case for the existing "EXECUTE <stmt> (<params>)"
syntax in ECPG. The current ECPG parsing of that is actually a bit
weird, it allows "EXECUTE stmt (:param1) USING :param2", which seems
unintentional. This patch rejects that syntax.
- Heikki
>From 3b6cad3f6ecb615442bd0d0f365fbdec91cf9317 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <heikki.linnakan...@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
