Skip to main content

Create table with a variable name

  • May 15, 2015
  • 1 reply
  • 7 views
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!!!

    1 reply

    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • May 15, 2015

    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)


    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement