The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
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
0
Best Answer
-
Jim_Knicely - Select Field - Administrator
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)
0
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..