Create table with a variable name

kundansonuJ

Hi,  I need to create tables on daily basis with name as date in form at (yyMMdd), I tried this :

 

dbadmin=> \set table_name 'select to_char(current_date, \'yyMMdd \')'
dbadmin=> :table_name;
to_char
---------
150515
(1 row)

 

 

and then tried to create table with table name from the set parameter :table_name, but got this

 

dbadmin=> create table :table_name(col1 varchar(1));
ERROR 4856: Syntax error at or near "select" at character 14
LINE 1: create table select to_char(current_date, 'yyMMdd ')(col1 va...

 

 

 

Is there a way where i could store a value in a variable and then use that variable as table name or to assign priority that the inner select statement has exeute first to give me the name i require.Please suggest!!!

Adrian_Oprea_1

Try this :

 

 

- you will need to store the result and not the select string 

 

(dbadmin@:5433) [dbadmin] *> \set table_name `vsql -U dbadmin -w d -t -c "select concat('tbl_',replace(to_char(current_date, 'yyMMdd'),' ',''))"`
(dbadmin@:5433) [dbadmin] *> \echo :table_name
 tbl_150515

(dbadmin@:5433) [dbadmin] > create table :table_name(col1 varchar(1));
CREATE TABLE

(dbadmin@:5433) [dbadmin] > \d tbl_150515;
 List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+------------+--------+------------+------+---------+----------+-------------+-------------
 public | tbl_150515 | col1 | varchar(1) | 1 | | f | f |
(1 row)