Variable interpolation in vertica client

Comments

• Jim_Knicely - Select Field - Administrator

  August 2019 edited August 2019

  This works

  dbadmin=> CREATE SCHEMA schemaname;
  CREATE SCHEMA
  
  dbadmin=> CREATE TABLE schemaname.foo (c INT);
  CREATE TABLE
  
  dbadmin=> INSERT INTO schemaname.foo SELECT 1;
   OUTPUT
  --------
        1
  (1 row)
  
  dbadmin=> COMMIT;
  COMMIT
  
  dbadmin=> \set mychemaname ''''schemaname.foo'
  
  dbadmin=> \echo :mychemaname
  'schemaname.foo'
  
  dbadmin=> SELECT DISTINCT has_statistics FROM PROJECTIONS WHERE projection_schema = 'schemaname' AND anchor_table_name = 'foo';
   has_statistics
  ----------------
   f
  (1 row)
  
  dbadmin=> SELECT analyze_statistics(:mychemaname);
   analyze_statistics
  --------------------
                    0
  (1 row)
  
  dbadmin=> SELECT DISTINCT has_statistics FROM PROJECTIONS WHERE projection_schema = 'schemaname' AND anchor_table_name = 'foo';
   has_statistics
  ----------------
   t
  (1 row)
  

  1
• dhazeghi ✭

  August 2019

  Thanks, that does the trick! I must admit I'm not aware of the subtleties of quoting here - is there a reference I should look at?

  Ideally I'd like to make this work with shell expansion too, like the below:

  zdb=> \set myschemaname `echo $SCHEMA`
  zdb=> \echo :myschemaname
  myschema
  zdb=> \set myquotedschemaname ''''`echo $SCHEMA`'
  unterminated quoted string
  zdb=> \set myquotedschemaname '''':myschemaname'
  unterminated quoted string
  zdb=>
  

  0
• Jim_Knicely - Select Field - Administrator

  August 2019

  Maybe this?

  [dbadmin@SE-Sandbox-26-node1 ~]$ export SCHEMA=myschemaname
  
  [dbadmin@SE-Sandbox-26-node1 ~]$ vsql
  Welcome to vsql, the Vertica Analytic Database interactive terminal.
  
  Type:  \h or \? for help with vsql commands
         \g or terminate with semicolon to execute query
         \q to quit
  
  dbadmin=>  \! echo $SCHEMA
  myschemaname
  
  dbadmin=> \set myschemaname `echo \'$SCHEMA\'`
  
  dbadmin=> \echo :myschemaname
  'myschemaname'
  

  0