Create a psql command \gset to store query results into psql variables.

This eases manipulation of query results in psql scripts.

Pavel Stehule, reviewed by Piyush Newe, Shigeru Hanada, and Tom Lane

4 files changed, 96 insertions, 2 deletions

diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
new file mode 100644
index 00000000000..7dd5c7b51b9
--- /dev/null
+++ b/src/test/regress/expected/psql.out
@@ -0,0 +1,54 @@
+--
+-- Tests for psql features that aren't closely connected to any
+-- specific server features
+--
+-- \gset
+select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
+\echo :pref01_test01 :pref01_test02 :pref01_test03
+10 20 Hello
+-- should fail: bad variable name
+select 10 as "bad name"
+\gset
+could not set variable "bad name"
+-- multiple backslash commands in one line
+select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x
+1
+select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y
+3
+4
+select 5 as x, 6 as y \gset pref01_ \\ \g \echo :pref01_x :pref01_y
+ x | y 
+---+---
+ 5 | 6
+(1 row)
+
+5 6
+select 7 as x, 8 as y \g \gset pref01_ \echo :pref01_x :pref01_y
+ x | y 
+---+---
+ 7 | 8
+(1 row)
+
+7 8
+-- NULL should unset the variable
+\set var2 xyz
+select 1 as var1, NULL as var2, 3 as var3 \gset
+\echo :var1 :var2 :var3
+1 :var2 3
+-- \gset requires just one tuple
+select 10 as test01, 20 as test02 from generate_series(1,3) \gset
+more than one row returned for \gset
+select 10 as test01, 20 as test02 from generate_series(1,0) \gset
+no rows returned for \gset
+-- \gset should work in FETCH_COUNT mode too
+\set FETCH_COUNT 1
+select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x
+1
+select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y
+3
+4
+select 10 as test01, 20 as test02 from generate_series(1,3) \gset
+more than one row returned for \gset
+select 10 as test01, 20 as test02 from generate_series(1,0) \gset
+no rows returned for \gset
+\unset FETCH_COUNT
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index bdcf3a6a559..d3def07f92c 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -88,7 +88,7 @@ test: privileges security_label collate
 # ----------
 # Another group of parallel tests
 # ----------
-test: misc alter_generic
+test: alter_generic misc psql
 
 # rules cannot run concurrently with any test that creates a view
 test: rules
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index c7c2ed0f6a0..7059fca092b 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -95,8 +95,9 @@ test: prepared_xacts
 test: privileges
 test: security_label
 test: collate
-test: misc
 test: alter_generic
+test: misc
+test: psql
 test: rules
 test: event_trigger
 test: select_views
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
new file mode 100644
index 00000000000..50ee02a7f79
--- /dev/null
+++ b/src/test/regress/sql/psql.sql
@@ -0,0 +1,39 @@
+--
+-- Tests for psql features that aren't closely connected to any
+-- specific server features
+--
+
+-- \gset
+
+select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
+
+\echo :pref01_test01 :pref01_test02 :pref01_test03
+
+-- should fail: bad variable name
+select 10 as "bad name"
+\gset
+
+-- multiple backslash commands in one line
+select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x
+select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y
+select 5 as x, 6 as y \gset pref01_ \\ \g \echo :pref01_x :pref01_y
+select 7 as x, 8 as y \g \gset pref01_ \echo :pref01_x :pref01_y
+
+-- NULL should unset the variable
+\set var2 xyz
+select 1 as var1, NULL as var2, 3 as var3 \gset
+\echo :var1 :var2 :var3
+
+-- \gset requires just one tuple
+select 10 as test01, 20 as test02 from generate_series(1,3) \gset
+select 10 as test01, 20 as test02 from generate_series(1,0) \gset
+
+-- \gset should work in FETCH_COUNT mode too
+\set FETCH_COUNT 1
+
+select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x
+select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y
+select 10 as test01, 20 as test02 from generate_series(1,3) \gset
+select 10 as test01, 20 as test02 from generate_series(1,0) \gset
+
+\unset FETCH_COUNT