Thread: 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.klaver@gmail.com

On 08/11/2013 16:47, "Adrian Klaver" <adrian.klaver@gmail.com> wrote:

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


Ooh, sneaky.  Thanks Adrian.


Remi, to answer your question - this is being called as part of a shell
script. Implementing it in another language would be overkill for what I¹m
trying to do.

Cheers,

Tim

On 11/08/13 09:30, Tim Kane wrote:
>
> Remi, to answer your question - this is being called as part of a shell
> script.

In that case your options expand greatly...

For example:
   tbl_name="YYYYMM"

   psql -c "alter table my_table rename to my_table_${tbl_name}_raw;"

or for more complex stuff:
   tbl_name="YYYYMM"

   psql -f- <<EOF
       alter table my_table rename to my_table_${tbl_name}_raw;
       ... <other DML/DDL>
   EOF

Or even:
   (
       <bunch of commands generating SQL DML/DDL>

   ) | psql -f-

Or ...

The sky's the limit once you remove the restriction of working 100%
*within* psql itself.

HTH

Bosco.

On 11/8/2013 9:57 AM, Bosco Rama wrote:
> On 11/08/13 09:30, Tim Kane wrote:
>> >
>> >Remi, to answer your question - this is being called as part of a shell
>> >script.
> In that case your options expand greatly...


I'd still suggest perl for this, especially if it is ever going to get
even a little bit more complicated.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast