postgresql-jdbc.git / summary / log / commit / refs
commit 3ced0bc7ba408ecdcb0101fbe586c44e50e04012
Author: Alexandre Boyer <33391039+ng-galien@users.noreply.github.com>
Commit: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Date: Sat Oct 28 21:05:03 2023 +0000
feat: support SET statements combining with other queries with semicolon in PreparedStatement
Previoulsy, PreparedStatement supported executing several statements delimited with semicolon,
however, it was failing for SELECT and SET combination as the driver was expecting "query results"
from SET.
Now we ignore SET results, so it enables users to save network roundtrip
.../src/main/java/org/postgresql/core/Parser.java | 19 ++
.../java/org/postgresql/core/SqlCommandType.java | 21 +-
.../org/postgresql/jdbc/BatchResultHandler.java | 9 +-
.../main/java/org/postgresql/jdbc/PgStatement.java | 17 +-
.../java/org/postgresql/jdbc/ResultWrapper.java | 30 ++-
.../test/java/org/postgresql/core/ParserTest.java | 21 ++
.../test/jdbc2/PreparedStatementWithSetTest.java | 173 +++++++++++++++++
.../org/postgresql/test/jdbc2/StatementTest.java | 211 +++++++++++++++++++++
8 files changed, 490 insertions(+), 11 deletions(-)
diff --git a/pgjdbc/src/main/java/org/postgresql/core/Parser.java b/pgjdbc/src/main/java/org/postgresql/core/Parser.java
index 76476156..c463d65a 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/Parser.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/Parser.java
@@ -235,6 +235,8 @@ public class Parser {
currentCommandType = SqlCommandType.SELECT;
} else if (wordLength == 4 && parseWithKeyword(aChars, keywordStart)) {
currentCommandType = SqlCommandType.WITH;
+ } else if (wordLength == 3 && parseSetKeyword(aChars, keywordStart)) {
+ currentCommandType = SqlCommandType.SET;
} else if (wordLength == 6 && parseInsertKeyword(aChars, keywordStart)) {
if (!isInsertPresent && (nativeQueries == null || nativeQueries.isEmpty())) {
// Only allow rewrite for insert command starting with the insert keyword.
@@ -732,6 +734,23 @@ public class Parser {
&& (query[offset + 5] | 32) == 't';
}
+ /**
+ * Parse string to check presence of SET keyword regardless of case.
+ *
+ * @param query char[] of the query statement
+ * @param offset position of query to start checking
+ * @return boolean indicates presence of word
+ */
+ public static boolean parseSetKeyword(final char[] query, int offset) {
+ if (query.length < (offset + 3)) {
+ return false;
+ }
+
+ return (query[offset] | 32) == 's'
+ && (query[offset + 1] | 32) == 'e'
+ && (query[offset + 2] | 32) == 't';
+ }
+
/**
* Parse string to check presence of CREATE keyword regardless of case.
*
diff --git a/pgjdbc/src/main/java/org/postgresql/core/SqlCommandType.java b/pgjdbc/src/main/java/org/postgresql/core/SqlCommandType.java
index c40887ac..ca332c3f 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/SqlCommandType.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/SqlCommandType.java
@@ -25,5 +25,24 @@ public enum SqlCommandType {
SELECT,
WITH,
CREATE,
- ALTER;
+ ALTER,
+ SET;
+
+ private static final SqlCommandType[] SQL_COMMAND_TYPES = SqlCommandType.values();
+
+ /**
+ * Returns the SqlCommandType for the given command status.
+ * Returns BLANK if the no match is found.
+ *
+ * @param commandStatus the command status
+ * @return the SqlCommandType for the given command status
+ */
+ public static SqlCommandType fromCommandStatus(String commandStatus) {
+ for (SqlCommandType type : SQL_COMMAND_TYPES) {
+ if (type.name().equalsIgnoreCase(commandStatus)) {
+ return type;
+ }
+ }
+ return SqlCommandType.BLANK;
+ }
}
diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/BatchResultHandler.java b/pgjdbc/src/main/java/org/postgresql/jdbc/BatchResultHandler.java
index 93167767..c4704d70 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/BatchResultHandler.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/BatchResultHandler.java
@@ -98,14 +98,19 @@ public class BatchResultHandler extends ResultHandlerBase {
this.latestGeneratedRows = null;
}
- if (resultIndex >= queries.length) {
+ //SET commands can be ignored for the purposes of update counts
+ boolean statusIsFromSet = status.regionMatches(true, 0, "SET", 0, 3);
+
+ if (!statusIsFromSet && resultIndex >= queries.length) {
handleError(new PSQLException(GT.tr("Too many update results were returned."),
PSQLState.TOO_MANY_RESULTS));
return;
}
latestGeneratedKeysRs = null;
- longUpdateCounts[resultIndex++] = updateCount;
+ if (!statusIsFromSet) {
+ longUpdateCounts[resultIndex++] = updateCount;
+ }
}
private boolean isAutoCommit() {
diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/PgStatement.java b/pgjdbc/src/main/java/org/postgresql/jdbc/PgStatement.java
index e12e0bc9..fe689d22 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/PgStatement.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/PgStatement.java
@@ -19,6 +19,7 @@ import org.postgresql.core.QueryExecutor;
import org.postgresql.core.ResultCursor;
import org.postgresql.core.ResultHandlerBase;
import org.postgresql.core.SqlCommand;
+import org.postgresql.core.SqlCommandType;
import org.postgresql.core.Tuple;
import org.postgresql.util.GT;
import org.postgresql.util.PSQLException;
@@ -213,7 +214,7 @@ public class PgStatement implements Statement, BaseStatement {
if (results == null) {
lastResult = results = newResult;
} else {
- castNonNull(lastResult).append(newResult);
+ lastResult = results = castNonNull(lastResult).append(newResult);
}
}
@@ -222,15 +223,23 @@ public class PgStatement implements Statement, BaseStatement {
@Nullable ResultCursor cursor) {
try {
ResultSet rs = PgStatement.this.createResultSet(fromQuery, fields, tuples, cursor);
- append(new ResultWrapper(rs));
+ append(new ResultWrapper(rs, commandTypeOfQuery(fromQuery)));
} catch (SQLException e) {
handleError(e);
}
}
+ private SqlCommandType commandTypeOfQuery(Query query) {
+ SqlCommand sqlCommand = query.getSqlCommand();
+ if (sqlCommand == null) {
+ return SqlCommandType.BLANK;
+ }
+ return sqlCommand.getType();
+ }
+
@Override
public void handleCommandStatus(String status, long updateCount, long insertOID) {
- append(new ResultWrapper(updateCount, insertOID));
+ append(new ResultWrapper(updateCount, insertOID, SqlCommandType.fromCommandStatus(status)));
}
@Override
@@ -901,7 +910,7 @@ public class PgStatement implements Statement, BaseStatement {
try (ResourceLock ignore = lock.obtain()) {
checkClosed();
if (wantsGeneratedKeysAlways) {
- generatedKeys = new ResultWrapper(handler.getGeneratedKeys());
+ generatedKeys = new ResultWrapper(handler.getGeneratedKeys(), SqlCommandType.BLANK);
}
}
}
diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/ResultWrapper.java b/pgjdbc/src/main/java/org/postgresql/jdbc/ResultWrapper.java
index 6a62f3ea..b1706d20 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/ResultWrapper.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/ResultWrapper.java
@@ -6,6 +6,8 @@
package org.postgresql.jdbc;
+import org.postgresql.core.SqlCommandType;
+
import org.checkerframework.checker.nullness.qual.Nullable;
import org.checkerframework.dataflow.qual.Pure;
@@ -18,13 +20,15 @@ import java.sql.ResultSet;
* @author Oliver Jowett (oliver@opencloud.com)
*/
public class ResultWrapper {
- public ResultWrapper(@Nullable ResultSet rs) {
+ public ResultWrapper(@Nullable ResultSet rs, SqlCommandType commandType) {
this.rs = rs;
+ this.commandType = commandType;
this.updateCount = -1;
this.insertOID = -1;
}
- public ResultWrapper(long updateCount, long insertOID) {
+ public ResultWrapper(long updateCount, long insertOID, SqlCommandType commandType) {
+ this.commandType = commandType;
this.rs = null;
this.updateCount = updateCount;
this.insertOID = insertOID;
@@ -47,17 +51,35 @@ public class ResultWrapper {
return next;
}
- public void append(ResultWrapper newResult) {
+ /**
+ * Append a result to its internal chain of results.
+ * It has a special behavior for {@code SET} commands as {@code SET} is discarded if there are
+ * other results in the chain.
+ * If this is a {@code SET} command, the {@code newResult} is returned has the new head of
+ * the chain.
+ * If the newResult is a {@code SET} command, it's not appended and this is returned.
+ *
+ * @param newResult the result to append
+ * @return the head of the chain
+ */
+ public ResultWrapper append(ResultWrapper newResult) {
+ if (commandType == SqlCommandType.SET) {
+ return newResult;
+ }
+ if (newResult.commandType == SqlCommandType.SET) {
+ return this;
+ }
ResultWrapper tail = this;
while (tail.next != null) {
tail = tail.next;
}
-
tail.next = newResult;
+ return this;
}
private final @Nullable ResultSet rs;
private final long updateCount;
private final long insertOID;
+ private final SqlCommandType commandType;
private @Nullable ResultWrapper next;
}
diff --git a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
index 47738d68..653e8014 100644
--- a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
@@ -13,6 +13,8 @@ import org.postgresql.jdbc.EscapeSyntaxCallMode;
import org.junit.Assert;
import org.junit.Ignore;
import org.junit.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
import java.sql.SQLException;
import java.util.List;
@@ -114,6 +116,16 @@ public class ParserTest {
assertTrue("Failed to correctly parse lower case command.", Parser.parseSelectKeyword(command, 0));
}
+ /**
+ * Test SET command parsing.
+ */
+ @ParameterizedTest
+ @ValueSource(strings = {"SET", "set", "sEt", "seT", "Set", "sET", "SeT", "seT"})
+ public void testSetCommandParsing(String set) {
+ char[] command = set.toCharArray();
+ assertTrue("Parser.parseSetKeyword(\"" + set + "\", 0)", Parser.parseSetKeyword(command, 0));
+ }
+
@Test
public void testEscapeProcessing() throws Exception {
assertEquals("DATE '1999-01-09'", Parser.replaceProcessing("{d '1999-01-09'}", true, false));
@@ -230,6 +242,15 @@ public class ParserTest {
Assert.assertEquals(56, command.getBatchRewriteValuesBraceClosePosition());
}
+ @Test
+ public void setVariable() throws SQLException {
+ String query =
+ "set search_path to 'public'";
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true);
+ SqlCommand command = qry.get(0).getCommand();
+ Assert.assertEquals("command type of " + query, SqlCommandType.SET, command.getType());
+ }
+
@Test
public void valuesTableParse() throws SQLException {
String query = "insert into values_table (id, name) values (?,?)";
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementWithSetTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementWithSetTest.java
new file mode 100644
index 00000000..f61e893d
--- /dev/null
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementWithSetTest.java
@@ -0,0 +1,173 @@
+/*
+ * Copyright (c) 2023, PostgreSQL Global Development Group
+ * See the LICENSE file in the project root for more information.
+ */
+
+package org.postgresql.test.jdbc2;
+
+import static org.junit.Assert.assertEquals;
+
+import org.postgresql.test.TestUtil;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collection;
+
+@RunWith(Parameterized.class)
+public class PreparedStatementWithSetTest extends BaseTest4 {
+ private final int setBefore;
+ private final int setAfter;
+ private final Operation operation;
+ private final BatchMode batchMode;
+
+ enum Operation {
+ SELECT,
+ INSERT,
+ UPDATE,
+ DELETE,
+ }
+
+ enum BatchMode {
+ YES,
+ NO,
+ }
+
+ public PreparedStatementWithSetTest(BinaryMode binaryMode, int setBefore, int setAfter,
+ Operation operation, BatchMode batchMode) {
+ setBinaryMode(binaryMode);
+ this.setBefore = setBefore;
+ this.setAfter = setAfter;
+ this.operation = operation;
+ this.batchMode = batchMode;
+ }
+
+ @Parameterized.Parameters(name = "binary = {0}, setBefore = {1}, setAfter = {2}, operation = "
+ + "{3}, batchMode = {4}")
+ public static Iterable<Object[]> data() {
+ Collection<Object[]> ids = new ArrayList<Object[]>();
+ for (BinaryMode binaryMode : BinaryMode.values()) {
+ for (int setBefore : new int[]{0, 1, 2}) {
+ for (int setAfter : new int[]{0, 1, 2}) {
+ for (Operation operation : Operation.values()) {
+ for (BatchMode batchMode : BatchMode.values()) {
+ if (batchMode == BatchMode.YES && operation == Operation.SELECT) {
+ continue;
+ }
+ ids.add(new Object[]{binaryMode, setBefore, setAfter, operation, batchMode});
+ }
+ }
+ }
+ }
+ }
+ return ids;
+ }
+
+ @BeforeClass
+ public static void createTestTable() throws SQLException {
+ try (Connection con = TestUtil.openDB()) {
+ TestUtil.createTable(con, "inttable", "a int, b int");
+ }
+ }
+
+ @AfterClass
+ public static void dropTestTable() throws SQLException {
+ try (Connection con = TestUtil.openDB()) {
+ TestUtil.dropTable(con, "inttable");
+ }
+ }
+
+ @Override
+ public void setUp() throws Exception {
+ super.setUp();
+ try (Statement st = con.createStatement()) {
+ st.execute("DELETE FROM inttable");
+ }
+ }
+
+ @Test
+ public void test() throws SQLException {
+ StringBuilder sb = new StringBuilder();
+ addSetSearchPath(sb, setBefore);
+ switch (operation) {
+ case SELECT:
+ sb.append("SELECT * FROM inttable WHERE a>? AND a<? order by a");
+ break;
+ case INSERT:
+ sb.append("INSERT INTO inttable VALUES (?, 1)");
+ break;
+ case UPDATE:
+ sb.append("UPDATE inttable SET b=b where a=?");
+ break;
+ case DELETE:
+ sb.append("DELETE FROM inttable WHERE a=?");
+ break;
+ }
+ sb.append(';');
+ addSetSearchPath(sb, setAfter);
+ // Remove the trailing ;
+ sb.setLength(sb.length() - 1);
+
+ if (operation != Operation.INSERT) {
+ // Other operations require data to process
+ try (PreparedStatement pstmt = con.prepareStatement("insert into inttable values (1, 1), "
+ + "(2, 1)")) {
+ pstmt.execute();
+ }
+ }
+
+ // Actual test
+ try (PreparedStatement pstmt = con.prepareStatement(sb.toString())) {
+ switch (operation) {
+ case SELECT:
+ pstmt.setInt(1, 0);
+ pstmt.setInt(2, 5);
+ try (ResultSet rs = pstmt.executeQuery();) {
+ assertEquals(
+ "pstmt.executeQuery() results",
+ "1,1\n2,1",
+ TestUtil.join(TestUtil.resultSetToLines(rs))
+ );
+ }
+ break;
+ case INSERT:
+ case UPDATE:
+ case DELETE:
+ pstmt.setInt(1, 1);
+ if (batchMode == BatchMode.NO) {
+ assertEquals(
+ "pstmt.executeUpdate()",
+ 1,
+ pstmt.executeUpdate()
+ );
+ } else {
+ pstmt.addBatch();
+ pstmt.setInt(1, 2);
+ pstmt.addBatch();
+ assertEquals(
+ "pstmt.executeBatch()",
+ "[1, 1]",
+ Arrays.toString(pstmt.executeBatch())
+ );
+ }
+ break;
+ }
+ }
+ }
+
+ private void addSetSearchPath(StringBuilder sb, int numberOfCommands) {
+ for (int i = 0; i < numberOfCommands; i++) {
+ sb.append("SET search_path = 'public';");
+ }
+ }
+}
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java
index fe47f421..b10b4e94 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java
@@ -9,6 +9,7 @@ import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertThrows;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
@@ -1117,6 +1118,216 @@ public class StatementTest {
}
}
+ @Test
+ public void testUpdateCountWithSet() throws SQLException {
+ Statement stmt = con.createStatement();
+ int count;
+ count = stmt.executeUpdate("SET search_path = 'public'; INSERT INTO test_statement VALUES (1)");
+ assertEquals(1, count);
+
+ count = stmt.executeUpdate("SET search_path = 'public'; UPDATE test_statement SET i=2");
+ assertEquals(1, count);
+
+ count = stmt.executeUpdate("SET search_path = 'public'; DELETE FROM test_statement");
+ assertEquals(1, count);
+ }
+
+ @Test
+ public void testUpdateCountForMultipleStatementsWithSet() throws SQLException {
+ try (Statement stmt = con.createStatement()) {
+ stmt.execute("DROP SCHEMA IF EXISTS another_schema_than_public CASCADE");
+ stmt.execute("CREATE SCHEMA another_schema_than_public");
+ stmt.execute("CREATE TABLE another_schema_than_public.test_statement_in_another_schema (i int)");
+ stmt.execute("TRUNCATE TABLE another_schema_than_public.test_statement_in_another_schema");
+ int count;
+ count = stmt.executeUpdate("SET search_path = another_schema_than_public;"
+ + "INSERT INTO test_statement_in_another_schema VALUES (1);"
+ + "INSERT INTO test_statement_in_another_schema VALUES (2);"
+ + "INSERT INTO test_statement_in_another_schema VALUES (3)");
+ assertEquals("Multiple inserts should return the last update count", 1, count);
+
+ count = stmt.executeUpdate("SET search_path = another_schema_than_public;"
+ + "UPDATE test_statement_in_another_schema SET i=2 WHERE i=1;"
+ + "UPDATE test_statement_in_another_schema SET i=4 WHERE i=3");
+ assertEquals("Multiple updates should return the last update count", 1, count);
+
+ count = stmt.executeUpdate("SET search_path = another_schema_than_public;"
+ + "UPDATE test_statement_in_another_schema SET i=2");
+ assertEquals("Single update should return the update count", 3, count);
+
+ count = stmt.executeUpdate("SET search_path = another_schema_than_public;"
+ + "DELETE FROM test_statement_in_another_schema WHERE i > 0");
+ assertEquals(3, count);
+
+ count = stmt.executeUpdate("SET search_path TO public;"
+ + "INSERT INTO test_statement VALUES (1);"
+ + "SET search_path TO another_schema_than_public;"
+ + "INSERT INTO test_statement_in_another_schema VALUES (1);"
+ + "SET search_path TO public;"
+ + "UPDATE test_statement SET i=2 WHERE i=1;"
+ + "SET search_path TO another_schema_than_public;"
+ + "UPDATE test_statement_in_another_schema SET i=2 WHERE i=1;"
+ + "DELETE FROM test_statement_in_another_schema WHERE i=2;"
+ + "SET search_path TO public;");
+ assertEquals("Multiple statements should return the last update count", 1, count);
+ }
+ }
+
+ @Test
+ public void testSingleSelectStatementsWithSetBefore() throws SQLException {
+ try (Statement stmt = con.createStatement()) {
+ stmt.execute("INSERT INTO test_statement SELECT * FROM GENERATE_SERIES(1, 10)");
+ ResultSet resultSet = stmt.executeQuery("SET search_path = 'public';"
+ + "SET search_path = 'public';SELECT * FROM test_statement");
+ int count = 0;
+ while (resultSet.next()) {
+ count++;
+ }
+ resultSet.close();
+ assertEquals("Single select should return all rows", 10, count);
+ }
+ }
+
+ @Test
+ public void testSingleSelectStatementsWithSetSurrounding() throws SQLException {
+ try (Statement stmt = con.createStatement()) {
+ stmt.execute("INSERT INTO test_statement SELECT * FROM GENERATE_SERIES(1, 10)");
+ ResultSet resultSet = stmt.executeQuery("SET search_path = 'public';"
+ + "SELECT * FROM test_statement;"
+ + "SET search_path = 'public'");
+ int count = 0;
+ while (resultSet.next()) {
+ count++;
+ }
+ resultSet.close();
+ assertEquals("Single select should return all rows", 10, count);
+ }
+ }
+
+ @Test
+ public void testMultipleSelectStatementsWithSetBefore() throws SQLException {
+ try (Statement stmt = con.createStatement()) {
+ stmt.execute("INSERT INTO test_statement SELECT * FROM GENERATE_SERIES(1, 10)");
+ boolean ok = stmt.execute("SET search_path = 'public';"
+ + "SET search_path = 'public';"
+ + "SELECT * FROM test_statement WHERE i>5;"
+ + "SET search_path = 'public';"
+ + "SELECT * FROM test_statement WHERE i<=5");
+ assertTrue(ok);
+ ResultSet resultSet = stmt.getResultSet();
+ int count = 0;
+ while (resultSet.next()) {
+ count++;
+ }
+ resultSet.close();
+ assertEquals("First select should return 5 rows", 5, count);
+ assertTrue(stmt.getMoreResults());
+ resultSet = stmt.getResultSet();
+ count = 0;
+ while (resultSet.next()) {
+ count++;
+ }
+ resultSet.close();
+ assertEquals("Second select should return 5 rows", 5, count);
+ assertFalse(stmt.getMoreResults());
+ }
+ }
+
+ @Test
+ public void testMultipleSelectStatementsWithSetSurrounding() throws SQLException {
+ try (Statement stmt = con.createStatement()) {
+ stmt.execute("INSERT INTO test_statement SELECT * FROM GENERATE_SERIES(1, 10)");
+ boolean ok = stmt.execute("SET search_path = 'public';"
+ + "SELECT * FROM test_statement WHERE i>5;"
+ + "SET search_path = 'public';"
+ + "SELECT * FROM test_statement WHERE i<=5;"
+ + "SET search_path = 'public'");
+ assertTrue(ok);
+ ResultSet resultSet = stmt.getResultSet();
+ int count = 0;
+ while (resultSet.next()) {
+ count++;
+ }
+ resultSet.close();
+ assertEquals("First select should return 5 rows", 5, count);
+ assertTrue(stmt.getMoreResults());
+ resultSet = stmt.getResultSet();
+ count = 0;
+ while (resultSet.next()) {
+ count++;
+ }
+ resultSet.close();
+ assertEquals("Second select should return 5 rows", 5, count);
+ assertFalse(stmt.getMoreResults());
+ }
+ }
+
+ @Test
+ public void testSetLocalUserSetting() throws SQLException {
+ Assume.assumeTrue(TestUtil.haveMinimumServerVersion(con, ServerVersion.v9_6));
+ try (Statement stmt = con.createStatement()) {
+ assertThrows(SQLException.class, () -> stmt.executeQuery("SELECT current_setting('var.test', FALSE)"));
+ ResultSet rs = stmt.executeQuery("SET LOCAL var.test='test';SELECT current_setting('var.test', FALSE)");
+ assertTrue(rs.next());
+ assertEquals("Current setting should be 'test'", "test", rs.getString(1));
+ rs.close();
+ }
+ }
+
+ @Test
+ public void testSetSessionUserSetting() throws SQLException {
+ Assume.assumeTrue(TestUtil.haveMinimumServerVersion(con, ServerVersion.v9_6));
+ try (Statement stmt = con.createStatement()) {
+ ResultSet rs = stmt.executeQuery("SET SESSION var.test='test';SELECT current_setting('var.test', FALSE)");
+ assertTrue(rs.next());
+ assertEquals("Current setting should be 'test'", "test", rs.getString(1));
+ rs.close();
+ }
+ }
+
+ @Test
+ public void testTriggerWithLocalUserSetting() throws SQLException {
+ Assume.assumeTrue(TestUtil.haveMinimumServerVersion(con, ServerVersion.v9_2));
+ try (Statement stmt = con.createStatement()) {
+ stmt.execute("CREATE TABLE IF NOT EXISTS test_trigger_source (i int)");
+ stmt.execute("TRUNCATE TABLE test_trigger_source");
+ stmt.execute("CREATE TABLE IF NOT EXISTS test_trigger_value (i int)");
+ stmt.execute("TRUNCATE TABLE test_trigger_value");
+ stmt.execute("DROP TRIGGER IF EXISTS update_on_change_trigger ON test_trigger_source");
+ stmt.execute("CREATE OR REPLACE FUNCTION update_on_change() RETURNS TRIGGER AS $$\n"
+ + "BEGIN\n"
+ + " INSERT INTO test_trigger_value VALUES (current_setting('var.test_value')::int);\n"
+ + " RETURN NEW;\n"
+ + "END;\n"
+ + "$$ LANGUAGE plpgsql;\n"
+ + "CREATE TRIGGER update_on_change_trigger\n"
+ + "AFTER INSERT ON test_trigger_source\n"
+ + "FOR EACH ROW EXECUTE PROCEDURE update_on_change()");
+ stmt.execute("SET LOCAL var.test_value=42;INSERT INTO test_trigger_source VALUES (1)");
+ ResultSet rs = stmt.executeQuery("SELECT i FROM test_trigger_value ORDER BY i");
+ assertTrue(rs.next());
+ assertEquals("Current setting should be 42", 42, rs.getInt(1));
+ rs.close();
+ }
+ }
+
+ @Test
+ public void testSelectWithLocalUserSetting() throws SQLException {
+ Assume.assumeTrue(TestUtil.haveMinimumServerVersion(con, ServerVersion.v9_2));
+ try (Statement stmt = con.createStatement()) {
+ stmt.execute("SET LOCAL var.nb_test=10;SET LOCAL var.test_value=42"
+ + ";INSERT INTO test_statement "
+ + "SELECT current_setting('var.test_value')::int "
+ + "FROM generate_series(1, current_setting('var.nb_test')::int)");
+ ResultSet rs = stmt.executeQuery("SELECT i FROM test_statement ORDER BY i");
+ for (int i = 1; i <= 10; i++) {
+ assertTrue(rs.next());
+ assertEquals("Current setting should be 42", 42, rs.getInt(1));
+ }
+ rs.close();
+ }
+ }
+
@Test
public void testUnterminatedDollarQuotes() throws SQLException {
ensureSyntaxException("dollar quotes", "CREATE OR REPLACE FUNCTION update_on_change() RETURNS TRIGGER AS $$\n"
[parent: e4f47d011489]