I wanted to parameterize my sql script

For example let’s say i have countryCode = IND
and I need to set it to ‘USA’
so that I can start creating tables with following command in the script

CREATE TABLE IF NOT EXISTS concat(countryCode, '_orders')

so that it creates a new table like USA_orders

is it possible?

Here is an example from an experiment I did a while back:

I have a query string like so:

const queryString = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'

Notice those “dollar” parameters to VALUES(). They are place holders to be filled in when the query string is used to make a query.

I have values in an array like so:

const values = ['brianc', 'brian.m.carlson@gmail.com']

I make queries like so:

client.query(queryString, values, (err, res) => {
...
})

Here the array parameter, values, to the query contains the values to be used for those $x parameters in the query string.

This example is javascript using the node-postgres (pg) postresql client module but I’m sure you will find similar functionality in other libraries for other languages.

Google “Parameterized query”.

HI Heater ,

I am going to actually run the sql script , on database instead of accessing database through a client library. so this is not what we are looking for.

Hi prabhushrikant,

The table name in CREATE TABLE is just a name and cannot be an arbitrary expression. I would write a script that generates the SQL statements instead of trying to do this in SQL.

Hi Shrikant,

I don’t understand what you mean by “…run the sql script , on database instead of accessing database through a client library”

I don’t recall seeing any cockroach documentation that suggests that is even possible.