How to concatenate psql variables in tablename?

Question

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.

Answer 1

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".