Variable interpolation in vertica client

dhazeghi

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.

Jim_Knicely

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)

dhazeghi

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_Knicely

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'