On Wed, Apr 05, 2023 at 05:39:35PM -0700, Andres Freund wrote: > Seems like a complicated enough facility to benefit from a test or two? Peter > Eisentraut added support for the extended query protocol to psql, so it > shouldn't be too hard...
PQsendQueryGuts() does not split yet the bind/describe phase and the execute phases, so we'd need a couple more libpq APIs to do that, with more tracking of the state we're currently on when looping across multiple execute fetches. My guess is that it is possible to follow a model similar to JDBC here. I don't think that's necessarily complicated, but it is not as straight-forward as it looks. \bind was much more straight-forward than that, as it can feed on a single call of PQsendQueryParams() after saving a set of parameters. An \exec would not completely do that. Attaching one of the scripts I've played with, in a very rusty java with no classes or such, for future reference. Just update CLASSPATH to point to a copy of the JDBC driver, run it with a java command, and then look at rows, query in pg_stat_statements. -- Michael
import java.sql.*; //import System.out.format; //import org.postgresql.Driver; public class Test { static final String DB_URL = "jdbc:postgresql://localhost/mydb"; public static void main(String[] args) { Connection conn = null; // Open a connection try { conn = DriverManager.getConnection(DB_URL); conn.setAutoCommit(false); Integer increment = 0; // SELECT query PreparedStatement statement = conn.prepareStatement("select * from pg_class"); statement.setFetchSize(100); ResultSet rs = statement.executeQuery(); while (rs.next()) { increment++; } conn.commit(); statement.close(); System.out.format("SELECT increment " + increment + "\n"); //INSERT RETURNING //CREATE TABLE aa (a int); increment = 0; PreparedStatement insertStmt = conn.prepareStatement("insert into aa select oid from pg_class returning a"); insertStmt.setFetchSize(100); ResultSet insertRs = insertStmt.executeQuery(); while (insertRs.next()) { increment++; } conn.commit(); insertStmt.close(); System.out.format("INSERT RETURNING increment " + increment + "\n"); //INSERT RETURNING //CREATE TABLE bb (a int); increment = 0; PreparedStatement withStmt = conn.prepareStatement("with insert_stmt AS (insert into bb select oid from pg_class returning a) SELECT r1.a, r2.a from insert_stmt as r1, aa as r2;"); withStmt.setFetchSize(100); ResultSet withRs = withStmt.executeQuery(); while (withRs.next()) { increment++; } conn.commit(); insertStmt.close(); System.out.format("INSERT WITH increment " + increment + "\n"); increment = 0; PreparedStatement withStmt2 = conn.prepareStatement("with select_stmt AS (select oid from pg_class) SELECT r1.oid, r2.a from select_stmt as r1, aa as r2;"); withStmt2.setFetchSize(100000); ResultSet withRs2 = withStmt2.executeQuery(); while (withRs2.next()) { increment++; } conn.commit(); insertStmt.close(); System.out.format("SELECT WITH increment " + increment + "\n"); } catch (SQLException e) { e.printStackTrace(); try { conn.rollback(); } catch (SQLException ee) { ee.printStackTrace(); } } } }
signature.asc
Description: PGP signature