Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

VSQL: Set variable to result of query.

In VSQL is there any method to set a VSQL variable to the result of a query..

select UUID_GENERATE into :UUID

where UUDI is a VSQL variable

Best Answer

  • Jim_KnicelyJim_Knicely Administrator
    edited April 23 Accepted Answer

    SELECT INTO is not supported yet in Vertica.

    What are you going to do with the variable? Is this a case where you simply need to store a result and use it over and over again in the same session?

    You could potentially store the result in a local temp table for reuse.

    Example:

    dbadmin=> CREATE LOCAL TEMP TABLE uuid ON COMMIT PRESERVE ROWS AS SELECT uuid_generate() uuid;
    CREATE TABLE
    
    dbadmin=> SELECT * FROM uuid;
                     uuid
    --------------------------------------
     d2cd153e-915a-45e4-8fd4-761f0e398ff1
    (1 row)
    
    dbadmin=> CREATE TABLE some_table (c1 UUID);
    CREATE TABLE
    
    dbadmin=> INSERT INTO some_table (c1) SELECT uuid FROM uuid;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> SELECT * FROM some_table WHERE c1 = (SELECT uuid FROM uuid);
                      c1
    --------------------------------------
     d2cd153e-915a-45e4-8fd4-761f0e398ff1
    (1 row)
    

    Or you can do something like this:

    dbadmin=> \set p `vsql -Atc "SELECT '''' || uuid_generate() || '''';"`
    
    dbadmin=> \echo :p
    'ed4850bc-870c-42c8-a064-31aafd54b504'
    
    dbadmin=> SELECT :p uuid;
                     uuid
    --------------------------------------
     ed4850bc-870c-42c8-a064-31aafd54b504
    (1 row)
    
    dbadmin=> CREATE TABLE another_table AS SELECT :p::UUID AS uuid;
    CREATE TABLE
    
    dbadmin=> \d another_table
                                        List of Fields by Tables
     Schema |     Table     | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
    --------+---------------+--------+------+------+---------+----------+-------------+-------------
     public | another_table | "uuid" | uuid |   16 |         | f        | f           |
    (1 row)
    
    dbadmin=> SELECT * FROM another_table;
                     uuid
    --------------------------------------
     ed4850bc-870c-42c8-a064-31aafd54b504
    (1 row)
    
    dbadmin=> SELECT * FROM another_table WHERE uuid = :p;
                     uuid
    --------------------------------------
     ed4850bc-870c-42c8-a064-31aafd54b504
    (1 row)
    

Answers

  • Jim

    Thanks I was able to use a variant of this to get where I needed to be.

    My problem was as part of the installation process for YADAMU, I needed to preserve the value of a UUID over a drop and recreate of a schema and then recreate a function that returned that UUID. Basically I needed to dynamically create a SQL statement..

    --
    create local temporary table if not exists INSTALLATION_STATUS(
      operation  VARCHAR(32),
      result     VARCHAR(36),
      success    BOOLEAN
    );
    --
    delete from INSTALLATION_STATUS;
    --
    insert into INSTALLATION_STATUS values ('INSTALLATION_ID',UUID_GENERATE(),false);
    --
    update INSTALLATION_STATUS 
      set result = YADAMU.YADAMU_INSTANCE_ID(),
          success = true
    where operation = 'INSTALLATION_ID';
    --
    \a
    \t
    \o YADAMU_INSTALLATION_ID.sql
    select 'create or replace function YADAMU.YADAMU_INSTANCE_ID() return VARCHAR(36) as begin return ''' || result || '''; end;' from INSTALLATION_STATUS where operation = 'INSTALLATION_ID';
    select 'create or replace function YADAMU.YADAMU_INSTALLATION_TIMESTAMP() return VARCHAR(36) as begin return ''' || to_char(current_timestamp,'YYYY-MM-DD"T"HH24:MI.SS.FFTZH:TZM') || ''';end;';
    \o
    \t
    \a
    --
    \! type YADAMU_INSTALLATION_ID.sql
    --
    drop schema if exists YADAMU cascade;
    --
    create schema YADAMU;
    --
    \i YADAMU_INSTALLATION_ID.sql
    --
    \! del YADAMU_INSTALLATION_ID.sql
    --
    select YADAMU.YADAMU_INSTANCE_ID(), YADAMU.YADAMU_INSTALLATION_TIMESTAMP();
    --
    /*
    

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.