Set a YugabyteDB ysqlsh Variable Equal To a Query Result

If you’ve worked with ysqlsh, the shell for interacting with the YugabyteDB YSQL API, you are probably familiar with the \set meta-command. It sets a ysqlsh variable name to a value, or if more than one value is given, to the concatenation of all of them.

Example:

				
					yugabyte=# \set verity 'YugabyteDB is awesome!'

yugabyte=# \echo :verity
YugabyteDB is awesome!

That’s great, but what if we want to set a variable equal to the result of a query?

For that, we use the \gset meta-command! It sends the current query buffer to the server and stores the query’s output into ysqlsh variables.

Example:

				
					yugabyte=# SELECT * FROM some_data ORDER BY created_ts;
 id | the_data  |         created_ts
----+-----------+----------------------------
  1 | Old Data1 | 2024-05-23 13:28:16.955521
  2 | Old Data2 | 2024-05-23 13:28:17.173187
  3 | New Data3 | 2024-05-23 13:28:30.593381
  4 | New Data4 | 2024-05-23 13:28:32.175775
(4 rows)

yugabyte=# SELECT the_data FROM some_data ORDER BY created_ts DESC LIMIT 1 \gset

yugabyte=# \echo :the_data
New Data4

yugabyte=# SELECT * FROM some_data WHERE the_data = :'the_data';
 id | the_data  |         created_ts
----+-----------+----------------------------
  4 | New Data4 | 2024-05-23 13:28:32.175775
(1 row)

Note: Do not place a semi-colon at the end of the query!

Have Fun!