As a lazy DBA/Developer one my favorite short cuts to do is to let the database generate SQL commands for me.
I learned this trick from an old Oracle consultant I was working with back in the late 90s. He called it “generating SQL from SQL…”.
Turns out I can still do this today! That is, I can have YugabyteDB generate SQL commands for me so that I don’t have to type them out manually.
Example:
I have a schema with a bunch of tables.
yugabyte=# \dt drop_some.*
List of relations
Schema | Name | Type | Owner
-----------+------+-------+----------
drop_some | s1 | table | yugabyte
drop_some | s2 | table | yugabyte
drop_some | s3 | table | yugabyte
drop_some | t1 | table | yugabyte
drop_some | t2 | table | yugabyte
drop_some | t3 | table | yugabyte
drop_some | t4 | table | yugabyte
drop_some | t5 | table | yugabyte
drop_some | t6 | table | yugabyte
(9 rows)
I’d like to DROP all off the table that start with the letter “s’, but not any of the other tables.
I can have YugabyteDB generate the DROP commands for me like this:
yugabyte=# SELECT 'DROP TABLE ' || schemaname || '.' || tablename || ' CASCADE;' FROM pg_tables WHERE schemaname = 'drop_some' AND tablename LIKE 's%';
?column?
----------------------------------
DROP TABLE drop_some.s1 CASCADE;
DROP TABLE drop_some.s2 CASCADE;
DROP TABLE drop_some.s3 CASCADE;
(3 rows)
But now I need to run all of the SQL commands. We saw in a previous tip how I would pipe the commands back into another YSQLSH session.
But there’s a better way!
\gexec meta-command will execute all of the SQL that is in the output buffer. Example:
yugabyte=# \gexec
DROP TABLE
DROP TABLE
DROP TABLE
yugabyte=# \dt drop_some.*
List of relations
Schema | Name | Type | Owner
-----------+------+-------+----------
drop_some | t1 | table | yugabyte
drop_some | t2 | table | yugabyte
drop_some | t3 | table | yugabyte
drop_some | t4 | table | yugabyte
drop_some | t5 | table | yugabyte
drop_some | t6 | table | yugabyte
(6 rows)
Have Fun!
