How to concatenate psql variables in tablename

psqlredshift

I am trying to concatenate some fixed string with a variable to build a table name. This simple example demonstrates the problem. The variable "suffix" resolved properly, but "prefix" does not because I am unable to figure out how to tell psql that "ws" is not part of the variable name.

What is the proper syntax so that the resultant query selects from "my_ws_group" ?

localhost ~ > psql -v prefix=my -v suffix=group
psql (8.4.4.10, server 8.0.2)
WARNING: psql version 8.4, server version 8.0.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

dev=> select * from :prefix_ws_:suffix;
ERROR:  syntax error at or near ":"
LINE 1: select * from :prefix_ws_group;
                      ^
dev=>

I prefer to use psql 8.4 because that is most compatible with Redshift.

Best Answer

psql's pattern matching is not strong feature, so your query cannot be performed.

When parsing your string, psql finds the :prefix_ws_ since it cannot match/find :prefix, but that variable isn't defined by -v option, so psql ignores it. Next, psql finds :suffix, then replaces to group since it is defined.

Additionally if you write "select * from :prefix:suffix", psql can replace both variables such as "select * from mygroup".

Related Question