Re: pgbench - allow to store select results into variables

Fabien COELHO <coelho@cri.ensmp.fr>

From: Fabien COELHO <coelho@cri.ensmp.fr>

To: Tom Lane <tgl@sss.pgh.pa.us>

Cc: Robert Haas <robertmhaas@gmail.com>, Pavel Stehule <pavel.stehule@gmail.com>, PostgreSQL Developers <pgsql-hackers@postgresql.org>

Date: 2016-07-15T16:33:33Z

Lists: pgsql-hackers

Attachments

pgbench-into-2.patch (text/x-diff) patch
into.sql (application/x-sql)
into2.sql (application/x-sql)
into-err-1.sql (application/x-sql)
into-err-2.sql (application/x-sql)
into-err-3.sql (application/x-sql)
into-err-4.sql (application/x-sql)
into-err-5.sql (application/x-sql)
into-err-6.sql (application/x-sql)
into-err-7.sql (application/x-sql)
into-err-8.sql (application/x-sql)
into-err-9.sql (application/x-sql)
into-err-10.sql (application/x-sql)

Hello again,

> I'd be okay with
>
> SELECT 1, 2 \into one two
> SELECT 3 \into three

Here is a v2 with more or less this approach, although \into does not end 
the query, but applies to the current or last sql command. A query is 
still terminated with a ";".

Now it handles things like :

   -- standard sql command
   SELECT balance FROM bank WHERE id=1;
   \into balance

   -- compound sql command, three == 3.
   SELECT 1, 2 \; SELECT 3 ;
   \into three

   -- compound query with 2 selects & 3 variables
   SELECT i \into one
     FROM generate_series(1, 1) AS i \;
   SELECT i+1, i+2 \into two three
     FROM generate_series(1, 1) AS i ;


I had to add a few lines in psql scanner to count "\;", so the parsing 
logic is a little more complicated than before.

-- 
Fabien.

Commits

pgbench: Remove \cset
- 25ee70511ec2 12.0 landed
pgbench: add \cset and \gset commands
- 6260cc550b0e 12.0 landed
Adjust pgbench to allow non-ASCII characters in variable names.
- 9d36a386608d 11.0 landed
Refactor script execution state machine in pgbench.
- 12788ae49e19 10.0 cited
Allow empty queries in pgbench.
- 6471045230f5 10.0 cited