nzsql - Using a defined variable inside of a string

Question

I'm running into a bit of a snag using Netezza's nzsql tool when it comes to using internal variables. The documentation mentions how to set those variables, \set varname value but doesn't make clear how to use them in any meaningful way.

I've worked out that they can be used within queries, but so far I can only use them as identifiers, not strings.

For example, this works fine:

DB.INST1(INST1)=> \set COLNAME 'table_name'

DB.INST1(INST1)=> SELECT :COLNAME FROM _v_sys_columns LIMIT 1;
         TABLE_NAME
----------------------------
 _V_TABLE_ONLY_STORAGE_STAT
(1 row)

DB.INST1(INST1)=> \p
SELECT table_name FROM _v_sys_columns LIMIT 1;

But this doesn't:

DB.INST1(INST1)=> \set TABLE_NAME 'table_to_search_for'

DB.INST1(INST1)=> SELECT owner, createdate FROM _v_table WHERE tablename = :TABLE_NAME;
ERROR:  Attribute 'TABLE_TO_SEARCH_FOR' not found

DB.INST1(INST1)=> \p
SELECT owner, createdate FROM _v_table WHERE tablename = table_to_search_for;

And neither does this (it uses :TABLE_NAME as a text literal, not its contents):

DB.INST1(INST1)=> \set TABLE_NAME 'table_to_search_for'

DB.INST1(INST1)=> SELECT owner, createdate FROM _v_table WHERE tablename = ':TABLE_NAME';
 OWNER | CREATEDATE
-------+------------
(0 rows)

DB.INST1(INST1)=> \p
SELECT owner, createdate FROM _v_table WHERE tablename = ':TABLE_NAME';

Is there any way to accomplish what I'm hoping to do? The result I'm hoping to achieve is having nzsql run the following query:

SELECT owner, createdate FROM _v_table WHERE tablename = 'table_to_search_for';

ScottMcG · Answer 1 · 2015-01-28T17:02:05.387

0

When you want to use the contents of a variable as a literal in nzsql you need to include an additional set of single quotes, escaped with backslashes, around the literal text when you set the variable.

TESTDB.ADMIN(ADMIN)=> \set tvar '\'BLAH\''
TESTDB.ADMIN(ADMIN)=> select :tvar col_alias;
 COL_ALIAS
-----------
 BLAH
(1 row)

Updating this with an example more relevant to your situation.

TESTDB.ADMIN(ADMIN)=> create table var_table (col1 bigint);
CREATE TABLE
TESTDB.ADMIN(ADMIN)=> \set TABLE_NAME '\'VAR_TABLE\''
TESTDB.ADMIN(ADMIN)=> SELECT owner, createdate FROM _v_table WHERE tablename = :TABLE_NAME; 
 OWNER |     CREATEDATE
-------+---------------------
 ADMIN | 2015-01-13 06:52:36
(1 row)

edited Jan 28 '15 at 17:02

answered Jan 27 '15 at 19:56

ScottMcG

3,867
2
12
21

So... it looks like the answer is no, you can't use variables inside of strings. `nzsql` just doesn't support it. – Mr. Llama Jan 28 '15 at 16:56
I think that's technically correct. You have to put the string in the variable rather than the variable in the string. I realized my answer wasn't as useful for your specific question as it could have been, so updating that. – ScottMcG Jan 28 '15 at 16:58
It took me almost a year but I finally figured out how to embed a variable in a string. Thanks for your help, it pushed me in the right direction! http://stackoverflow.com/a/34270716/477563 – Mr. Llama Dec 14 '15 at 15:28

score 0 · Accepted Answer · edited May 23 '17 at 12:17

You can embed the contents of a variable in a string. The trick is making a new variable (quoted_foo in the below example) which contains a single quote, your variable's contents, and another single quote.

DB.TST(LLAMA)=> SELECT * FROM example_table WHERE example_column LIKE '%ello%';
 EXAMPLE_COLUMN
----------------
 Hello World
(1 row)

DB.TST(LLAMA)=> \set foo ello
DB.TST(LLAMA)=> \set quoted_foo '\'' :foo '\''
DB.TST(LLAMA)=> \echo :quoted_foo
'ello'

DB.TST(LLAMA)=> SELECT * FROM example_table WHERE example_column LIKE '%' || :quoted_foo || '%';
 EXAMPLE_COLUMN
----------------
 Hello World
(1 row)

For more about session variables in nzsql including using variables as part of table and column names, see this answer.

nzsql - Using a defined variable inside of a string

2 Answers2