CODEWITHSUNDEEP
sqlcockroachdb
Christian
Christian

Reputation: 7429

Renaming a table in cockroachdb with dynamic value

In cockroachdb, I'm trying to rename a table with a dynamic value.

Current table name: bla

Wanted table name: bla_currentDay

I was trying something like this:

ALTER TABLE bla RENAME TO (SELECT concat('bla',extract('day', CURRENT_DATE)::STRING));

But I got an error. 

invalid syntax: statement ignored: syntax error at or near "("
DETAIL: source SQL:
ALTER TABLE bla RENAME TO (SELECT concat('bla',extract('day', CURRENT_DATE)::STRING))
                          ^
HINT: try \h ALTER TABLE

Is this actually possible with SQL?

Upvotes: 1

Views: 499

Answers (1)

Peter Vandivier
Peter Vandivier

Reputation: 681

At this time, it looks like that is unsupported. According to this comment responding to the question of dynamic sql feature support

...CockroachDB supports SQL with the expectation that it will be driven by an external application written in some other programming language (and you can construct dynamic SQL however you like in your external program). We don’t (yet) have a full in-database programming language like PL/SQL.

...implying that you need to obey the syntax & type constraints for the name identifier as specified in the RENAME TABLE docs.

alter-table-syntax

If you construct your table rename externally to the sql shell, you can pass in the entire command via the --execute flag. You can use the same method to run & retrieve the initial query. For example...

newNameQuery="SELECT concat('bla',extract('day', CURRENT_DATE)::STRING);"

# tail to strip header from scalar, otherwise awk/grep to parse output
newName=`echo $newNameQuery | cockroach sql --certs-dir=$certsDir --host=$host1 | tail -1`

renameQuery="ALTER TABLE bla RENAME TO $newName;"

echo $renameQuery | cockroach sql --certs-dir=$certsDir --host=$host1

...if you fancy a quick'n'dirty bash example.

If however, you really want to do it all in the integrate shell session ... an adversarial reading of the client documentation will reveal the \! & \| utilities. These allow you to pass strings back to your client to execute in your initial shell and pass the results optionally back into the sql shell.

You could string together a long gross string within the sql client and use these flags to juggle back-and-forth as needed until an ALTER TABLE... string was found which the CockroachDB server could validly interpret. If I were pedantic enough to specify what that might look like in your case, I might provide an example like the following...

[1028 16:19:36+0000 ~]$ cockroach sql --certs-dir=$CERTS_DIR --host=$HOST_1
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
#
# Server version: CockroachDB CCL v19.1.4 (x86_64-unknown-linux-gnu, built 2019/08/06 15:34:13, go1.11.6) (same version as client)
# Cluster ID: deadbeef-dead-beef-dead-beefdeadbeef
#
# Enter \? for a brief introduction.
#
root@host1:26257/defaultdb> create table bla (i int);
CREATE TABLE

Time: 38.562ms

root@host1:26257/defaultdb> \| name=`cockroach sql --certs-dir=$CERTS_DIR --host=$HOST_1 --execute "SELECT concat('bla',extract('day', CURRENT_DATE)::STRING);" | tail -1` && echo "ALTER TABLE bla RENAME to $name ;"
RENAME TABLE

Time: 75.371ms

...where $CERTS_DIR and $HOST_1 are environment variables on my laptop.

Upvotes: 1

Related Questions