Re: psql variable interpolation with subsequent underscore

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(dot)klaver(at)gmail(dot)com