On 11/08/2013 08:08 AM, Tim Kane wrote:
Hi all,
I’m having difficulty using variable interpolation within psql, where
that variable is within a table name…
Like so..
=# set YYYYMM 201310
=# select :YYYYMM;
?column?
----------
201309
(1 row)
=# alter table my_table rename to my_table_:YYYYMM_raw;
ERROR: syntax error at or near ":"
LINE 1: …my_table rename to my_table_:YYYYMM_ra...
^
The problem is that psql tries to interpret ‘YYYYMM_raw’ as the variable
name, but my intention is to only interpret ‘YYYYMM’ followed by a
literal underscore.
I can’t find any other way to encapsulate the variable name in this way…
Is there a trick to it?
I suspect I’ll need to work around this by altering the naming
convention such that the YYYYMM is at the ‘end’ of the table name.
Maybe.. Thoughts?
test=> \set tbl_name 'my_table_':YYYYMM'_raw';
test=> alter table my_table rename to :tbl_name;
ALTER TABLE
test=> \d my_table_201310_raw
Table "public.my_table_201310_raw"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
Tim
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
