Create a variable in Vertica

Question

I am transitioning from SQL Server to Vertica. Is there any comparable way to create a variable?

Usually I do something like:

Define @myVariable int
Set @myVariable = select MAX(Field1) from myTable

Krol

Vertica is based on Postgres, so I would expect Postgres-style declarations to work (postgresql.org/docs/9.1/static/plpgsql-declarations.html). – Hurff

@GordonLinoff Vertica is a column-oriented database, PostgerSQL is not. How can Vertica be "based" on Postgres? – Devolution

@Mauro . . . The license for Postgres code permits developers to modify the code and re-sell it. Many more recent databases are based on Postgres -- Greenplum, ParAccel, Netezza, Redshift, for example. Actually, this isn't a big secret. The Postgres Wiki proudly proclaims the many derivative databases: wiki.postgresql.org/wiki/PostgreSQL_derived_databases. – Hurff

@GordonLinoff exactly! And this is what they say:"Column-oriented DataWarehouse (created by Stonebraker), may only be forking the psql client library." – Devolution

Answer 1

I do not think Vertica allows variables, except if you are using vsql directly, but then vsql variables are very limited and will not do what you expect:

-- this will work
\set a foo
\echo :a
foo

-- this is not what you expect:
\set a NOW()
\echo :a
NOW()

\set a select max(id) from s.items()
\echo :a
selectmax(id)froms.items()

See for more information the vertica doc at https://my.vertica.com/docs/6.1.x/HTML/index.htm#2732.htm

Colter

I should clarify: I'm running my queries in DBeaver, so I can't use this trick. Perhaps the issue is not one of Vertica necessarily? – Krol

It is Vertica itself which does not allow variables, except under a very limited form via vsql. Your other workaround are programming or subqueries. – Colter

you should put your query in apostrophes and braces if you want to use it in SQL later. E.g.: \set a '(select max(id) from s.items())' – Nordin

Answer 2

You do not "create variables" in Vertica the same way you do not "create variables" in SQL Server. What you're trying to convert is a T-SQL script.

You can do the same in Vertica by creating Perl or Python or Java ... scripts running outside the database or writing a user defined function in C++ or R or Java running inside Vertica.

Devolution

Answer 3

You can use :variable_name in Vertica for a user input variable. For example:

select date_time from table_1 where date_time between :start and :end

In above start and end are the variables. When you run the query, a dialog box opens prompting you to enter the values for start and end.

Discoverer

Answer 4

If you are using vsql, you can create variables that contain results of queries, though it is a bit convoluted:

Lets assume you start vsql with vsql -v INARG=33;

SELECT :INARG+1;                -- Set up the query
\pset format u
\pset t                         -- Update output format to bare
\g `echo /tmp/dyneval`          -- Eval the query and write into file
\set DYNARG `cat /tmp/dyneval`  -- Set var from shell command output
\echo :DYNARG

So basically we write the query result into a file and read the file's contents into a variable.

You can use /tmp/dyneval-${PPID}_id instead of /tmp/dyneval, so you can ensure, that parallel executions wont alter each other. (PPID being the parent process's process ID, that is the vsql process's PID.)

The solution has some limitations:

• assumes a linux env (echo, cat)
• it changes the output formatting settings (\pset)

Nordin