VSQL: Set variable to result of query.

mark_d_drake

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

Jim_Knicely

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)

mark_d_drake

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();
--
/*