v20251230-0003-Test-SQL-PGQ-with-ECPG.patch
text/x-patch
Filename: v20251230-0003-Test-SQL-PGQ-with-ECPG.patch
Type: text/x-patch
Part: 1
From 8fb08a75726f862cda17bce353f5df65af115dee Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Tue, 30 Dec 2025 14:00:46 +0530
Subject: [PATCH v20251230 3/4] Test SQL/PGQ with ECPG
Basic tests with queries containing SQL/PGQ syntax.
---
src/interfaces/ecpg/test/ecpg_schedule | 1 +
.../ecpg/test/expected/sql-sqlpgq.c | 274 ++++++++++++++++++
.../ecpg/test/expected/sql-sqlpgq.stderr | 182 ++++++++++++
.../ecpg/test/expected/sql-sqlpgq.stdout | 6 +
src/interfaces/ecpg/test/sql/Makefile | 1 +
src/interfaces/ecpg/test/sql/meson.build | 1 +
src/interfaces/ecpg/test/sql/sqlpgq.pgc | 101 +++++++
7 files changed, 566 insertions(+)
create mode 100644 src/interfaces/ecpg/test/expected/sql-sqlpgq.c
create mode 100644 src/interfaces/ecpg/test/expected/sql-sqlpgq.stderr
create mode 100644 src/interfaces/ecpg/test/expected/sql-sqlpgq.stdout
create mode 100644 src/interfaces/ecpg/test/sql/sqlpgq.pgc
diff --git a/src/interfaces/ecpg/test/ecpg_schedule b/src/interfaces/ecpg/test/ecpg_schedule
index 254a0bacc75..b75e16fde1e 100644
--- a/src/interfaces/ecpg/test/ecpg_schedule
+++ b/src/interfaces/ecpg/test/ecpg_schedule
@@ -53,6 +53,7 @@ test: sql/quote
test: sql/show
test: sql/sqljson
test: sql/sqljson_jsontable
+test: sql/sqlpgq
test: sql/insupd
test: sql/parser
test: sql/prepareas
diff --git a/src/interfaces/ecpg/test/expected/sql-sqlpgq.c b/src/interfaces/ecpg/test/expected/sql-sqlpgq.c
new file mode 100644
index 00000000000..72c52cb9c8a
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-sqlpgq.c
@@ -0,0 +1,274 @@
+/* Processed by ecpg (regression mode) */
+/* These include files are added by the preprocessor */
+#include <ecpglib.h>
+#include <ecpgerrno.h>
+#include <sqlca.h>
+/* End of automatic include section */
+#define ECPGdebug(X,Y) ECPGdebug((X)+100,(Y))
+
+#line 1 "sqlpgq.pgc"
+#include <stdlib.h>
+#include <string.h>
+#include <stdio.h>
+
+
+#line 1 "regression.h"
+
+
+
+
+
+
+#line 5 "sqlpgq.pgc"
+
+
+/* exec sql whenever sqlerror sqlprint ; */
+#line 7 "sqlpgq.pgc"
+
+
+int
+main(void)
+{
+/* exec sql begin declare section */
+
+
+
+
+
+#line 13 "sqlpgq.pgc"
+ char command [ 512 ] ;
+
+#line 14 "sqlpgq.pgc"
+ char search_address [ 10 ] ;
+
+#line 15 "sqlpgq.pgc"
+ char cname [ 100 ] ;
+
+#line 16 "sqlpgq.pgc"
+ int reg ;
+/* exec sql end declare section */
+#line 17 "sqlpgq.pgc"
+
+
+ ECPGdebug(1, stderr);
+
+ { ECPGconnect(__LINE__, 0, "ecpg1_regression" , NULL, NULL , "main", 0);
+#line 21 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 21 "sqlpgq.pgc"
+
+
+ /* Create schema and tables for property graph testing */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create schema graph_ecpg_tests", ECPGt_EOIT, ECPGt_EORT);
+#line 24 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 24 "sqlpgq.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "set search_path = graph_ecpg_tests", ECPGt_EOIT, ECPGt_EORT);
+#line 25 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 25 "sqlpgq.pgc"
+
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table customers ( customer_id integer primary key , name varchar , address varchar )", ECPGt_EOIT, ECPGt_EORT);
+#line 31 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 31 "sqlpgq.pgc"
+
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table orders ( order_id integer primary key , register integer )", ECPGt_EOIT, ECPGt_EORT);
+#line 36 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 36 "sqlpgq.pgc"
+
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table customer_orders ( customer_orders_id integer primary key , customer_id integer references customers ( customer_id ) , order_id integer references orders ( order_id ) )", ECPGt_EOIT, ECPGt_EORT);
+#line 42 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 42 "sqlpgq.pgc"
+
+
+ /* Insert test data */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into customers values ( 1 , 'customer1' , 'US' ) , ( 2 , 'customer2' , 'CA' ) , ( 3 , 'customer3' , 'GL' )", ECPGt_EOIT, ECPGt_EORT);
+#line 45 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 45 "sqlpgq.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into orders values ( 1 , 100 ) , ( 2 , 200 ) , ( 3 , 500 )", ECPGt_EOIT, ECPGt_EORT);
+#line 46 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 46 "sqlpgq.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into customer_orders ( customer_orders_id , customer_id , order_id ) values ( 1 , 1 , 1 ) , ( 2 , 2 , 2 ) , ( 3 , 3 , 3 )", ECPGt_EOIT, ECPGt_EORT);
+#line 47 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 47 "sqlpgq.pgc"
+
+
+ /* Create property graph */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create property graph shopgraph vertex tables ( customers , orders ) edge tables ( customer_orders key ( customer_orders_id ) source key ( customer_id ) references customers ( customer_id ) destination key ( order_id ) references orders ( order_id ) )", ECPGt_EOIT, ECPGt_EORT);
+#line 59 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 59 "sqlpgq.pgc"
+
+
+ { ECPGtrans(__LINE__, NULL, "commit");
+#line 61 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 61 "sqlpgq.pgc"
+
+
+ /* direct sql - US customers */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select * from graph_table ( shopgraph match ( c is customers where c . address = 'US' ) - [ is customer_orders ] -> ( o is orders ) columns ( c . name , o . register ) )", ECPGt_EOIT,
+ ECPGt_char,(cname),(long)100,(long)1,(100)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
+ ECPGt_int,&(reg),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 64 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 64 "sqlpgq.pgc"
+
+ printf("found %ld results (%s, %d)\n", sqlca.sqlerrd[2], cname, reg);
+
+ /* direct sql with C variable - GL customers */
+ strcpy(search_address, "GL");
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select * from graph_table ( shopgraph match ( c is customers where c . address = $1 ) - [ is customer_orders ] -> ( o is orders ) columns ( c . name , o . register ) )",
+ ECPGt_char,(search_address),(long)10,(long)1,(10)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,
+ ECPGt_char,(cname),(long)100,(long)1,(100)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
+ ECPGt_int,&(reg),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 69 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 69 "sqlpgq.pgc"
+
+ printf("found %ld results (%s, %d)\n", sqlca.sqlerrd[2], cname, reg);
+
+ /* prepared statement - CA customers */
+ sprintf(command, "select * from graph_table (shopgraph match (c is customers where c.address = $1)-[is customer_orders]->(o is orders) columns (c.name, o.register))");
+ { ECPGprepare(__LINE__, NULL, 0, "graph_stmt", command);
+#line 74 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 74 "sqlpgq.pgc"
+
+ strcpy(search_address, "CA");
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "graph_stmt",
+ ECPGt_char,(search_address),(long)10,(long)1,(10)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,
+ ECPGt_char,(cname),(long)100,(long)1,(100)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
+ ECPGt_int,&(reg),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 76 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 76 "sqlpgq.pgc"
+
+ printf("found %ld results (%s, %d)\n", sqlca.sqlerrd[2], cname, reg);
+ { ECPGdeallocate(__LINE__, 0, NULL, "graph_stmt");
+#line 78 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 78 "sqlpgq.pgc"
+
+
+ /* cursor test - all customers with orders */
+ /* declare graph_cursor cursor for select * from graph_table ( shopgraph match ( c is customers ) - [ is customer_orders ] -> ( o is orders ) columns ( c . name , o . register ) ) order by name */
+#line 81 "sqlpgq.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare graph_cursor cursor for select * from graph_table ( shopgraph match ( c is customers ) - [ is customer_orders ] -> ( o is orders ) columns ( c . name , o . register ) ) order by name", ECPGt_EOIT, ECPGt_EORT);
+#line 82 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 82 "sqlpgq.pgc"
+
+ /* exec sql whenever not found break ; */
+#line 83 "sqlpgq.pgc"
+
+ while (1) {
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "fetch graph_cursor", ECPGt_EOIT,
+ ECPGt_char,(cname),(long)100,(long)1,(100)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
+ ECPGt_int,&(reg),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 85 "sqlpgq.pgc"
+
+if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
+#line 85 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 85 "sqlpgq.pgc"
+
+ printf("cursor result: %s, %d\n", cname, reg);
+ }
+ /* exec sql whenever not found continue ; */
+#line 88 "sqlpgq.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "close graph_cursor", ECPGt_EOIT, ECPGt_EORT);
+#line 89 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 89 "sqlpgq.pgc"
+
+
+ /* Clean up */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop property graph shopgraph", ECPGt_EOIT, ECPGt_EORT);
+#line 92 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 92 "sqlpgq.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table customer_orders", ECPGt_EOIT, ECPGt_EORT);
+#line 93 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 93 "sqlpgq.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table orders", ECPGt_EOIT, ECPGt_EORT);
+#line 94 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 94 "sqlpgq.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table customers", ECPGt_EOIT, ECPGt_EORT);
+#line 95 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 95 "sqlpgq.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop schema graph_ecpg_tests", ECPGt_EOIT, ECPGt_EORT);
+#line 96 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 96 "sqlpgq.pgc"
+
+ { ECPGtrans(__LINE__, NULL, "commit");
+#line 97 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 97 "sqlpgq.pgc"
+
+ { ECPGdisconnect(__LINE__, "CURRENT");
+#line 98 "sqlpgq.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 98 "sqlpgq.pgc"
+
+
+ return 0;
+}
diff --git a/src/interfaces/ecpg/test/expected/sql-sqlpgq.stderr b/src/interfaces/ecpg/test/expected/sql-sqlpgq.stderr
new file mode 100644
index 00000000000..b559e70b7a9
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-sqlpgq.stderr
@@ -0,0 +1,182 @@
+[NO_PID]: ECPGdebug: set to 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGconnect: opening database ecpg1_regression on <DEFAULT> port <DEFAULT>
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 24: query: create schema graph_ecpg_tests; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 24: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 24: OK: CREATE SCHEMA
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 25: query: set search_path = graph_ecpg_tests; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 25: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 25: OK: SET
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 27: query: create table customers ( customer_id integer primary key , name varchar , address varchar ); with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 27: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 27: OK: CREATE TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 33: query: create table orders ( order_id integer primary key , register integer ); with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 33: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 33: OK: CREATE TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 38: query: create table customer_orders ( customer_orders_id integer primary key , customer_id integer references customers ( customer_id ) , order_id integer references orders ( order_id ) ); with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 38: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 38: OK: CREATE TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 45: query: insert into customers values ( 1 , 'customer1' , 'US' ) , ( 2 , 'customer2' , 'CA' ) , ( 3 , 'customer3' , 'GL' ); with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 45: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 45: OK: INSERT 0 3
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 46: query: insert into orders values ( 1 , 100 ) , ( 2 , 200 ) , ( 3 , 500 ); with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 46: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 46: OK: INSERT 0 3
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 47: query: insert into customer_orders ( customer_orders_id , customer_id , order_id ) values ( 1 , 1 , 1 ) , ( 2 , 2 , 2 ) , ( 3 , 3 , 3 ); with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 47: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 47: OK: INSERT 0 3
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 50: query: create property graph shopgraph vertex tables ( customers , orders ) edge tables ( customer_orders key ( customer_orders_id ) source key ( customer_id ) references customers ( customer_id ) destination key ( order_id ) references orders ( order_id ) ); with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 50: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 50: OK: CREATE PROPERTY GRAPH
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 61: action "commit"; connection "main"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 64: query: select * from graph_table ( shopgraph match ( c is customers where c . address = 'US' ) - [ is customer_orders ] -> ( o is orders ) columns ( c . name , o . register ) ); with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 64: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 64: correctly got 1 tuples with 2 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 64: RESULT: customer1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 64: RESULT: 100 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 69: query: select * from graph_table ( shopgraph match ( c is customers where c . address = $1 ) - [ is customer_orders ] -> ( o is orders ) columns ( c . name , o . register ) ); with 1 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 69: using PQexecParams
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_free_params on line 69: parameter 1 = GL
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 69: correctly got 1 tuples with 2 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 69: RESULT: customer3 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 69: RESULT: 500 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: prepare_common on line 74: name graph_stmt; query: "select * from graph_table (shopgraph match (c is customers where c.address = $1)-[is customer_orders]->(o is orders) columns (c.name, o.register))"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 76: query: select * from graph_table (shopgraph match (c is customers where c.address = $1)-[is customer_orders]->(o is orders) columns (c.name, o.register)); with 1 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 76: using PQexecPrepared for "select * from graph_table (shopgraph match (c is customers where c.address = $1)-[is customer_orders]->(o is orders) columns (c.name, o.register))"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_free_params on line 76: parameter 1 = CA
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 76: correctly got 1 tuples with 2 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 76: RESULT: customer2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 76: RESULT: 200 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 78: name graph_stmt
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: query: declare graph_cursor cursor for select * from graph_table ( shopgraph match ( c is customers ) - [ is customer_orders ] -> ( o is orders ) columns ( c . name , o . register ) ) order by name; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 82: OK: DECLARE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: query: fetch graph_cursor; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 85: correctly got 1 tuples with 2 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 85: RESULT: customer1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 85: RESULT: 100 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: query: fetch graph_cursor; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 85: correctly got 1 tuples with 2 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 85: RESULT: customer2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 85: RESULT: 200 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: query: fetch graph_cursor; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 85: correctly got 1 tuples with 2 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 85: RESULT: customer3 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 85: RESULT: 500 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: query: fetch graph_cursor; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 85: correctly got 0 tuples with 2 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode 100 on line 85: no data found on line 85
+[NO_PID]: sqlca: code: 100, state: 02000
+[NO_PID]: ecpg_execute on line 89: query: close graph_cursor; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 89: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 89: OK: CLOSE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 92: query: drop property graph shopgraph; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 92: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 92: OK: DROP PROPERTY GRAPH
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 93: query: drop table customer_orders; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 93: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 93: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 94: query: drop table orders; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 94: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 94: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 95: query: drop table customers; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 95: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 95: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 96: query: drop schema graph_ecpg_tests; with 0 parameter(s) on connection main
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 96: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 96: OK: DROP SCHEMA
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 97: action "commit"; connection "main"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_finish: connection main closed
+[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-sqlpgq.stdout b/src/interfaces/ecpg/test/expected/sql-sqlpgq.stdout
new file mode 100644
index 00000000000..29e6a11ff26
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-sqlpgq.stdout
@@ -0,0 +1,6 @@
+found 1 results (customer1, 100)
+found 1 results (customer3, 500)
+found 1 results (customer2, 200)
+cursor result: customer1, 100
+cursor result: customer2, 200
+cursor result: customer3, 500
diff --git a/src/interfaces/ecpg/test/sql/Makefile b/src/interfaces/ecpg/test/sql/Makefile
index 3ff190a5233..5296848cda9 100644
--- a/src/interfaces/ecpg/test/sql/Makefile
+++ b/src/interfaces/ecpg/test/sql/Makefile
@@ -25,6 +25,7 @@ TESTS = array array.c \
show show.c \
sqljson sqljson.c \
sqljson_jsontable sqljson_jsontable.c \
+ sqlpgq sqlpgq.c \
insupd insupd.c \
twophase twophase.c \
insupd insupd.c \
diff --git a/src/interfaces/ecpg/test/sql/meson.build b/src/interfaces/ecpg/test/sql/meson.build
index c0608173e4b..b7b284d0cae 100644
--- a/src/interfaces/ecpg/test/sql/meson.build
+++ b/src/interfaces/ecpg/test/sql/meson.build
@@ -27,6 +27,7 @@ pgc_files = [
'sqlda',
'sqljson',
'sqljson_jsontable',
+ 'sqlpgq',
'twophase',
]
diff --git a/src/interfaces/ecpg/test/sql/sqlpgq.pgc b/src/interfaces/ecpg/test/sql/sqlpgq.pgc
new file mode 100644
index 00000000000..3b2b0d710c0
--- /dev/null
+++ b/src/interfaces/ecpg/test/sql/sqlpgq.pgc
@@ -0,0 +1,101 @@
+#include <stdlib.h>
+#include <string.h>
+#include <stdio.h>
+
+exec sql include ../regression;
+
+exec sql whenever sqlerror sqlprint;
+
+int
+main(void)
+{
+exec sql begin declare section;
+ char command[512];
+ char search_address[10];
+ char cname[100];
+ int reg;
+exec sql end declare section;
+
+ ECPGdebug(1, stderr);
+
+ exec sql connect to REGRESSDB1 as main;
+
+ /* Create schema and tables for property graph testing */
+ exec sql create schema graph_ecpg_tests;
+ exec sql set search_path = graph_ecpg_tests;
+
+ exec sql create table customers (
+ customer_id integer primary key,
+ name varchar,
+ address varchar
+ );
+
+ exec sql create table orders (
+ order_id integer primary key,
+ register integer
+ );
+
+ exec sql create table customer_orders (
+ customer_orders_id integer primary key,
+ customer_id integer references customers (customer_id),
+ order_id integer references orders (order_id)
+ );
+
+ /* Insert test data */
+ exec sql insert into customers values (1, 'customer1', 'US'), (2, 'customer2', 'CA'), (3, 'customer3', 'GL');
+ exec sql insert into orders values (1, 100), (2, 200), (3, 500);
+ exec sql insert into customer_orders (customer_orders_id, customer_id, order_id) values (1, 1, 1), (2, 2, 2), (3, 3, 3);
+
+ /* Create property graph */
+ exec sql create property graph shopgraph
+ vertex tables (
+ customers,
+ orders
+ )
+ edge tables (
+ customer_orders key (customer_orders_id)
+ source key (customer_id) references customers (customer_id)
+ destination key (order_id) references orders (order_id)
+ );
+
+ exec sql commit;
+
+ /* direct sql - US customers */
+ exec sql select * into :cname, :reg from graph_table (shopgraph match (c is customers where c.address = 'US')-[is customer_orders]->(o is orders) columns (c.name, o.register));
+ printf("found %ld results (%s, %d)\n", sqlca.sqlerrd[2], cname, reg);
+
+ /* direct sql with C variable - GL customers */
+ strcpy(search_address, "GL");
+ exec sql select * into :cname, :reg from graph_table (shopgraph match (c is customers where c.address = :search_address)-[is customer_orders]->(o is orders) columns (c.name, o.register));
+ printf("found %ld results (%s, %d)\n", sqlca.sqlerrd[2], cname, reg);
+
+ /* prepared statement - CA customers */
+ sprintf(command, "select * from graph_table (shopgraph match (c is customers where c.address = $1)-[is customer_orders]->(o is orders) columns (c.name, o.register))");
+ exec sql prepare graph_stmt from :command;
+ strcpy(search_address, "CA");
+ exec sql execute graph_stmt into :cname, :reg using :search_address;
+ printf("found %ld results (%s, %d)\n", sqlca.sqlerrd[2], cname, reg);
+ exec sql deallocate graph_stmt;
+
+ /* cursor test - all customers with orders */
+ exec sql declare graph_cursor cursor for select * from graph_table (shopgraph match (c is customers)-[is customer_orders]->(o is orders) columns (c.name, o.register)) order by name;
+ exec sql open graph_cursor;
+ exec sql whenever not found do break;
+ while (1) {
+ exec sql fetch graph_cursor into :cname, :reg;
+ printf("cursor result: %s, %d\n", cname, reg);
+ }
+ exec sql whenever not found continue;
+ exec sql close graph_cursor;
+
+ /* Clean up */
+ exec sql drop property graph shopgraph;
+ exec sql drop table customer_orders;
+ exec sql drop table orders;
+ exec sql drop table customers;
+ exec sql drop schema graph_ecpg_tests;
+ exec sql commit;
+ exec sql disconnect;
+
+ return 0;
+}
--
2.34.1