Create table with a variable name

Question

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 execute first to give me the name i require.

Please suggest!!!

It might just be easier to set up an external procedure using a shell script. — Kermit, May 16 '15 at 09:34

score 1 · Answer 1 · edited Oct 27 '15 at 19:40

Try this

for what ever reason the variable stored comes with some space and i had to remove it and also cannot start naming table starting with numbers so i had to add something in form like tbl_

in short you just need to store the value of the exit so you need to do some extra work and execute the query.

\set table_name `vsql -U dbadmin -w d -t -c "select concat('tbl_',replace(to_char(current_date, 'yyMMdd'),' ',''))"`

Create table:

create table :table_name(col1 varchar(1));
(dbadmin@:5433) [dbadmin] *> \d tbl_150515
Schema      | public
Table       | tbl_150515
Column      | col1
Type        | varchar(1)
Size        | 1
Default     |
Not Null    | f
Primary Key | f
Foreign Key |

Create table with a variable name

1 Answers1