How to access variable name inside single quotes.

rajatpaliwal86rajatpaliwal86 Vertica Customer

we have a database setup script where we set the schema name at just one place. like:
\set schema_name 'athena'
and later uses it other places like
create table if not exists :schema_name.foo (....);
There are some places where we need to access schema_name variable inside singe quotes.
select refresh(':schema_name.foo');
select analyze_statistics(':schema_name.foo');

How can I access variable name inside single quotes?

Best Answer

  • marcothesanemarcothesane - Select Field - Administrator
    Answer ✓

    Some try-and-error brought me to this possibility:
    Put the table name into another variable, then it could work ..., like here ...

    \set schema_name public
    \set table_name foo
    \set obj_s '''':schema_name'.':table_name''''
    \echo :obj_s
    -- out 'public.foo'
    select count(*) from :schema_name.foo;
    -- out  count 
    -- out -------
    -- out     42
    select analyze_statistics(:obj_s);
    -- out  analyze_statistics 
    -- out --------------------
    -- out                   0
    
    

Answers

  • rajatpaliwal86rajatpaliwal86 Vertica Customer

    @marcothesane said:
    Some try-and-error brought me to this possibility:
    Put the table name into another variable, then it could work ..., like here ...

    \set schema_name public
    \set table_name foo
    \set obj_s '''':schema_name'.':table_name''''
    \echo :obj_s
    -- out 'public.foo'
    select count(*) from :schema_name.foo;
    -- out  count 
    -- out -------
    -- out     42
    select analyze_statistics(:obj_s);
    -- out  analyze_statistics 
    -- out --------------------
    -- out                   0
    
    

    cool!!

Leave a Comment

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