Variable interpolation in vertica client

Hi all,

A basic question regarding variable interpolation in the vsql client - how can it be done within strings?

For example:

\set schemaname myschema
select count(*) from :schemaname.foo; -- works
select analyze_statistics(:schemaname'.foo', 10); -- throws error

The intent for the second command is to interpolate schemaname into the string being passed in to analyze_statistics. I've tried various forms of syntax, but without success. Thanks.

Tagged:

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    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)
    
  • 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=>
    
  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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'
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file