VSQL: Set variable to result of query.
mark_d_drake
Community Edition User ✭
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..
-- 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(); -- /*